By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Importing A Tab-Delimited Text File Containing Double Quotes

P: n/a
I'm using DoCmd.TransferText 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

Dec 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.OpenRecordSet("ImportTable")

Open "C:\someDir\yourTextFile.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 ***
Dec 7 '06 #2

P: n/a

Thanks Rich.
Sheldon

Dec 7 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.