Unparsable Record error 1

[maheshkava]

all,
I am trying to import a tab delimited text file into a access table.The
field names & values are seperated by double quotes.I am using the
TransferText method for this purpose:
AccessApp.DoCmd.TransferText
acImportDelim, "", "aggregated", "c:\test1.txt", True
The first row of the text file are the column names.
Only the first column is imported correctly for the rest
i get the 'Unparsable Record error'.
Any help would be appreciated
Thankx in advance
mahesh

 

[vb6novice]

According to the Access Help Files, the second argument for specificationname is for an import/export spec you have created in the database. Your use of double quotes for this argument may be misinterpreted as specifying a spec that has a zero length name, in which case it will not be able to find it. Try removing the double quotes from your statement:

AccessApp.DoCmd.TransferText
acImportDelim, , "aggregated", "c:\test1.txt", True

 

[maheshkava]

Thankx vb6novice for ru tip..i appreciate that, but i have already tried that out by leaving the specificationname blank & also using double quotes.But i still get the Unparsable Record import error.
mahesh

 

[VBslammer]

Your description of the problem is contradictory. You say 'tab delimited' and then 'separated by double quotes.' So, what is the true structure of the file? A delimited file can only use 1 character as its delimiter, typically a space, tab, comma, etc.

When you say 'tab delimited' does that mean the file uses tabs as delimiters?

If the file uses Char(34) as a delimiter, its usage must be the same as any other delimiter, such as:

Value1__Value2__Value3__Value4

or

Value1"Value2"Value3"Value4

The key to your import is setting up an import specification using the 'File -> Get External Data -> Import' menu. You have to open your text file using the import wizard, set all of the correct options, and then before you click finish, you must click the 'Advanced' button and save the spec using 'Save As.'

Once you create a valid spec that imports your text file properly, then you can use the name of that spec in your TransferText method. In other words, the Specification argument is not asking you for a delimiter, it is asking you for the name of the specification you created using the import wizard, which has all the necessary information in it to properly handle your import.

I did a sample import using a text file that was formatted like this:

"value1" "value2" "value3" "value4"
"value5" "value6" "value7" "value8"
"value9" "value10" "value11" "value12"

When I created my spec, I used a {space} as the delimiter, and made sure to set the Text Qualifier to {"}. Then I checked the 'First Row Contains Field Names' checkbox. The import worked as expected.

The thing to remember is to click the Advanced button and then click Save As to name and save your spec before you finish the wizard, or else your settings are treated as one-time only and discarded after the import.

Having named your specs, you can now perform the import as expected:

DoCmd.TransferText acImportDelim, "ImportTest Import Specification", "aggregated", "C:\ImportTest.txt", True

Have fun


VBSlammer

 

[maheshkava]

Thankx VBSlammer for ur help..it did work(if i create a ImportSpecification).
BUT..I have to create a new Database for every text file i import,which means that i have to create a new database manually & then create a ImportSpecification for the db every time i need to import a file.
That means that the entire purpose of automating the process is not met.
FYI again i m using TAB as the delimiter and double quotes as Text Qualifier.
Thankx in advance
mahesh

 

[VBslammer]

You can create an import spec programmatically with a little work. Access stores the import specs in a hidden table called 'MSysIMEXSpecs.' If you are creating a database every time you import, the new database will not have this table, so you must create one on-the-fly.

Here is a sample function that creates the 'MSysIMEXSpecs' table and fills it with a custom specification:


Function AddSpec() As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim idx As DAO.Index

Set db = CurrentDb

Set tdf = db.CreateTableDef("MSysIMEXSpecs&quot

With tdf

.Fields.Append .CreateField("DateDelim", dbText, 2)
.Fields.Append .CreateField("DateFourDigitYear", dbBoolean)
.Fields.Append .CreateField("DateLeadingZeros", dbBoolean)
.Fields.Append .CreateField("DateOrder", dbInteger)
.Fields.Append .CreateField("DecimalPoint", dbText, 2)
.Fields.Append .CreateField("FieldSeparator", dbText, 2)
.Fields.Append .CreateField("FileType", dbInteger)
.Fields.Append .CreateField("SpecID", dbLong)
.Fields("SpecID&quot.Attributes = dbAutoIncrField ' Autonumber
.Fields.Append .CreateField("SpecName", dbText, 64)
.Fields.Append .CreateField("SpecType", dbByte)
.Fields.Append .CreateField("StartRow", dbLong)
.Fields.Append .CreateField("TextDelim", dbText, 2)
.Fields.Append .CreateField("TimeDelim", dbText, 2)
db.TableDefs.Append tdf
db.TableDefs.Refresh

Set idx = db.TableDefs(tdf.Name).CreateIndex
With idx
.Name = "PrimaryKey"
.Fields.Append .CreateField("SpecName&quot
.Unique = True
.Primary = True
End With
.Indexes.Append idx
.Indexes.Refresh
End With

Set rst = db.OpenRecordset(tdf.Name)
With rst
.AddNew
.Fields("DateDelim&quot = "/"
.Fields("DateFourDigitYear&quot = True
.Fields("DateLeadingZeros&quot = False
.Fields("DateOrder&quot = 2
.Fields("DecimalPoint&quot = "."
.Fields("FieldSeparator&quot = " " ' space
.Fields("FileType&quot = 437 ' Excel 5
.Fields("SpecName&quot = "ImportTest Import Specification"
.Fields("SpecType&quot = 1
.Fields("StartRow&quot = 1 ' 0 for 'No Header Row'
.Fields("TextDelim&quot = Chr(34)
.Fields("TimeDelim&quot = ":"
.Update
End With

AddSpec = True

ExitHere:
Set tdf = Nothing
Set db = Nothing
Set idx = Nothing
Set rst = Nothing
Exit Function
ErrHandler:
MsgBox "Error: " & Err & " - " & Err.Description
Resume ExitHere
End Function VBSlammer

 

[maheshkava]

hey VBSlammer!
thankx for all your help but m getting a Runtime error 3625 'The text file specification ImportTest Import Specification does not exist' on this line
DoCmd.TransferText acImportDelim, "ImportTest Import Specification", "aggregated", "C:\test1.txt", True
but it does create the MSysIMEXSpecs table in the db with 'importTest Import Specification' as a value in the SpecName column.
can't figure out why it wont read the specification.
mahesh

 

[VBslammer]

You're right, there is another table I overlooked that gets created when you use the spec wizard (MSysIMEXColumns). If your import file has header names, you must either know them in advance or create a parser that will extract them into variables you can pass to the 'AddSpecs' function.

In the following example, I imported a table with only 4 columns, with headings of 'Hello' and 'World' and 'Import' and 'Test.' If you don't create the heading names and put them in the MSysIMEXColumns table your import table will get default headings starting with F1, F2, etc. Here is an updated script:

Sub TransferIt()
Call AddSpecs("Hello", "World", "Import", "Test&quot
DoCmd.TransferText acImportDelim, "ImportTest Import Specification", "ImportTemp", "C:\ImportTest2.txt", True
End Sub

Function AddSpecs(ParamArray ColumnNames() As Variant) As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim idx As DAO.index
Dim idx2 As DAO.index
Dim i As Integer

Set db = CurrentDb

' Make the spec table.
Set tdf = db.CreateTableDef("MSysIMEXSpecs&quot

With tdf

.Fields.Append .CreateField("DateDelim", dbText, 2)
.Fields.Append .CreateField("DateFourDigitYear", dbBoolean)
.Fields.Append .CreateField("DateLeadingZeros", dbBoolean)
.Fields.Append .CreateField("DateOrder", dbInteger)
.Fields.Append .CreateField("DecimalPoint", dbText, 2)
.Fields.Append .CreateField("FieldSeparator", dbText, 2)
.Fields.Append .CreateField("FileType", dbInteger)
.Fields.Append .CreateField("SpecID", dbLong)
.Fields("SpecID&quot.Attributes = dbAutoIncrField ' Autonumber
.Fields.Append .CreateField("SpecName", dbText, 64)
.Fields.Append .CreateField("SpecType", dbByte)
.Fields.Append .CreateField("StartRow", dbLong)
.Fields.Append .CreateField("TextDelim", dbText, 2)
.Fields.Append .CreateField("TimeDelim", dbText, 2)
db.TableDefs.Append tdf
db.TableDefs.Refresh

Set idx = db.TableDefs(tdf.Name).CreateIndex
With idx
.Name = "PrimaryKey"
.Fields.Append .CreateField("SpecName&quot
.Unique = True
.Primary = True
End With
.Indexes.Append idx
.Indexes.Refresh
End With

Set rst = db.OpenRecordset(tdf.Name)
With rst
.AddNew
.Fields("DateDelim&quot = "/"
.Fields("DateFourDigitYear&quot = True
.Fields("DateLeadingZeros&quot = False
.Fields("DateOrder&quot = 2
.Fields("DecimalPoint&quot = "."
.Fields("FieldSeparator&quot = " " ' space
.Fields("FileType&quot = 437 ' Excel 5
.Fields("SpecName&quot = "ImportTest Import Specification"
.Fields("SpecType&quot = 1
.Fields("StartRow&quot = 1 ' 0 for 'No Header Row'
.Fields("TextDelim&quot = Chr(34)
.Fields("TimeDelim&quot = ":"
.Update
End With

' Make the columns table
Set tdf2 = db.CreateTableDef("MSysIMEXColumns&quot

With tdf2
.Fields.Append .CreateField("Attributes", dbLong)
.Fields.Append .CreateField("DataType", dbInteger)
.Fields.Append .CreateField("FieldName", dbText, 64)
.Fields.Append .CreateField("IndexType", dbByte)
.Fields.Append .CreateField("SkipColumn", dbBoolean)
.Fields.Append .CreateField("SpecID", dbLong)
.Fields.Append .CreateField("Start", dbInteger)
.Fields.Append .CreateField("Width", dbInteger)
db.TableDefs.Append tdf2
db.TableDefs.Refresh

Set idx2 = db.TableDefs(tdf2.Name).CreateIndex
With idx2
.Name = "PrimaryKey"
.Fields.Append .CreateField("FieldName&quot
.Fields.Append .CreateField("SpecID&quot
.Unique = True
.Primary = True
End With
.Indexes.Append idx2
.Indexes.Refresh
End With

Set rst = db.OpenRecordset(tdf2.Name)

For i = 0 To UBound(ColumnNames)
With rst
.AddNew
.Fields("Attributes&quot = 0
.Fields("DataType&quot = dbText
.Fields("FieldName&quot = ColumnNames(i)
.Fields("IndexType&quot = 0
.Fields("SkipColumn&quot = 0
.Fields("SpecID&quot = 1 ' The spec table P.K.
.Fields("Start&quot = i
.Fields("Width&quot = Null
.Update
End With
Next i

AddSpecs = True

ExitHere:
Set tdf = Nothing
Set db = Nothing
Set idx = Nothing
Set rst = Nothing
Exit Function
ErrHandler:
MsgBox "Error: " & Err & " - " & Err.Description
Resume ExitHere
End Function VBSlammer

 

[AppStaff]

nice post

 

[maheshkava]

Thankx VBSlammer for all ur help,time & patience.I really appreciate that.IT DID WORK!!