Securely skipping the last x lines in a "somewhat" CSV file

Hi there,

I guess some of you know the situation: you have to solve a simple task and fail over and over again. I am sitting in from of such a beast at the moment.

The task is easy: parse a CSV file which has a few leading and trailing lines that do not conform to the CSV format of the middle lines (let's call that part the payload of the file).

So all I have is a definition of the columns of the CSV payload part and the number of lines to ignore at the head and the tail of the file.

For the payload; I use NeoCSVReader.There is very little trouble with this, as long as I only use that payload part of the ReadStream for it.

Now to the "hard" part: ignoring irrevelant lines at the beginnig and the end of the file.

The problem is the same for the first and last lines, but the trainling lines are a bit harder, because you need to know that this upcoming line is the x-last or (x-1)-last line of the file.

The thing is: if the lines to be ignored were just lines, I coul used #nextLine. But in some cases they are CSV-lines which may or may not have the same number of columns as the payload lines. The hardest part is, however, that sometimes these lines are CSV (same or different number of columns as the payload) and contain qoeted fields with line breaks.

So I know ho to solve this in principle: I need to first cut the file into "real" lines, respecting the possibility of line breaks within quoted and sometime even quoted fields with quotes in them, stash them in a collection, remove the first y and last x lines, and feed the rest into NeoCSVReader. The hard part is first identifying the "real" lines.

So #nextLine is not enough, and it seems like what I need to do is write something very similar to NeoCSVReader that knows how to distinguish between line breaks inside quoted fields and "real" line breaks. I wonder if there is anything around that can make my journey shorter...?

Joachim

Hi,

I hope I will not frustate you all by adding my experiences here.

I had a case where the CSV was created out of Excel. So it is common that  line breaks are part of a cell, e.g.

field1,field2,field3
1,2,"3
4,5,5"
6,7,8

gives a header and two lines with three cells each (from a Smalltalk inspector):

First line

('1' '2' '3
4,5,5')

Second line

('6' '7' '8')

The third cell of the first line contains , and line break.

So the whole story was a night mare, too.

Consider that the " in itself needs special treatment. Consider

"a",abcde"

"a",abcde"

should read as two lines (in a Smalltalk inspector)

('"a"' 'abcde"')

('"a"' 'abcde"')

And further

"a","ab,cde"

"a","abcde"","

should give you

('"a"' 'ab,cde')

('"a"' 'abcde",')

or

"abc","bde" garbage

should read as

('"abc"' '"bde"')

-> there may be garbage even within a CSV (better to say it is a malformed CSV)

-> nextLine definitily cannot handle this

-> I created a deterministic automaton to handle ", line breaks and the comma to find the meta - structure in a first pass. The idea is based on "lex" in Unix, may be the support for regular expressions in Smalltalk will help.

-> I isolated leading and trailing garbage on this meta structure.

-> I retransformed the meta structure back to s.th. which NeoCSVReader understands as second pass, as the chances to integrate this logic into NeoCSVReader are very low.

Note also that a very common deliverer of CSV, Excel, has even some more additional particularities:

° consider the ' at the beginning of cell as a meta symbol (in particular, when the first character in a cell should be =, but no a formula),

° cells are limited in size

° not to speak that Excel sometimes uses ; (or any other national defined delimiter) instead of ,.

Good luck!

Marcus

[hidden email] schrieb am Dienstag, 19. Januar 2021 um 10:03:04 UTC+1:

Hi there,

I guess some of you know the situation: you have to solve a simple task and fail over and over again. I am sitting in from of such a beast at the moment.

The task is easy: parse a CSV file which has a few leading and trailing lines that do not conform to the CSV format of the middle lines (let's call that part the payload of the file).

So all I have is a definition of the columns of the CSV payload part and the number of lines to ignore at the head and the tail of the file.

For the payload; I use NeoCSVReader.There is very little trouble with this, as long as I only use that payload part of the ReadStream for it.

Now to the "hard" part: ignoring irrevelant lines at the beginnig and the end of the file.

The problem is the same for the first and last lines, but the trainling lines are a bit harder, because you need to know that this upcoming line is the x-last or (x-1)-last line of the file.

The thing is: if the lines to be ignored were just lines, I coul used #nextLine. But in some cases they are CSV-lines which may or may not have the same number of columns as the payload lines. The hardest part is, however, that sometimes these lines are CSV (same or different number of columns as the payload) and contain qoeted fields with line breaks.

So I know ho to solve this in principle: I need to first cut the file into "real" lines, respecting the possibility of line breaks within quoted and sometime even quoted fields with quotes in them, stash them in a collection, remove the first y and last x lines, and feed the rest into NeoCSVReader. The hard part is first identifying the "real" lines.

So #nextLine is not enough, and it seems like what I need to do is write something very similar to NeoCSVReader that knows how to distinguish between line breaks inside quoted fields and "real" line breaks. I wonder if there is anything around that can make my journey shorter...?

Joachim

Hi

I did something vaguely similar long ago. I imported the csv file into a SQLite memory db and selected for the compliant lines. The SQLite memory db is very fast.

(Actually I did a SELECT INSERT into an error table all the non-compliant lines for reporting back to the user, and if the error table was empty then I would process the data.)

Vince