Importing text files..to include datafile name as a field..

[arsenal042004]

I currently use this small bit of code to import text files into a table as batch. I'd like to know if there is a way to also import in a field, the file name. Since I have many files and I need to know where certain records come from.

Thanks in advance for your assistance!
Todd
Private Sub Command0_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

' ChDir ("D:\New")
strfile = Dir("D:\CNew\*.csv")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "EVAL", "EVAL", "D:\CNew\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "D:\CNew\" & strfile
strfile = Dir
Loop

End Sub

 

[sxschech]

What if instead of importing the data, you have the code link it. If you did that, then you can create an append query. After that, add a calculated field into the query which would then include the file name variable which would be saved into the table. If you need a code example let me know.

 

[HaroldGE]

Since you are importing into an existing table,
add one more field at the end for file name (FileName).
The import should leave the field null.
Then RunSQL that will put the file name in the table where it's null.

DoCmd.TransferText acImportDelim,"EVAL","EVAL","D:\CNew\"& strfile,True
[b]Docmd.RunSQL "UPDATE EVAL SET FileName = " & strfile & _
             " WHERE FileName Is Null;"[/b]
Kill "D:\CNew\" & strfile

good luck
HGE