Parsing records

I’m reading a csv file, spitting records and building an array where a comma is the delimiter (in the record). However, some of the fields also contain commas, which cause the number of elements in the array to differ from record to record. I’d appreciate any suggestions to parse a file to insert into a db that works around the problem of a delimiter being the same as a valid character in a record.

tia
cwest1Asked:
Who is Participating?
 
QJohnsonConnect With a Mentor Commented:
If you know the data in each field, you can make more meaningful names than I can, of course.  But let's say these records hold the following fields:
strCompanyID
strCompanyName
strNickname
intDaysHeld
strContact
strPosition
strDateLastContact
strEmptyStringForSomeReason
lngLowPrice
dblHighPrice
intStatus
strRecommendation
dblPERatio
sglEmptyNumericForSomeReason
lngFlags
intA
intB
sglSomeValue
intC

To read each record just open the file for input as usual:

dim intHandle as integer

intHandle = FreeFile

Open "yourFileSpecHere" for Input as #intHandle

Do While Not EOF(intHandle)

      Input #intHandle, strCompanyID, strCompanyName, intrNickname, _
         intDaysHeld, strContact, strPosition, strDateLastContact, _
         strEmptyStringForSomeReason, lngLowPrice, dblHighPrice, _
         intStatus, strRecommendation, dblPERatio, sglEmptyNumericForSomeReason, _
         lngFlags, intA, intB, sglSomeValue, intC
      ' Do something with the variables before reading next record
     
Loop

A shorter explanation could have been shorter (and pasted from MS Help, in fact) but would probably not have been as useful, right?

<g>

Good luck,

Q
0
 
DocMCommented:
Hy.
Is it possible that the comma used as a delimiter is followed by a space and the coma within a record is never followed by a space?
Just an idea. That would solve your problem.
0
 
cwest1Author Commented:
Unfortunately there are instances where the delimiter and a comma within a field don't have any space around them. Good thought though :)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DocMCommented:
And I suppose you can't create a new csv file with a different delimiter.
0
 
cwest1Author Commented:
I suspect that's about my only choice short of some ingeniuos parsing function.
0
 
QJohnsonCommented:
Are the number of fields of data the same for each line.  Sounds like they are from your surprise at those lines which have extra fields on them.

If so, you can read them all into separate variables with the Input# statement rather than reading the whole line and splitting it yourself.  The Input# statement expects the text fields to be surrounded by quotes and will ignore embedded commas in them (this is the original standard for comma-delimited ASCII files - and most programs support it if you look through their output options long enough).  They expect no commas around numeric fields.

So, are those text fields surrounded by the quotation marks?
0
 
cwest1Author Commented:
Here's a typical record. As you can see only some of the fields are enclosed in quotes, which as you say are the text fields. However, in the example the date field is also surrounded in quotes, but I can fix with a Replace(). QJohnson, do you have an example of Input# syntax? I'm unfamiliar with it.

"90331S109","US LEC CORP","CLEC",286,"AAB, RICHARD THOMAS","Chairman","20030311","",20000,53000.0000,1,"Sell",2.65,,4400847,1,0,.45,4
0
 
cwest1Author Commented:
Thanks Q. Why read then manual when you're around :)
0
 
cwest1Author Commented:
For those who may read this at a later time, here's a reference from Microsoft: http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_textwiz.asp
0
All Courses

From novice to tech pro — start learning today.