Sign in to follow this  
Followers 0
Zest

read in .csv with line feed / carriage return

4 posts in this topic

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



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


Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

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  
Followers 0