http://www.perlmonks.org?node_id=1154917


in reply to CSV headers. Feedback wanted

I like but will give an example of a CSV format I'm using that is pathological and will give the wrong separator, but it's a special case and the number of "customers" for it is very restricted.

I have a data acquisition system made by someone else that reads a few hundred parameters every 20 seconds or so. It writes them to disk locally and spits them out via UDP to a closed network (because of some firewall requirements).

The data format for each line is:

val1,val2, val3,...valN,;,header1, header2, header3,...,headerN <CRLF>

but the first line of any given file is:

header1, header2, header3,...,headerN,;,header1, header2, header3,...,headerN <CRLF>

where the semicolon is in there to let the reader know that it's at the end of the value list, and the rest of the line is header information.

The reason for the first line is that the reader of the file is likely going to load it into Excel and putting a header at the top reduces the risk of column alignment errors from copying the header information to the top of the Excel file. The reasoning behind the line format is that the listener (which is written in Perl) on the UDP port, which makes a backup copy of the data and serves it up to both a web page and a labview program that displays data plots, knows nothing about the details of the sender, and if the sender software is updated to add data columns, the listener has to notice and still properly display and label them. So if the listener sees a change in the header list it will start a new file with the correct header at the top of it

Your autodetect looks like it would catch the semicolon and quit looking for separators. I admit that this is a pathological case and you're unlikely to see it in the wild, but oddball cases with more than one potential separator in the header line do exist. You might want to have an option to throw an error if more than one potential separator appears in the first line, and/or maybe an option to count the number of each type of potential separator and pick the one with the most instances. I think I'd rather see it default to an error message if there are multiple potential separators in the header, and have a flag that lets me tell it to suppress the error and guess.

Replies are listed 'Best First'.
Re^2: CSV headers. Feedback wanted
by Tux (Canon) on Feb 11, 2016 at 07:22 UTC

    As you are stating yourself already, this is no standard CSV, and no auto-detection would work. The new header method is intended for the majority of CSV data file that contain a sane header.

    As this will be a new auxiliary / helper method that is not integrated in the normal flow, it is completely optional. Lets say it is just as optional as fragment or callbacks are, which are also there just to ease attacking specific problems in CSV parsing.

    Your example is clear, and from real-life data. Not just some data invented to point at the weaknesses new functionality, but with this data, it is obvious that this new method is not something that would help you at all. This new method is not created to help you here.

    In your case [fragment could help a lot: read the first line, detect the headers and the header count and then set fragment to only read the data part just before continuing reading the rest of the data.


    Enjoy, Have FUN! H.Merijn
      It is legal CSV - the only thing goofy about it is that it has a semicolon on one of the places between commas, rather than useful header or data information. If I'm blindly using your new approach to read CSV files because, for example, I'm an unwitting recipient of the file, or because (more likely) I've forgotten about the format, I'd like the autodetect to warn me "Hey, this isn't quite standard CSV". I don't have a problem reading the data - we just read a whole line, use a regex to split it at the semicolon and away we go. It's intended as an example of something that will trip up a user of your autodetect. The lines in the file are long enough that a lazy user would look at the upper left and say "looks like CSV, I'll apply my generic CSV reader that use's Tux's nifty autodetect"

      First, I agree that guessing separators in a weird situation is too risky. Also, I think that detecting the presence of multiple possible separators and throwing an error makes sense.

      Yes, I know that the data resulting from choosing the wrong separator should look weird, but a person might not realize what they are seeing. Throwing an error when an easily detectable "weirdness" is seen will help alert the user to the situation.

        Your point about looking weird to the reader reminds me of one incident that I forgot about that happened with the files. There's a notes field that happens to be too small to see much of anything, and one user put in a note that accidentally embedded a linefeed rather than entering the note due to differences in "return" vs "enter" behavior of Labview. It confused people for about a day until they got someone to put in a thumb drive at the source and extract a copy of the original data (most users didn't have much understanding of the data flow). So I'd definitely prefer a way to throw an error or warning for multiple separators.