[freetds] FreeTDS Digest, Vol 157, Issue 1

Andrew Punch andrew.punch at smart-associates.biz
Mon Feb 8 05:41:45 EST 2016


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?


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

More information about the FreeTDS mailing list