Question VBA DoCmd.TransferText add file name to field in table (1 Viewer)

[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

 

[Micron]

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.

 

[Uncle Gizmo]

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?

 

[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

 

[Uncle Gizmo]

I've been working on a system for importing spreadsheets with more than 255 columns into several MS Access tables. I've discussed it with colleagues on this site a while back, I think it was February/March this year...

I realised it would be relatively simple to alter my import routine so that it added the filename as one of the columns. However the sample data you sent me contains some NULL values. These NULLS are interfering with the custom VBA import routine I have developed...

I have searched the internet to try and discover how to remove the NULL values, but I don't seem to be able to. It appears that I will have to open the text file in binary and replace the NULL values with zero length strings and then import it...

So the options are:-
Can provide me with text files without NULL values in them?
Alternatively someone may know how I can remove the NULL with the split function...

The offending line of code is:-

            With rst
                .AddNew
                    For I = 0 To UBound(varSplit) 'Get all the Field Names
                        If I >= intStartFld And I <= (intEndFld - 1) Then 'If there are more that 255 Flds then restrict the number of flds created
                            .Fields(((I + 1) - intStartFld)) = varSplit(I)
                        End If
                    Next I
               .Update
           End With

If varSplit(I) is "NULL" then I get two Errors...

---------------------------
Import CSV Files ERROR 1
---------------------------
Case Else Error
Error From:-
Form - Form1 Subroutine >>>>>>> fImportText

Error Number >>>>> -2147217887

Error Description:- Field 'tblTempImport_01.Height' cannot be a zero-length string.

---------------------------
Import CSV Files ERROR 2
---------------------------
Case Else Error
Error From:-
Form - Form1 Subroutine >>>>>>> fImportText

Error Number >>>>> 3219

Error Description:- Operation is not allowed in this context.

 

[Uncle Gizmo]

I haven't fixed it yet, but I think I know how to. I was looking the wrong place, I thought I needed to fix it at the Variant "varSplit"...

However, before it gets there, it's in a string variable! So that's what I need to do, replace the NULL with a zero length string in the string variable. I'm going to try that!

           'Creates a single dimension array using the split function
           varSplit = Split(strInput, vbTab, , vbBinaryCompare)

Before the split function is passed into the Variant varSplit, its a simple string "String Input" "strInput", that's where I'm going to to attempt to take Out the NULL(s)...

 

[Uncle Gizmo]

I'm slowly getting to the bottom of this, but it is very frustrating, like it was 6 months ago, and I wish I'd never got back into it! I think one problem is the sample data has a vendor column which has empty cells. They are reported as being NULL, but that's when the code tries to put the value in the field. But they are not actually Null, they contain a control line feed. That's why I was having problems getting rid of the NULL because there wasn't one!

I removed the vendor column entirely, and recreated the text file with everything in text format and with everything in text delimiters. Tried the import and it still failed!

The second problem is responsible for this failure. The problem is with my import routine, and I know what it is, (I think). In March 2019 I was importing Comma Separated Value (CSV) Files with over 1000 columns created with a Google spreadsheet. The sheet has 106 rows and 1122 columns - named:- CSV Large Sample Data Set_MASTER... I wasn't particularly bothered about the accuracy of the individual tables it created.

The routine creates around 20 tables with 50 columns in each, I wasn't too bothered if each table had the right number of columns in, and I think occasionally it did miss out the odd column. The main task was to get the program to extract the data from the Comma Separated Value (CSV) File, create the tables, copy the data into those Tables. The minor details could be sorted out later, and of course I haven't done that, so I'm having problems importing to a single table.