StringSplit ignore character in quotes?

[MikelSevrel]

I'm trying to import CSV data, but unfortunately there are commas in quotes that are not meant as delimiters. So I'm trying to figure out a way to ignore the commas within quotes. Anyone have any idea on how I might go about this?

Edited March 6, 2006 by MikelSevrel

[herewasplato]

I'm trying to import CSV data, but unfortunately there are commas in quotes that are not meant as delimiters. So I'm trying to figure out a way to ignore the commas within quotes. Anyone have any idea on how I might go about this?

Just off the top of my head:

Read the entire file into one AutoIt variable via FileRead

Then do a string replace on "," - replace it with something like "_"

Then parse your data as desired - perhaps via stringsplits

Then replace "_" with ","

might work - might not...

[MikelSevrel]

Well, see there is other text within the quotation marks as well.

[Valuater]

Well, see there is other text within the quotation marks as well.

maybe you just found an answer

string split the quotation marks??

8)

[herewasplato]

Well, see there is other text within the quotation marks as well.

Yeah, I thought that was too simple of a solution.

Can you show a sample of the data?

If it is like this:

some words, some other words, address "here, there", more stuff

then you might have to read each character, act on each comma - unless preceded by a "

and then there is StringRegExp

[MikelSevrel]

Right now I'm just using various sampls of CVS found on the web. I'm trying to set up an import utility into my program, here's the kidn of data I was meaning:

REMOVED

EDIT: oops, that's not one of the problem ones.

Here:

"Joan, turn ""left""",Jet,"9th, at Terrace plc",Desert City,CO,00123

Should come out:

Joan, turn "left"|Jet|9th, at Terrace plc|Desert City|CO|00123

Edited March 6, 2006 by MikelSevrel

[MHz]

Something like this may do (not tested)

Global $file, $array, $final, $i, $split, $start, $final
$file = FileRead('file.csv', FileGetSize('file.csv'))
If Not @error Then
    $file = StringReplace($file, '""', '||')
    $array = StringSplit($file, '"')
    If Not @error Then
        $file = ''
    ; See if 1st element is quoted
        If StringLeft($array[1], 1) = '"' Then
            $start = 1
        Else
            $start = 2
        EndIf
    ; Replace the quotes in the elements with pipes
        For $i = $start To $array[0] Step 2
            $array[$i] = StringReplace($array[$i], ',', '|')
        Next
    ; Return the array elements back into a single variable
        For $i = 1 To $array[0]
            $file = $file & $array[$i]
        Next
    ;; Split the variable into an array with the pipes.
        $split = StringSplit($file, ',')
        If Not @error Then
            For $i = 1 To $split[0]
                $split[$i] = StringReplace($split[$i], '|', ',')
            Next
        EndIf
    EndIf
EndIf

; The array $plit holds your final elements.

Edit2:

I should say that $final is the variable that should hold the contents of the csv file.

Edit3:

Added extra $final to accumulate the array into a variable at the end.

Edit4:

Added comments and now see a problem, so change the last lines to fix it.

Edited March 6, 2006 by MHz

[MikelSevrel]

Worked pretty darn well, thanks.