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: Fredy Paquet <fredy AT opag.ch>
  • To: jklowden AT freetds.org, FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp adding escaping
  • Date: Fri, 22 Apr 2016 16:29:42 +0200

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 <andrew.punch AT smart-associates.biz> 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
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

Attachment: smime.p7s
Description: S/MIME cryptographic signature




Archive powered by MHonArc 2.6.24.

Top of Page