read in .csv with line feed / carriage return

[Zest]

Hi,

I'm making a little database utility, for which I must read in .csv (comma separated values) files, which have been exported from Excel, into an array. I use the semicolon ( ; ) as a separator.

Each row has the exact same number of columns in Excel, but some of the cells contain multiline text

When I export the Excel sheet to .csv, the cells containing multiline text cause a new line to start in the .csv file.

Therefor using the StringSplit function doesn't work here when importing.

So some lines in the file have the right number of columns, others have too little columns, because some of them are on the next line.

One sidenote: Excel puts the contents of a multiline cell in parantheses ( " )

Example of a .csv file:

col1;col2;col3

one-one;one-two;one-three

two-one;"two-two is a long

multiline text.";two-three

three-one;three-two;three-three

The 3rd and 4th line should be on one row in the array.

Now the question: What would be a good approach to read such .csv files into and array?

I've tried several things, but keep ending up making way too complicated routines, which in the end don't work the way I want to....

Thanks for any help!

[SmOke_N]

I've done some work with them in the past http://www.autoitscript.com/forum/index.ph...highlite=%2Bcsv

[ReFran]

Hi,

attached one way to do it.

HTH, Reinhard

$sCsv = "col1;col2;col3"&@crlf& _
        "one-one;one-two;one-three"&@crlf& _
        "two-one;"&"""two-two is a long" &@crlf& _
        "multiline text."""&";two-three"&@crlf& _
        "three-one;three-two;three-three"

;; the csv you get as string from File
MsgBox(0,"What you Get",$sCsv)
;; read into an array
$aFirst = stringsplit(stringstripCr($sCsv),@lf)

;; correct the array to a temp string
DIM $sTemp = ""
for $i = 1 to $aFirst[0]
    $ahelper = stringsplit($aFirst[$i],";")
    if $ahelper[0] = 3 then 
        $sTemp &=$aFirst[$i]&@LF
    Else 
        $sDouble = stringReplace($aFirst[$i] &$aFirst[$i+1],'"','')
        $sTemp &=$sDouble&@LF
        $i += 1
    EndIf
Next
msgbox(0,"What you wanted",$sTemp)

;; split temp string into the final array
$aCsv = stringsplit($sTemp,@lf)

Edited March 4, 2008 by ReFran

[Zest]

Thank you both for your help!

I found out it is possible to read directly from the Excel sheet, which is a LOT easier when working with multiline cells, so I'm 'rewriting' the script to use Excel directly.

Nevertheless ReFran your solution also does about 95% of what I needed. I'm defenitely saving that one for possible use in a future script, because I'm quite sure this was not the last time I'm using .csv files or .ini files...

to you you both for the help!