Jump to content
Sign in to follow this  
Zest

read in .csv with line feed / carriage return

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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 by ReFran

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.