TransferText "Unparsable Record" error

[Ted]

Hi all, i'm using Access to import a .csv file. i'm using the TransferText
method but i'm missing some records due to them being "Unparsable". It's
because some of the records have the double quotes in one of the fields in
the .csv file. Any ideas on how to get around this? Perhaps a macro that
opens the .csv file in Excel searches for all quotes, deletes them and
closes the file. I don't know..i'm really grasping at straws here.

i tried the following code
Set DB = CurrentDb
Set RS = DB.OpenRecordSet("KISClaims-BWUR_csv")

strFileName = txtFileToBeImported

LineNum = 0

Open strFileName For Input As #1
Do While Not EOF(1)
LineNum = LineNum + 1
Line Input #1, str1
If LineNum > 1 Then
str2 = Split(str1, ",")
RS.AddNew
For i = 0 To UBound(str2)
RS(i) = str2(i)
Next
RS.Update
End If
Loop
Close #1
RS.Close

but was getting some data type errros with that as well and it brings in all
text fields with quotes around it.

Any help would be very much appreciated.
TIA
Ted

 

[Stephen Raftery]

You need to change all the double-quotes to single-quotes.
Open the csv file in Excel, select the column in question, and Replace all
the " marks with ' marks.


Stephen

 

[Ted]

That's what i was thinking but i'd like to do it proggramatically so the
user won't have to do it. I'm a little weak in Excel VBA