DoCmd.TransferText acImportDelim, mySpec, myTable, myFile, True, ""

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

I'm importing a text file into a table. The code below works great. However, it is possible to import the wrong text file (ie., a file which should be imported into another table, with different columns etc. Is there anyway to specify to check column heading of the import file with the table or something

You can see I've added an InStr function which checks the file name for the right text as a way around it.. but its not really sufficient

If myFile <> "" The
If InStr(1, myFile, MyText, vbTextCompare) > 0 Then '<the text was found
DoCmd.TransferText acImportDelim, mySpec, myTable, myFile, True, "
MsgBox "File uploaded: " & myFile & Chr(13) & " to Table : '" & myTable & "'.
Else '<it wasn't

MsgBox "File : " & myFile & Chr(13) &
"Not uploaded. Are you sure this is the right file? The file must have the text : '" & MyText & "'."
End I

Where
myFile = file name and directory for uploading into tabl
myTable = table name that myFile is uploaded int
mySpec = is specification name for importation into tabl
myText = additional check, to see if the file has the right name in it (see comments above

Thanks
Marcu
 
marcus. said:
Hi All,

I'm importing a text file into a table. The code below works great.
However, it is possible to import the wrong text file (ie., a file
which should be imported into another table, with different columns
etc. Is there anyway to specify to check column heading of the
import file with the table or something?

You can see I've added an InStr function which checks the file name
for the right text as a way around it.. but its not really
sufficient.

If myFile <> "" Then
If InStr(1, myFile, MyText, vbTextCompare) > 0 Then '<the
text was found> DoCmd.TransferText acImportDelim, mySpec,
myTable, myFile, True, "" MsgBox "File uploaded: " &
myFile & Chr(13) & " to Table : '" & myTable & "'." Else '<it
wasn't>

MsgBox "File : " & myFile & Chr(13) & _
"Not uploaded. Are you sure this is the right file?
The file must have the text : '" & MyText & "'." End If

Where:
myFile = file name and directory for uploading into table
myTable = table name that myFile is uploaded into
mySpec = is specification name for importation into table
myText = additional check, to see if the file has the right name in
it (see comments above)

Thanks,
Marcus
Click to expand...

You could open the text file directly, read a line from it, and decide
based on that line whether it's the right sort of file. For example
(air code):

Dim intFile As Integer
Dim strLine As String

intFile = FreeFile()
Open myFile For Input As #intFile
Line Input #intFile, strLine
Close #intFile

If strLine Like "*Header1,*Header2,*Header3*" Then
DoCmd.TransferText ...
Else
MsgBox "Hey! This isn't the right file!"
End If
 
I like making a linked table pointing to the text file. This way, you
can use DAO/ADO to manipulate it just like a regular text file. I've
found this to be a more efficient way to get data out of text files.
I'm not sure how it's possible to import the wrong file? You're
telling Access the path to find the file. Is the file overwritten
sometimes with different formats? You could create two linked tables,
with different column settings pointing to the same file in that case
I believe.
 
Back
Top