>Hi All
>
>I have a CSV file which uses comma as the primary delimiter between fields. However, some fields might contain a comma (e.g. a description field) and the supplier provides those fields enclosed in quotes. For example:
>
>AAA,BBB,"XXX YYY, ZZZ",CCC,"X1, Y1, Z1",DDD,
>
>I cannot do an import on this CSV file as it contains too many fields. I need to parse the file, line by line.
>
>How can I get each field so that the result would be:
>
>Field 1: AAA
>Field 2: BBB
>Field 3: XXX YYY, ZZZ
>Field 4: CCC
>Field 5: X1, Y1, Z1
>Field 6: DDD
>
>GETWORDNUM() works for fields separated with a comma but then it picks up individual sections in the description fields which are enclosed in quotes. I can write a manual parsing routine but was wondering if there was a faster way.
The only reliable way I have found is to
(1) copy the line, then
(2) replace the double quotes and everything between them with eg ~ (by pairs of " )
(3) find the position of every comma ( use a regex )
(4) split the original line using the positions of every comma of (3)
>
>TIA
Gregory