Jump to content

Processing Thunderbird exported CSV file

Recommended Posts


I want to process my Thunderbird contacts data. I exported it to a CSV file and thought I could use the FileReadToArray() function to read the data. The problem is that the Thunderbird csv data contains x0A line breaks quoted in some of the fields (like the "notes" field) and uses @crlf (x0D0A) as end of line delimiters at end of each record. The functions simply see the @lf (x0A) line breaks inside the notes fields and considers it an end of record for the csv file. The FileRead() function works in a similar manner.

#include <array.au3>
Local $aLines = FileReadToArray('d:\temp\Personal Address Book.csv')
For $i = 0 To UBound($aLines) - 1 ; Loop through the array.
    ConsoleWrite($i & ': ' & $aLines[$i] & @crlf ) ; Display the contents of the array.

How can I process the csv file, allowing the x0A to be rightfully considered as part of the quoted field data and not the end of the record?

Edited by wysocki

Share this post

Link to post
Share on other sites

If you attach a sample file I'll have a go.

Can you read the whole thing into a variable, switch out the x0a, x0d with crlf then the below should work:

#include <Array.au3>

$string = "1,2,3" & @CRLF & "4,5,6" & @CRLF & "7,8,9" & @CRLF & "10,11,12"
$array = StringRegExp($string,"(.+)\R*",3)      ;1d array with each line as 1 element
$one_row = StringRegExp($array[0],"(\w+),*",3)  ;1 element of above array split into array by ,
$cols = Ubound($array,1)                        ;number of columns
$rows = Ubound($one_row)                        ;number of rows

dim $aResult[$cols][$rows]

for $x = 0 to $cols-1
    $aRow = StringRegExp($array[$x],"(\w+),*",3)
    for $y = 0 to $rows-1
        $aResult[$x][$y] = $aRow[$y]
        _ArrayDisplay($aResult,"aResult Col" & $x & "  Row" & $y)


edited.... ok so I admit I clearly didn't read to the end of your post....

You can't split on the CRLF i guess so if there's a fixed number of columns in there, use stringregexp to bust it up into a 1d array by ",".... divide the Ubound of that array by the number of columns to get the rows, make a new array of rows x cols and push it all into there.  I'm out of here.  It's Friday night  wooohooo!!!!

Edited by gruntydatsun
lack of reading combined with premature answering

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