Skip to Content.
Sympa Menu

freetds - Re: [freetds] freebcp adding escaping

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, William" <bill.d.thompson AT baml.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp adding escaping
  • Date: Thu, 04 Feb 2016 09:31:55 +0000

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.




Archive powered by MHonArc 2.6.24.

Top of Page