Import multiple csv and add filename in additional field

[gservin]

Dear All,

I spent the whole morning searching an answer to my problem but I must admit
reaching my very limited capabilities in Access.

In short I need to import more than 100 .csv files (with the same structure
and in the same folder) to 1 table. At the same time, in order to
differentiate where the records are coming from (each csv has 1000) I would
like to add, for each .csv, its unique filename (or even better the last 8
characters) in an additional new field called "CSV_DATE".

I've found some code which works great for the import but I can't get the
filename part right.

QUOTE
Sub Import_multiple_csv_files()
'Modified from WillR - www.willr.info (December 2004)

Const strPath As String = "O:\ICT\CSV_import\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

' Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, "CSV_Import_Specs2", _
"CSV_Import_ALL2", strPath & strFileList(intFile)
'Check out the TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & "Files were Imported"
End Sub
UNQUOTE

Any help will save me hours and is greatly appreciated.
Cheers from Brussels,

GéraudS

 

[Rita LnRRigB]

I have a large data base with several tables, quires and reports.

In Access 2003 I was able to create a new database and keep some of my tables.
Example, I was able to save data and format for Dogs and Owners. Then I was
able to save format for events and entries.

But, now I can't seem to figure out how to do that with 2007.

Any help would be appreciated.

 

[pietlinden]

Dear All,

I spent the whole morning searching an answer to my problem but I must admit
reaching my very limited capabilities in Access.

In short I need to import more than 100 .csv files (with the same structure
and in the same folder) to 1 table. At the same time, in order to
differentiate where the records are coming from (each csv has 1000) I would
like to add, for each .csv, its unique filename (or even better the last 8
characters) in an additional new field called "CSV_DATE".

I've found some code which works great for the import but I can't get the
filename part right.

QUOTE
Sub Import_multiple_csv_files()
     'Modified from WillR -www.willr.info(December 2004)

    Const strPath As String = "O:\ICT\CSV_import\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File   Array
    Dim intFile As Integer 'File Number

     ' Loop through the folder & build file list
    strFile = Dir(strPath & "*.csv")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
         MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list of files & import to Access
     'creating a new table called MyTable
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acImportDelimi, "CSV_Import_Specs2", _
        "CSV_Import_ALL2", strPath & strFileList(intFile)
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of
         'optional settings
    Next
    MsgBox UBound(strFileList) & "Files were Imported"
End Sub
UNQUOTE

Any help will save me hours and is greatly appreciated.
Cheers from Brussels,

GéraudS

I am assuming that only the current file's records would have a
blank If that's the case, you could use an update query inside your
code after the TransferText command.

DBEngine(0)(0).Execute "UPDATE CSV_Import_All2 SET FileName = ' " &
strFile & " ' "

remove the excess spaces around the single quotes... they're there for
clarity...

 

[gservin]

Sorry I can't make it work. Shouldn't the fieldname "CSV_DATE" be somewhere;
I was not clear enough but I am trying for all records of each .csv
processed to update the field "CSV_DATE" with the .csv filename.

GeraudS

 

[gservin]

You're assumption is right. The field "CSV_DATE" in the table
"CSV_Import_All2" I want to update with the .csv filename is blank.
Unfortunately I can't make it work...

QUOTE
DoCmd.TransferText acImportDelimi, "Copy of fordsara specs", _
"CSV_Import_All2", strPath & strFileList(intFile)
'DBEngine(0)(0).Execute "UPDATE CSV_Import_All2 SET FileName ='" &
strFile & "'"
UNQUOTE

Géraud