Re: freebcp adding escaping

On Wed, 3 Feb 2016 15:40:09 +1100
Andrew Punch <[email protected]> wrote:

> 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 disagree with Frediano; I think freebcp is the right place to add csv
support.  But if I were in his shoes, I'd be reluctant to take your
patch without quite a bit more work and testing.  

It's not true that freebcp handles escape characters incorrectly.
freebcp *defines* its text-file formats, and that definition doesn't
include an escape mechanism.  The column and row delimiters are strings
(not single characters).  Any character sequence that does not
appear in the data will serve.  ASCII includes US and RS codes (31 and
30) which were intended for the purpose.  (It would be a nice addition
to support them as named escapes for the -r and -t options of
freebcp.)  

The reason I'd be reluctant to apply your patch is that CSV is a
complex format.  It's defined, sort of, by an RFC, but there are many
de facto variations on that theme, including using tabs instead of
commas as delimiters (in a Comma-Separated Variable format!) 

To parse CSV correctly, you need to parse it as a context-free
language.  Regular expressions aren't good enough, and an ad hoc
work-for-me hack will likely be frustrating to users and developers
both.  

If you want to do the job correctly, then, you need to write or import
a parser for CSV grammar. As it happens, I've written one that could
perhaps be adapted and that I would be willing to contribute to the
FreeTDS project (http://www.schemamania.org/sql/sqlite/udf/).  It's
certainly not the only one.  It doesn't handle every variation, but it
did pass tests I found that were provided by another csv-parser as
examples.  Let me know if you're interested.  

To answer your other questions: 

>  1. is dblib the best place to make the change?

If so, it would be in the bcp functions.  

>  2. what is the best structure to pass parameters and flags? Namely:
>      1. Flag for delimiter escaping

Please elaborate. 

>      2. The actual delimiter (I notice that the column delimiter
> changes to EOL for the last column)

If you want to last column to include the column *separator*, define
your row separator to include it.  For example, 

	-t \\t -r\\t\\n

would do the job.  

>      3. Flag for NULL string replacement (instead of ASCII NUL)
>      4. NULL string replacement

This is a separate issue, and could go two ways: either convert
an external string to NULL in the database, or convert missing
external data to a particular string in the database.  The only real
challenge is to define the freebcp option syntax; internally, it's just
a few calls to bcp_colfmt.  

HTH.  

--jkl