Question VBA DoCmd.TransferText add file name to field in table

[palmer.rp]

Hi,
Thanks for any assistance in advance. I have VB code using the DoCmd.TransferText which imports multiple files and loads the data into a table. I would like to add part of the file name as a value in a given field in the table.
The table name is NS_Import2
The field name is NS_RouteFileName
The file names are e.g. A_190304.txt, B_190304.txt, C_190304.txt, etc...
I want to put A_190304, B_190304, and C_190304 as a value in the NS_RouteFileName field in the NS_Import2 table.
Here is my code:

Private Sub btn_import_CSV_DblClick(Cancel As Integer)
DoCmd.SetWarnings (False)
On Error GoTo Err_FTP
Dim filepath As String
Dim strFile As String
Dim strPathFile As String
Dim user As String
user = Environ("username")

'################################################################################################
'Drop error tables
For Each tbl In CurrentDb.TableDefs
If InStr(tbl.Name, "Errors") > 0 Then
s = "DROP TABLE [" & tbl.Name & "]"
Debug.Print s
CurrentDb.Execute s
End If
Next
'################################################################################################

If user = "administrator" Then
filepath = "C:\Users" & user & "\OneDrive - Ryder\Desktop\OMS2\OMS\NorthStar\Import"
Else
filepath = "C:\Users" & user & "\OneDrive - Ryder\Desktop\OMS\NorthStar\Import"
End If

DoCmd.RunSQL "Delete * from NS_Import2"

strFile = Dir(filepath & "*.txt")
Do While Len(strFile) > 0
strPathFile = filepath & strFile

DoCmd.TransferText acImportDelim, "NS_DataImport", "NS_Import2", strPathFile, False
 
' Kill strPathFile
strFile = Dir()
Loop
MsgBox ("Data Imported!")

DoCmd.SetWarnings (True)
HandleExit:
Exit Sub

Err_FTP:
MsgBox Err.Description
Resume HandleExit
DoCmd.SetWarnings (True)
End Sub

 

[isladogs]

Suggest you either
a) use an update query to insert the file name based on strFile after your code has run
b) scrap the TransferText code, link to the text file and use an append query to insert the data including a field based on strFile as above

Hope that helps

 

[Deleted member 147267]

I see no code there for updating the table after the import. Execute an update query or sql in code, or open a recordset on the table and update that. You seem to be missing a slash between the user variable and folder Users:
filepath = "C:\Users" & user & "\OneDrive

Please use code tags (# on forum toolbar) with indentation to make code easier to read.

 

[palmer.rp]

What are the text files like? are they Comma Separated Value files (CSV)? If not, what separates the text into the different Fields? Can you supply a sample of the the data from the text files?

Hi Gizmo,
See attached. Tab separated.

 

[palmer.rp]

Suggest you either
a) use an update query to insert the file name based on strFile after your code has run
b) scrap the TransferText code, link to the text file and use an append query to insert the data including a field based on strFile as above

Hope that helps

It does make sense Isladogs, my problem is I do not know how to.

a) Something like this? or this?

DoCmd.RunSQL "UPDATE NS_Import2 "SET NS_Import2.NS_RouteFileName'" & strFile & "' WHERE NS_Import2.NS_RouteFileName'IS NULL;"

or

Set rs = CurrentDb.??("NS_Import2")
rs.AddNew
rs.Fields("NS_RouteFileName").Value = strFile
rs.Update
rs.Close
Set rs = Nothing

b) if you have a small insert/append example to capture the strFile as a field value. Please provide.
Thanks

 

[isladogs]

For the update query, use

UPDATE NS_Import2 SET NS_Import2.NS_RouteFileName = '" & strFile & "' WHERE NS_Import2.NS_RouteFileName IS NULL;

However I would just use an append query so the file name is added when the data is imported from the link table. For example

INSERT INTO NS_Import2 ( NS_RouteFileName ) SELECT B_190304.*, '" & strFile & "' AS NS_RouteFileName FROM B_190304;

Or assuming the link table has the same name as the strFile variable, we can generalise this as

INSERT INTO NS_Import2 ( NS_RouteFileName )
 SELECT " & strFile & ".*, '" & strFile & "' AS NS_RouteFileName
 FROM " & strFile & ";

NOTE: Running a debug.print on that sql for your supplied file will give

INSERT INTO NS_Import2 ( NS_RouteFileName ) SELECT B_190304.*, 'B_190304' AS NS_RouteFileName FROM B_190304;

 

[arnelgp]

you should also add backslash to this area:

If user = "administrator" Then
filepath = "C:\Users[COLOR="Blue"]\[/COLOR]" & user & "\OneDrive - Ryder\Desktop\OMS2\OMS\NorthStar\Import[COLOR="Blue"]\[/COLOR]"
Else
filepath = "C:\Users[COLOR="blue"]\[/COLOR]" & user & "\OneDrive - Ryder\Desktop\OMS\NorthStar\Import[COLOR="blue"]\[/COLOR]"
End If