CSV Import(Bigtext, NO XMLPort, NO ExcelBuffer, NO Dataport)

mik

Hi!

As we all know importing files is sometimes a nice challenge. We managed it to import CSV Files with columns > 1024.

I talk about standard CSV
http://en.wikipedia.org/wiki/Comma-separated_values

Everything worked fine up to the point we got a file with line delimiters in the columns.
As Example

Working...
1234;"It's a Test;Try this at home!";5678;<CRLF>
"A;BC";DEFG;HIJK;<CRLF>

Error...
1234;"It's a Test;<CRLF>
Try this at home!";5678;<CRLF
"A;BC";DEFG;HIJK;<CRLF>

My solution:
I tried to get the quantity of my column delimiters (") in front of my first found <CRLF>.
If (ColumnDelimiterCounter MOD 2) = 0 :thumbsup: ELSE go on and search next <CRLF> and repeat searching for columnd delimiters.

Any idea for a better workaround?

greetings mik

Sog

Your workaround is easy enough, however your columndelimiter might also be in the expression itself.

When a reserved symbol is used in an import it's normal that it fails, a rule (I decided for myself) is to check the data on reserved symbols before formatting them into csv.

This isen't always required if you use a combination of symbols that seperate fields, namely [ ";" ] (between the brackets[] literal, quotes are part of it)
If you use multiple symbols, the data only has to be checked on that combination. Which decreases the chance of hitting a false positive delimiter.

mik

Yes the column delimiter could be in the column as a sign. But in the CSV specification it's like this ...

1997,Ford,E350,"Super, ""luxurious"" truck"

So my result will also be an even number of column delimiters.
I thought it is not the best way to parse this file.

The solution should not modify the file before importing. I also do not want to parse the file before my import. (performance)

(To convert my file (.xls) I use the "Excel Buffer". So I can be sure that I have a standard .csv file. Every Delimiter (column, expression, line) is correct)
http://en.wikipedia.org/wiki/Comma-separated_values

Sog

mik wrote:

...
So my result will also be an even number of column delimiters.
I thought it is not the best way to parse this file....

Well that's the beauty of programming. Sometimes a simple solution, compare and/or formula is all you need to achieve a desired effect.

But you mention a standard csv file, however there is no universal standard for that file (only the RFC 4180 since 2005, But that's more of a guideline to people who can't imagine anything with "CSV").
But I just mentioned the colum delimiter because quotes do not always come in pairs:

1996,Bridgestone,Wheel,"19""

this is different from your example, but you've escaped your quote on "luxurious", which will not happen when using excel to create csv files.

mik

Hi,

in my case i tried this ..

This is a "nice" Test
TV with 19"

Excel would do this for me ..

"This is a ""nice"" Test"
"TV with 19"""

In my scenario it should work. An if this is a "standard .CSV" we can tell our customers that we can handle standard .CSV and nothing else! No more need to be afraid of ";" in the column text :-k

Sog

Ah, It will probably be because there are multiple CSV options when saving a excel file.
The behaviour you describe is not detected when I save using the "csv (ms-dos)" option