FreeTDS Development Group

["Thompson, William" <bill.d.thompson AT baml.com>]

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

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