Add File Name to Table when Importing Files

[MentallyDestructive]

Hello. I have a database which allows a user to import multiple .csv files at one time and adds them to a table. The file name format is based on the date and time including seconds which eliminates the possibility of duplicates since one person creates these throughout the day. Each file is considered a "batch" and can contain anywhere from 1 to 50 (or more) line items. If I can capture the file name and import that into the table that will allow me to show how many "batches" have been submitted.

Is there code that is compatible with what I am currently doing that will also import the filename into the table?

Operating System: Windows XP Professional
Program: Microsoft Access 2003
Example of .csv File Name: AROPS20111104145057.csv (AROPSYYYYMMDDHHMMSS.csv)

Here is the code I am currently using and it works wonderfully:

Function ImportARData()
Dim fDialog As Office.FileDialog
Dim varFile As Variant

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = True

.Title = "Choose Files to Import"

.Filters.Clear
.Filters.Add "Text Files", "*.csv"

If .Show = True Then
For Each varFile In .SelectedItems

DoCmd.TransferText acImportDelim, "AROPSImport", "tbl_AROPSImport", varFile
Next
Else
MsgBox "You have cancelled the import."
End If
End With
End Function

Any help you can provide would be greatly appreciated. Thank you.

 

[Mr. B]

Here is a modified version of your code:

Function ImportARData()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim rs As DAO.Recordset

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    .AllowMultiSelect = True

    .Title = "Choose Files to Import"

    .Filters.Clear
    .Filters.Add "Text Files", "*.csv"

If .Show = True Then
    For Each varFile In .SelectedItems

        DoCmd.TransferText acImportDelim, "AROPSImport", "tbl_AROPSImport", varFile
        'add the File Name to your table
        Set rs = CurrentDb.OpenRecordset("TableName")
        rs.AddNew
        rs.Fields("FieldName").Value = varFile
        rs.Update
        rs.Close
        Set rs = Nothing
    Next
Else
    MsgBox "You have cancelled the import."
End If
End With
End Function

In the code above, change "TableName" to the actual name of the table where the file names are to be saved and "FieldName" to the name of the field where the file name is to be saved.

This code will add the name of the file that was just imported to the table you specify.

 

[MentallyDestructive]

Mr. B,

Thank you responding so quickly. I tried your updated code and it ran without error but when I pulled up the table itself only one line item had the filename:

-The file that had the filename was blank except for the "Batch Number" field and then my 4 test records were in the line items below it with the "Batch Number" still blank.
-The full path was displayed C:\Documents and Settings\user\Desktop\AROPS Batch Files\AROPS20111104144847.csv. Which is fine if that is how it comes in because it is easy to get just the number by trimming etc.

Do you have any suggested adjustments? I am thrilled that I am at least one step closer though. I really appreciate that.

 

[Mr. B]

Ok, I think I misunderstood what you were wanting to do. I was thinking that you had a separate table where you just wanted to write the file name just to track the files that had been imported. I did not realize that you wanted to write the file name in ever imported record.

From what you are saying now it would seem that you want to have the file name (or full path) written into the same table where the records from the table are being written.

If this is the case, then you can create an update type sql statement that would update all record in your table with the filename where that field is null. You can then execute when that sql statement in you code where I have the code that opens the record set.

Function ImportARData()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim strsql As String

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    .AllowMultiSelect = True

    .Title = "Choose Files to Import"

    .Filters.Clear
    .Filters.Add "Text Files", "*.csv"

If .Show = True Then
    For Each varFile In .SelectedItems
        'here you can extract just the filename from the complete path if you wish
        'try using InStrRev function along with other text functions
        'like left, right, mid, etc.
        DoCmd.TransferText acImportDelim, "AROPSImport", "tbl_AROPSImport", varFile
        'create the update type sql statement
        strsql = "UPDATE TableName SET TableName.FieldName = '" & varFile & "' " _
               & "WHERE (((TableName.FieldName) Is Null));"
        'execute the action queryt to update the File Name in all imported records
        CurrentDb.Execute strsql
    Next
Else
    MsgBox "You have cancelled the import."
End If
End With
End Function

\

Again change the "TableName" and "FieldName" values to the actual names of the objects.

 

[MentallyDestructive]

Mr. B,

You are the best. It works great. Thank you so much for your help.

 

[Mr. B]

You are quiet welcome. Glad to help.

Good luck with your project.

 

[nstratton]

I apologize for reviving this old post however this is a very quick question related directly to this post.

If I currently have records in the database without the information for the filename then bring in new data, will the code above add the filename to those records as well?

I am adding this in as a "phase 2" like step because I just found it and figured it might be useful. I wouldn't want data already in database to be skewed because they all have the same information. I assumed that is what the WHERE part of the code does, without regard to new or old data

 

[jdraw]

It isn't clear what you mean by this:
If I currently have records in the database without the information for the filename then bring in new data, will the code above add the filename to those records as well?


The technique to record the file name with the imported records is:

-For the imported record, provide a field for each field in the imported record and an extra field to record the filename. The file name is Null/empty when you import the data records.
-get the filename and put it in a variable (author used a filedialog, but key is to isolate the filename for your use)
-now-the file name is Null/empty for the records you just imported, so you update the filename field in the imported records with the variable(filename) you just identified. Those would be the only records where the filename field is Null/empty.
- this technique requires that you consistently import the data, then immediately update/populate the filename field in the data record. Then move to the next import
file.
If you do not have a field reserved in the imported data table for the filename, then you will have to create same before using the technique.

The technique works because the is only one set of imported records where the filename is Null/blank.

Caution: If you have set up your imported table and allowed a Null/empty field for the table name, and you run another import without updating the filename field, you can not simply run the file name update now --because there would be null/empty filenames for those records from 2 separate imports--You can not identify which records were from which import file (unless you have some other identifier/means to do so).


Another, similar approach is to modify the import record before importing. Append a field to the import data that contains the file name. Then, when you import, the filename has already been included in the import process.

 

[kimarwan]

Dear Experts, can I get a sample access 2003 file of it.

 

[Gasman]

Dear Experts, can I get a sample access 2003 file of it.

Dear Experts, can I get a sample access 2003 file of it.

What is wrong with either of the codes posted?
One keeps track in a seperate table, the other in the same table?
My preference would be for a dedicated table.