[freetds] freebcp adding escaping

Thompson, William bill.d.thompson at baml.com
Thu Feb 4 04:31:55 EST 2016

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, 


-----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


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

This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.

More information about the FreeTDS mailing list