Import multiple text files with an extra identifying column

[knifelong]

Hi.

I have the code below 'Courtesy of Tim K.
This code works well to import multiple text files into one table in Access according to the saved field specification. However what I would also like to be able to do is for it to create an extra column with the name of the text file so that I may be able to see which text file the record came from.

The text files are located at C:\Import TXT files\ and are moved to the archive folder C:\Archived TXT Files\

Any ideas on how to adapt the code ? I'm still very basic with VBA yet. thanks.


Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFolderPath = "C:\Import TXT files\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, "TextImportSpecs", "tblImportedFiles", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "C:\Archived TXT Files\" & objF1.Name 'Move the files to the archive folder
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Sub

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit

End Sub

[CreganTur]

Without seeing any more specific information, the only option I can think of is to run an UPDATE query right after you import the textfile- have it update the desired field with the name of the text file, where that field has a null value. The logic is that only newly imported records will have a null value in that field, so you can safely and correctly update all null valued fields to have the name of the current text file.

You can do this by using the DoCmd.RunSQL method.

HTH:thumb

[knifelong]

Hi. Thanks for your ideas. I have adapted the code below with 2 sql lines which adds a column and updates with the name of the text files in the input folder. Or at least it should as currently only the name of the first text file is updated to the new column. Obviously I want each filename updated to correspond to the name of the data of each file imported. Any ideas how to achieve this.?



Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
ImportFile = Dir("C:\test\" & "\*.txt")
strFolderPath = "C:\test\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportFixed, "Therapy_Import_Specification", "table name", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "C:\test1\" & objF1.Name 'Move the files to the archive folder

End If
Next

DoCmd.RunSQL "ALTER TABLE [table name]ADD pracid text(50)"
DoCmd.RunSQL "UPDATE [table name]Set pracid =" & "'" & ImportFile & "' WHERE pracid Is Null"
ImportFile = Dir
MsgBox "the import file is" & " " & ImportFile

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
bImportFiles_Click_Exit:
Exit Sub
bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

[hansup]

Hi. Thanks for your ideas. I have adapted the code below with 2 sql lines which adds a column and updates with the name of the text files in the input folder. Or at least it should as currently only the name of the first text file is updated to the new column. Obviously I want each filename updated to correspond to the name of the data of each file imported. Any ideas how to achieve this.? I don't see why you should need to run an "ALTER TABLE" statement for each TXT file you import. Add the required field to your table structure, and leave it there. Then revise your import spec to designate which fields will receive the imported data. That approach works for me (with acImportDelimited; I haven't tried acImortFixed).

You're using two methods to retreive the names of the TXT files you import: one based on Scripting.FileSystemObject; and the Dir() function. You don't need both. And store the TXT file name in your destination table within the same loop where you do the TransferText operation.
Private Sub bImportFiles_Click()
Const strSourceFolder As String = "D:\txt_data\source\"
Const strArchiveFolder As String = "D:\txt_data\archive\"
Const strDestinationTable As String = "tblFromImportedText"
Const strImportSpec As String = "My_Import_Spec"
Const strFileNameField As String = "source_file"
Dim strFileName As String
Dim strSQL As String

strFileName = Dir(strSourceFolder & "*.txt")

Do While Len(strFileName) > 0
'Debug.Print strFileName
MsgBox "the import file is " & strFileName
DoCmd.TransferText acImportDelim, strImportSpec, _
strDestinationTable, strSourceFolder & strFileName, False
strSQL = "UPDATE " & strDestinationTable & " SET " _
& strFileNameField & " = '" & strFileName & "' WHERE " _
& strFileNameField & " Is Null;"
'Debug.Print strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Name strSourceFolder & strFileName As strArchiveFolder & strFileName
strFileName = Dir
Loop

bImportFiles_Click_Exit:
Exit Sub
bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub