Importing data from multiple csv files

[mrkipling]

I need to import data from multiple csv files into an access table in my DB. These csv files are being constantly uploaded to a folder on an ftp server, then forwarded to a folder that resides on the Database PC. They need to be added to the database as they arrive. They will have various names. Now the linked table feature in access will only allow the table to retrieve data from a named data source. Can anyone advise me as to the best way of going about this

TIA

Mark

 

[tunsarod]

I created a solution in Access97 that import data from comma delimited files using this process:

1. identify the csv filename and path
2. copy the data from the file into the target table

First you must establish an import profile specific to your CSV file format by manually going through the process of importing a csv file and naming and saving the resulting profile. This can then be used with the Docmd.transferText method to bring the data into the table. By using this profile with the Docmd.TransferText method you can copy the data from any csv with the same format.

here's the code that transfers the text:



Docmd.Transfertext acImportDelim,"nameofprofile","nameoftable","filepathandname"


Here's the code I use to gather the file details for the combo-box:


Function ManifestList(fld As Control, ID As Variant, row As Variant, _
col As Variant, Code As Variant) As Variant
Dim stRCriteria As String, strFile As String, strPath As String, strExtn As String, intFiles As Integer


Select Case Code
Case acLBInitialize ' Initialize.
ManifestList = True
Case acLBOpen ' Open.
ManifestList = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ManifestList = -1
Case acLBGetColumnCount ' Get columns.
ManifestList = 2
Case acLBGetColumnWidth ' Get column width.
ManifestList = -1 ' Use default width.
Case acLBGetValue ' Get the data.

intFiles = 0

strPath = "C:\CSV IMPORTS\"
strExtn = "*.csv"
stRCriteria = strPath & strExtn

strFile = Dir(stRCriteria)

Do While Len(strFile) > 0

If intFiles = row Then
Debug.Print col
If col = 0 Then
ManifestList = strFile
ElseIf col = 1 Then
ManifestList = FileDateTime(strPath & strFile)
End If
Exit Do
End If

intFiles = intFiles + 1

strFile = Dir

Loop

End Select

End Function


Change the function name ManifestList to whatever you prefer. Change the Path to the path on your C: drive where the files are parked and place the function name in the RowSourceType of the Combo box. Set the combo to a column count of 2 bound to column 1.

If your don't need the date/time info for your csv files then modify the function accordingly and set the combo to 1 column.


Function ManifestList(fld As Control, ID As Variant, row As Variant, _
col As Variant, Code As Variant) As Variant
Dim stRCriteria As String, strFile As String, strPath As String, strExtn As String, intFiles As Integer


Select Case Code
Case acLBInitialize ' Initialize.
ManifestList = True
Case acLBOpen ' Open.
ManifestList = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ManifestList = -1
Case acLBGetColumnCount ' Get columns.
ManifestList = 1
Case acLBGetColumnWidth ' Get column width.
ManifestList = -1 ' Use default width.
Case acLBGetValue ' Get the data.

intFiles = 0

strPath = "C:\CSV IMPORTS\"
strExtn = "*.csv"
stRCriteria = strPath & strExtn

strFile = Dir(stRCriteria)

Do While Len(strFile) > 0

If intFiles = row Then
ManifestList = strFile
Exit Do
End If

intFiles = intFiles + 1

strFile = Dir

Loop

End Select

End Function

 

[mrkipling]

Thanks alot tunsarod , just what i was looking for.

thanks

Mark