Import .txt files with file name

[afaust]

Hi,
I have searched on here and gotten most of the way with this problem.

I want to import about 500 text files into one table but also add the file name as a field while importing.

So far I have figured out that I should use a temporary table and then copy the data from there into my permanent table with the file name being selected during appending.

Here is the code that I have so far. I try to run it and it says that the sub or function is not defined in my select statement at "from".

Any help with this would be much appreciated. I have already used this forum to get this close...

Thanks,
Abby

Option Compare Database

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 = "G:\Processing\Fish"
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, "FISH", "Temporary_Table", strFolderPath & objF1.Name, True
End If

txtFileName = objF1.Name
CurrentDb.Execute "SELECT txtFileName AS FISH_ECHO.[FileName], Temporary_Table.Type, Temporary_Table.Ping_Num, Temporary_Table.Depth, Temporary_Table.ESn, Temporary_Table.ESw, Temporary_Table.TS, Temporary_Table.Bn, Temporary_Table.Along, Temporary_Table.Athwart, Temporary_Table.SD_Along, Temporary_Table.SD_Athwart, Temporary_Table.Corr, Temporary_Table.Width, Temporary_Table.Bot, Temporary_Table.Latitude, Temporary_Table.Longitude, Temporary_Table.Time_and_Date)"

FROM Temporary_Table

Insert Into, FISH_ECHO([FileName], [Type], [Ping_Num], [Depth], [ESn], [ESw], [TS], [Bn], [Along], [Athwart], [SD_Along], [SD_Athwart], [Corr], [Width], [Bot], [Latitude], [Longitude], [Time_and_Date])
CurrentDb.Execute "DELETE * FROM Temporary_Table;"

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

End Sub

 

[afaust]

I realize that I have my INSERT INTO and SELECT in the wrong order. When i move them to the right place, i still get a compile error...I really can't see what is wrong with this select statement...

Thanks in advance for any help!

 

[Rkay]

Try removing the second end sub at the buttom

 

[afaust]

Thanks, I caught that after posting...I am still getting a syntax error in the select statement...im not sure why but i am going through it again and trying to get it worked out...thanks again!

 

[afaust]

Can anyone help me with this? I keep getting a compile error syntax error with the select part of the statement...I dont know why....anyone??? please help...

Option Compare Database
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:\Users\abby\Desktop\Metadata to Move to other computer\Acousitc"
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, "FISH", "Temporary_Table", objF1.Name, True
End If
txtFileName = objF1.Name
CurrentDb.Execute "INSERT INTO FISH_ECHO([FileName], [Type], [Ping_Num], [Depth], [ESn], [ESw], [TS], [Bn],"
& "[Along], [Athwart], [SD_Along], [SD_Athwart], [Corr], [Width], [Bot], [Latitude], [Longitude], [Time_and_Date])"
& "SELECT txtFileName AS [FileName], [Type], [Ping_Num], [Depth], [ESn],"
& "[ESw], [TS], [Bn], [Along], [Athwart],"
& "[SD_Along], [SD_Athwart], [Corr], [Width],"
& "[Bot], [Latitude], [Longitude], [Time_and_Date] &"
& "FROM Temporary_Table;"
CurrentDb.Execute "DELETE * FROM Temporary_Table;"
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

 

[highandwild]

Give this a go:

strSQL = "INSERT INTO FISH_ECHO([FileName], [Type], [Ping_Num], [Depth], [ESn], [ESw], [TS], [Bn]," & _
" [Along], [Athwart], [SD_Along], [SD_Athwart], [Corr], [Width], [Bot], [Latitude], [Longitude], [Time_and_Date])" & _
" SELECT " & Chr(34) & txtFileName & Chr(34) & " AS [FileName], [Type], [Ping_Num], [Depth], [ESn]," & _
" [ESw], [TS], [Bn], [Along], [Athwart]," & _
" [SD_Along], [SD_Athwart], [Corr], [Width]," & _
" [Bot], [Latitude], [Longitude], [Time_and_Date]" & _
" FROM Temporary_Table"

There are a number of changes here but I cannot test it without the tables.

 

[highandwild]

Whoops!!

and

DoCmd.RunSQL strSQL

after it.

Don't forget to declare the strSQL variable as a string.