Skip to Content.
Sympa Menu

freetds - Re: [freetds] FreeTDS Digest, Vol 157, Issue 1

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Andrew Punch <andrew.punch AT smart-associates.biz>
  • To: freetds AT lists.ibiblio.org
  • Subject: Re: [freetds] FreeTDS Digest, Vol 157, Issue 1
  • Date: Mon, 8 Feb 2016 21:41:45 +1100

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




  • Re: [freetds] FreeTDS Digest, Vol 157, Issue 1, Andrew Punch, 02/08/2016

Archive powered by MHonArc 2.6.24.

Top of Page