G
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
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