Re: freebcp adding escaping

Hello James

Your CSV parser is very interesting, but it doesn't seem to handle the 
ugly quoting rules for CSV correctly. Also newline within a text field
isn't processed correctly.

<https://en.wikipedia.org/wiki/Comma-separated_values>

Isn't there any better implementation around ?

fp

-- 
Fredy Paquet
Mitglied der Geschäftsleitung
OPAG Informatik AG
Fabrikmattenweg 11
CH-4144 Arlesheim
http://www.opag.ch
Tel. ++41 61 716 92 22
Fax. ++41 61 716 92 29


On Sun, 2016-04-17 at 18:27 -0400, James K. Lowden wrote:
> 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
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds