?
Solved

Parsing records

Posted on 2003-03-17
9
Medium Priority
?
132 Views
Last Modified: 2010-05-03
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
0
Comment
Question by:cwest1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 3

Expert Comment

by:DocM
ID: 8155110
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
 

Author Comment

by:cwest1
ID: 8155279
Unfortunately there are instances where the delimiter and a comma within a field don't have any space around them. Good thought though :)
0
 
LVL 3

Expert Comment

by:DocM
ID: 8155331
And I suppose you can't create a new csv file with a different delimiter.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:cwest1
ID: 8155496
I suspect that's about my only choice short of some ingeniuos parsing function.
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8155877
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
 

Author Comment

by:cwest1
ID: 8156237
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
 
LVL 3

Accepted Solution

by:
QJohnson earned 400 total points
ID: 8156936
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
 

Author Comment

by:cwest1
ID: 8159435
Thanks Q. Why read then manual when you're around :)
0
 

Author Comment

by:cwest1
ID: 8180592
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question