Parsing a line

[Brian]

Hi,

I thought I would end this competition of seeing how long the forum could go without having a post! Have I won?

Anyway, I am reading this whole line into a single variable, for display in a listview. When inserted into the listview,
I am coming to grief when it hits the double quotes, and comma inside the quotes. Of course, the comma pushes
the text into the next listview field. How can I parse a line like this, removing the quotes, and removing the commas,
so that it displays properly?

There should be 17 columns inserted (0 to 16)

D,29/05/2013,Mr,John ,,Doe,"Flat One, Wherever ","Whatever Street, Wherever",Whatever Town,Whatever County,ZZ00 0ZZ,,1234567890,09/08/1965,ABCD11EF01,JD1500,X

Any ideas for a short routine?

Brian

[billhsln]

I have had to read STANDARD CSV files with extra "'s and commas between "'s.  You run into address's in Washington DC of 13 Avenue "A", which ends up looking like:
Name,Address,CityStateZip
"Smith, Sam","13 Avenue """A"","Washington, DC  01000"

Gives:

Name = Smith, Sam
Address = 13 Avenue "A"
CityStateZip = Washington, DC  01000

You will still need to check for Comma's within the split up fields (Name and CityStateZip)and remove them.

Will also parse:

Name,Address,City,State,Zip
"Smith, Sam","13 Avenue """A"","Washington","DC","01000"

Gives:

Name = Smith, Sam
Address = 13 Avenue "A"
City = Washington
State = DC
Zip = 01000

I use this and it does work as expected.

Bill

[Brian]

Thanks, Bill - giving it a go now!

Brian

Edit: Screws up badly, asking me to send a love letter to Microsoft! Outputs two headers line, two records, another
header line, same two records as the first two, and carries on in that vein until it bombs. Really confused now...

[LarryMc]

Brian
Send me a csv file to read and I'll have a look at it.

[billhsln]

Can you give an exact few lines for us to look at?

I know the code works, because I use it.

Bill

[billhsln]

Parsed your example:

----
D
29/05/2013
Mr
John

Doe
Flat One, Wherever
Whatever Street, Wherever
Whatever Town
Whatever County
ZZ00 0ZZ

1234567890
09/08/1965
ABCD11EF01
JD1500
X
-----

The above is what it split up and printed out.

Bill

[LarryMc]

Bill
I ran your code above and inputed a test file with one line of the data Brian showed above.
Your code printed TWO copies of the converted data to the output file:

D
29/05/2013
Mr
John

Doe
Flat One, Wherever
Whatever Street, Wherever
Whatever Town
Whatever County
ZZ00 00Z

1234567890
09/08/1965
ABCD11EF01
JD1500
X
D
29/05/2013
Mr
John

Doe
Flat One, Wherever
Whatever Street, Wherever
Whatever Town
Whatever County
ZZ00 00Z

1234567890
09/08/1965
ABCD11EF01
JD1500
X

[LarryMc]

I added a 2nd input line and the new output file contains:

the 1st records data
the 1st records data again
the 2nd records partial data

I added a 3rd input line and the new output file contains:

the 1st records data
the 1st records data again
the 2nd records partial data
the 1st records data again
the 2nd records partial data
the 3rd records data

So there is definitely a problem somewhere.

[billhsln]

Test input:

D,29/05/2013,Mr,John ,,Doe,"Flat One, Wherever ","Whatever Street, Wherever",Whatever Town,Whatever County,ZZ00 0ZZ,,1234567890,09/08/1965,ABCD11EF01,JD1500,X
B,04/07/19xx,Mr,Bill,K,Haesslein,xxxx Streets Dr,N. Richland Hills,TX,76180,"The End"

Resulting output:

<>
D,29/05/2013,Mr,John ,,Doe,"Flat One, Wherever ","Whatever Street, Wherever",Whatever Town,Whatever County,ZZ00 0ZZ,,1234567890,09/08/1965,ABCD11EF01,JD1500,X
<>
D
29/05/2013
Mr
John

Doe
Flat One, Wherever
Whatever Street, Wherever
Whatever Town
Whatever County
ZZ00 0ZZ

1234567890
09/08/1965
ABCD11EF01
JD1500
X
<>
B,04/07/19xx,Mr,Bill,K,Haesslein,xxxx Streets Dr,N. Richland Hills,TX,76180,"The End"
<>
B
04/07/19xx
Mr
Bill
K
Haesslein
xxxx Streets Dr
N. Richland Hills
TX
76180
The End

I did make a couple of minor changes.  Have attached code.

Bill

[LarryMc]

this last version works good for me also.

[billhsln]

Glad I could help.

Take care,
Bill

[Brian]

Thanks to both of you - I'll take a look tonight when work doesn't get in the way!

Brian

[Brian]

Hi,

Very annoying! If you download this freeware application:
http://www.nirsoft.net/utils/csv_file_view.html
you will find that it will open any CSV file, no matter the combination of commas,
commas within quoted fields, etc, and gets it right every time

What is the author doing or knows what to do that I don't when reading in a CSV file?

Brian

[aurelCB]

Brian i am not sure what this guy know ...
when i catch some time i will convert my parser from o2 to EB/IWB and
maybe will parse properly comma delimited strings....

[Brian]

That would be good, Aurel

You can also download the more professional looking CSVed, which is also freeware,
and he gets it right, as well!

http://csved.sjfrancke.nl/index.html

Brian

[aurelCB]

Brian...
Here is short one.
Infact you need two functions...
Tally( string,delimiter)
ParseArgs() - > who parse into array of arguments (strings)..
it is in o2 but it is easy translate ( ahh..basic is basic ,right?)

Code Select Expand

FUNCTION Tally(STRING Main$,STRING Match$) As INT
PC = 1
'print "TALLY:" + main$
INT n
For n=1 TO 20:dPos[n]=0:Next n

    Dim i,j,q,mlen,matchlen As INT
Dim t$ As STRING
    mlen = Len(Main$)
    matchlen = Len(Match$)
    i = 1
    j = 0
    q = 0
    If (mlen = 0) Or (matchlen = 0)
        Return j
    End If
   
    do

t$ = Mid(Main$,i,matchlen)
If t$ = Chr(34) THEN q = q + 1
If q=2 THEN q = 0
        If t$ = Match$ And q=0
           j++
'mem del$ position
dpos[j]=i
  'j = j + 1
         
        End If

           i++
        If i > mlen then
           exit do
        End If
    End do
'tbreak:
    Return j
End FUNCTION


replace do/end do with DO/UNTIL and will work..
and..

Code Select Expand

SUB ParseArgs
INT n,id:'clear arguments array
'+PC  is a Program Counter
For n=0 TO 20:arg[n]="":Next n

string t$=tBuffer
t$=Trim(t$)

INT k,i,j
i = 0
j = 1
k = 1

For i = 1 TO Len(t$)
If i = dPos[k]

arg[k] = Mid(t$,j,(i-j))
   arg[k] = Trim(arg[k])    
k=k+1
j = i + 1
   
         
End If
Next
'+ extract last argument
arg[k] = Mid(t$,j,Len(t$))
arg[k] = Trim(arg[k])

'fill source table >>>>>
eID = eID + 1
arg1[pc]=arg[1] : SendMessage hEdit[1],WM_SETTEXT,0,strptr arg[1]
arg2[pc]=arg[2]: SendMessage hEdit[2],WM_SETTEXT,0,strptr arg[2]
arg3[pc]=arg[3]: SendMessage hEdit[3],WM_SETTEXT,0,strptr arg[3]
arg4[pc]=arg[4]: SendMessage hEdit[4],WM_SETTEXT,0,strptr arg[4]
arg5[pc]=arg[5]
arg6[pc]=arg[6]
arg7[pc]=arg[7]
arg8[pc]=arg[8]
arg9[pc]=arg[9]
arg10[pc]=arg[10]
arg11[pc]=arg[11]
arg12[pc]=arg[12]
arg13[pc]=arg[13]
arg14[pc]=arg[14]
arg15[pc]=arg[15]
arg16[pc]=arg[16]

temp$=""

END SUB

so put each argument into one listview item and that is

[Brian]

Thanks, Aurel - I'll get my thinking head on, and have a look!

Brian