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
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
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...
Brian
Send me a csv file to read and I'll have a look at it.
Can you give an exact few lines for us to look at?
I know the code works, because I use it.
Bill
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
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:
QuoteD
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
I added a 2nd input line and the new output file contains:
Quotethe 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:
Quotethe 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.
Test input:
QuoteD,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:
Quote<>
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
this last version works good for me also.
Glad I could help.
Take care,
Bill
Thanks to both of you - I'll take a look tonight when work doesn't get in the way!
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
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....
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
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?)
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..
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
Thanks, Aurel - I'll get my thinking head on, and have a look!
Brian