Importing A Tab-Delimited Text File Containing Double Quotes

[Sheldon]

I'm using DoCmd.TransferT ext acImportDelim.. . to import a tab-delimited
..txt file using an Import Specification file and that works fine. The
problem is that a few records could have double-quotes in one of the
ten fields and that causes an "Unparsable Record" error and the
remaining fields for those records are blank. This is part of a
process that should not require manual intervention i.e. using an
editor to remove the ". Is there a workaround that anyone knows of?
Thanks, Sheldon Potolsky

[Rich P]

One solution is to use VBA code instead of Docmd. Here is a routine for
reading a text file using VBA:

Sub ReadText()
Dim DB As DAO.Database, RS As DAO.Recordset
Dim str1 As String, str2() As String
Dim i As Integer

Set DB = CurrentDB
Set RS = DB.OpenRecordSe t("ImportTable" )

Open "C:\someDir\you rTextFile.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, str1
str2 = Split(str1, vbTab)
RS.AddNew
For i = 0 to Ubound(str2)
RS(i) = str2(i)
Next
RS.Update
Loop
Close #1
RS.Close
End Sub

What I am doing here is reading each line of the text file using the
Line Input method. This will read the entire line into str1. Then I
parse str1 into an array using the Split function. str2 is a string
array. Since your data is tab delimited I specify vbTab as the
delimeter for the Split function. Then I use DAO code to write the data
to a table. This assumes you have a table with the same number of
fields as the data in the textfile. DAO does not care if you have
quotes or any other weird symbols. I could do the same thing using Sql
which is a more efficient, but it becomes more complex for dealing with
quotes and weird symbols.

Using code is more reliable than DoCmd.Transfer. ..

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Sheldon]

Thanks Rich.
Sheldon