FreeTDS Development Group

[Andrew Punch <andrew.punch AT smart-associates.biz>]

Hi,

The delimited file I am creating is for use with Netezza's create 
external table. This format would also be useful for Redshift.

Excel format [https://tools.ietf.org/html/rfc4180] would also be 
acceptable as I could process it in awk or another tool if it is 
properly escaped.

Regarding NULL. How do you suggest to differentiate between an empty 
field and a NULL? Currently freebcp inserts an ASCII NUL if the field is 
NULL. A zero length string has nothing between the delimiters. I suppose 
I could replace the ASCII NUL with the desired NULL character but what 
if there is an ASCII NUL in the field?

-Andrew

On 05/02/16 01:16, freetds-request AT lists.ibiblio.org wrote:
> Hi Andrew,
>
> It's a perfectly reasonable requirement, but I'm not sure that your implementation 
> produces "properly formatted CSV output" as commonly understood.
> Although there is no agreed standard for CSV, the Wikipedia page on CSV 
> summarises my understanding of the conventions, namely that a CSV file 
> requires:
>
> * lines that end with a newline character or characters (optional for the 
> last line)
> * An optional header record (although there is no sure way to detect whether 
> it is present, so care is required when importing).
> * Each record should contain the same number of comma-separated fields.
> * Any field may be quoted (with double quotes).
> * Fields containing a line-break, double-quote, and/or commas should be 
> quoted.
> * A (double) quote character in a field must be represented by two (double) 
> quote characters.
>
> Given this, a neat way of fulfilling the requirement would be to implement a 
> single command line flag,
> which would operate as an alternative to the -c, -n or -f flags.
> This could then:
>
> Default the field separator to comma, (with possible override using the -t 
> flag)
> Wrap double quotes around at least all the character fields extracted, maybe 
> all fields...
> Double up any double quotes in any character fields.
>
> And that's all you would need.
> For most applications accepting CSV files (Excel for example) that would deal 
> with the issues you have tried to address with the escaping functionality 
> you've coded.
>
> I'm not sure about the Null string/field replacement stuff you've done.
> An empty or absent field in CSV is usually just represented by two 
> consecutive delimiters, and I think bcp would do that already.
>
> Hope this helps,
>
> Bill
>
>
>
> -----Original Message-----
> From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Andrew 
> Punch
> Sent: 03 February 2016 04:40
> To:freetds AT lists.ibiblio.org
> Subject: [freetds] freebcp adding escaping
>
> Hi,
>
> I had a requirement to have properly formatted CSV output from freebcp.
> Unfortunately freebcp (and the original bcp) do not escape characters 
> correctly. For example in a comma delimited file commas will not be escaped. 
> Also the way NULLs are indicated can be inconvenient.
>
> I hacked together a patch which I have attached. I would like to do a pull 
> after a clean up but I have a few questions:
>
>   1. is dblib the best place to make the change?
>   2. what is the best structure to pass parameters and flags? Namely:
>       1. Flag for delimiter escaping
>       2. The actual delimiter (I notice that the column delimiter changes
>          to EOL for the last column)
>       3. Flag for NULL string replacement (instead of ASCII NUL)
>       4. NULL string replacement