How to detect unparsable records

[Guest]

I am using a DoCmd.TransferText method to import text files into a table
using an import specification that expects a comma delimited text file. This
works fine when the text file is comma delimited, but when a tab delimited
file is selected, the text is loaded anyway, resulting in garbage data in the
table, and the creation of an ImportErrors table.

Is there a way for vba code to detect if a text file matches the import
secifications prior to attempting an import? At the very least I would like
to know if tab, rather than comma delimiters are used in the file, then I
could specify an alternate import specification, or at least alert the user
that the text file is in the wrong format.

Thanks for any suggestions,

TK

 

[Guest]

I guess you could open the file and read through it looking for the
characters you want and take the action necessary based on what you find.

 

[Guest]

Thanks for the post.

I was hoping that there is a trappable error or a way to detect that Access
wants to create an ImportError file before this occurs. If possible, this
would allow another guard against loading bad or incomplete data, which could
occur even if the correct delimiter is used in the file.

If it is necessary to open and scan the text file for the delimiters can you
provide an example of the syntax involved in opening and searching a text
file? My grasp of file manipulation with vba is pretty basic, having always
used the TransferText method.

Thanks for your help,

TK

 

[Guest]

Is there a way for vba code to detect if a text file matches the import

secifications prior to attempting an import?

No. But if one were to execute an append query within a VBA procedure using
a tab-delimited file without a schema.ini file to govern the field types,
Error #3127 occurs. And if a make table query is executed within a VBA
procedure using a tab-delimited file without a schema.ini file to govern the
field types, Error #3061 occurs. Both of these errors are trappable in an
error handler. And the import will be prevented when either of these errors
occur, so one doesn't have to worry about garbage data being imported by
accident.

Here are two sample queries using a text file named MyData.txt (with
headers) located in the C:\Data directory:

qryAppendFromTextFile:

INSERT INTO tblMyTable
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MyData.txt;

qryMakeTblFromTextFile:

SELECT State, City, Status
INTO tblMyTable
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MyData.txt;

And here's the VBA code to execute either one of these queries (with the
make table query code currently commented out):

Public Sub importTextFile()

On Error GoTo ErrHandler

CurrentDb().Execute "qryAppendFromTextFile", dbFailOnError
'CurrentDb().Execute "qryMakeTblFromTextFile", dbFailOnError

Exit Sub

ErrHandler:

If (Err.Number = 3127) Then ' Use this when appending to existing
tables.
MsgBox "The import file is tab-deliminated. Please ensure" & vbCrLf
& _
"that only comma-separated files are imported.", vbCritical +
vbOKOnly, _
"Import Failed!"
'If (Err.Number = 3061) Then ' Use this when creating new tables.
' MsgBox "The import file may be tab-deliminated. Please ensure" &
vbCrLf & _
"that only comma-separated files are imported.", vbCritical +
vbOKOnly, _
"Import Failed!"
Else
MsgBox "Error in importTextFile( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear

End Sub

Of course, with the append query, the table tblMyTable must already exist.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Thanks!

That will help alot. I had forgotten that an external ISAM reference could
be used in a query.

Is there a way for vba code to detect if a text file matches the import
secifications prior to attempting an import?

No. But if one were to execute an append query within a VBA procedure using
a tab-delimited file without a schema.ini file to govern the field types,
Error #3127 occurs. And if a make table query is executed within a VBA
procedure using a tab-delimited file without a schema.ini file to govern the
field types, Error #3061 occurs. Both of these errors are trappable in an
error handler. And the import will be prevented when either of these errors
occur, so one doesn't have to worry about garbage data being imported by
accident.

Here are two sample queries using a text file named MyData.txt (with
headers) located in the C:\Data directory:

qryAppendFromTextFile:

INSERT INTO tblMyTable
SELECT *
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MyData.txt;

qryMakeTblFromTextFile:

SELECT State, City, Status
INTO tblMyTable
FROM [TEXT;HDR=YES;DATABASE=C:\Data\].MyData.txt;

And here's the VBA code to execute either one of these queries (with the
make table query code currently commented out):

Public Sub importTextFile()

On Error GoTo ErrHandler

CurrentDb().Execute "qryAppendFromTextFile", dbFailOnError
'CurrentDb().Execute "qryMakeTblFromTextFile", dbFailOnError

Exit Sub

ErrHandler:

If (Err.Number = 3127) Then ' Use this when appending to existing
tables.
MsgBox "The import file is tab-deliminated. Please ensure" & vbCrLf
& _
"that only comma-separated files are imported.", vbCritical +
vbOKOnly, _
"Import Failed!"
'If (Err.Number = 3061) Then ' Use this when creating new tables.
' MsgBox "The import file may be tab-deliminated. Please ensure" &
vbCrLf & _
"that only comma-separated files are imported.", vbCritical +
vbOKOnly, _
"Import Failed!"
Else
MsgBox "Error in importTextFile( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If

Err.Clear

End Sub

Of course, with the append query, the table tblMyTable must already exist.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I am using a DoCmd.TransferText method to import text files into a table
using an import specification that expects a comma delimited text file. This
works fine when the text file is comma delimited, but when a tab delimited
file is selected, the text is loaded anyway, resulting in garbage data in the
table, and the creation of an ImportErrors table.

Is there a way for vba code to detect if a text file matches the import
secifications prior to attempting an import? At the very least I would like
to know if tab, rather than comma delimiters are used in the file, then I
could specify an alternate import specification, or at least alert the user
that the text file is in the wrong format.

Thanks for any suggestions,

TK