string delimiters not recognized

[Rocky]

I am trying to parse a text file into recordsets and then into access tables.
The text file is a file rescued from a crashed proprietary database program
and may contain corruptions. It does contain many empty fields between
delimiters. However, it is quite large and until I can get a few lines
transferred into Access I'm not sure what I have.
The problem at this moment is that some fields containing names have commas
in them and commas are the delimiter in this text file. The name fields are
surrounded by quotes " , but I am using the SPLIT function to read the text
file and it does not recognize a comma inside quotes as not a delimiter. As
a result, I get varying numbers of fields in each row of text file because of
the vagaries of name fields, (i.e. some have 3 names per name field, some
have none and everything in between) (I think). I tried to use Notepad to
find and replace all the " with "", but it choked on the size of the file
(757 fields per row and several thousand rows).
The code below was written to try to loop through strings delimited by " and
having comas inside the " delimiter, but the functions LEFT and RIGHT do not
recognzie the " when it is used as a string delimiter.
Any thoughts would be appreciated.
I apologize in advance for the length of this post and the code. Relatively
new at this and not sure how best to present problems.

Private Sub FillUpRecords()

....Dim statements...

strSourceFile = "C:\Documents and Settings\RRode\Desktop\summitcardiac.txt"
lngInputFile = FreeFile
Open strSourceFile For Input As lngInputFile

'Append the following lines as records
n = 0
i = 0
Set db = CurrentDb()

Set rst1 = db.OpenRecordset("Group1Table", dbOpenDynaset, dbAppendOnly)
Set rst2 = db.OpenRecordset("Group2Table", dbOpenDynaset, dbAppendOnly)
Set rst3 = db.OpenRecordset("Group3Table", dbOpenDynaset, dbAppendOnly)

Do
Line Input #lngInputFile, strInText
varInputLine = Split(strInText, ",")
If Not strInText = "" Then
rst1.AddNew
For i = 0 To rst1.Fields.Count - 1
If Left(varInputLine(n), 1) = Chr(34) Then
doc = varInputLine(n)
Do
n = n + 1
doc = doc & "," & varInputLine(n)
Loop Until Right(doc, 1) = Chr(34)
rst1(i) = doc
n = n + 1
End If
rst1(i) = varInputLine(n)
n = n + 1
Next
rst1.Update

....the code then loops through two other tables to finish each line of text,
about 750 fields...

Loop Until EOF(lngInputFile)

A sample of the text line:
"GONZALES,ESTEBAN,JR",,,,,4.0,No,,14102,4.0,,, ...

Thanks

 

[Diane]

I had a similar problem with importing a text file. I used word to
manipulate the file, using the special character for example you can
search for a tab character which is ^t etc. it was tedious at first
but soon managed to get it in a suitable format to import into access.

good luck.

 

[Ralph]

Maybe you could use the Replace function before you assingned it to the array.

Do
Line Input #lngInputFile, strInText
varInputLine=Replace(varInputLine,Chr(34),"")
varInputLine = Split(strInText, ",")
If Not strInText = "" Then