CSV with added ,

What’s the Definition of CSV?

A simple question, I would have thought, and one that’s answered reasonably comprehensively elsewhere.  However, I note that there appears to be no “official” format definition, but only a de facto standard.

Consider the following:

ID,Name
1,Dave
2,William

Three records, each of which has two fields.

And if you have a field that’s blank, you would have something like:

ID,Name,Company
1,Dave,IBM
2,William,HP
3,John,

indicating John doesn’t work for a company.  All well and good, I would have thought.

HOWEVER.  (You knew that was coming…)

I was working on an Assyst migration – from v 7.something to a new shiny v9 installation – and, as part of this process, you can prepopulate various tables using an import program.   This importer takes an input file consisting of a few lines of header information, and the remainder is data in CSV format.

So I was running various import routines with data such as the above.   And these were failing.  And the reason they were failing?  The final field in many rows was blank.   Query this behaviour with Axios, and their response was to tell me to stick an extra comma at the end of the line.

ID,Name,Company
1,Dave,IBM
2,William,HP
3,John,,

So that last entry, where ID = 3, now looks (to me) as though it should have four fields not three.

This, to me, seems wrong.  But it works when importing data into Assyst.  So if you’re having that problem, then there’s the counter-intuitive fix.   “Stick an extra comma at the end of the affected lines.”  Marvellous.

Incidentally, this problem wasn’t fixed in v9.1.  I’ve moved on since then, so I have no idea if Axios ever addressed it, or indeed intend to address it.

Advertisements
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

4 Responses to CSV with added ,

  1. WilliamD says:

    Logic fail!

    You have to wonder which bonehead came up with that solution. Since when does a comma denote an empty value?

    • thomasrushton says:

      That’s the thing – they’re using it as a terminator as well as a delimiter, but only in the cases when the final field is blank… It just feels wrong.

  2. Farkough says:

    CSV is a real minefield. As you say, nobody can agree. It’s not just CSV, it’s what the record terminator is (CRLF), are text fields quoted, etc, etc. God forbid they load it into Excel…

  3. thomasrushton says:

    Nice to see I’m not the only one still having problems with CSVs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s