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. Is it possible to automate this so that access will retrieve the data from the csv file when a new one arrives in the folder ?

TIA

Mark

 

[FoFa]

I would setup a form with a timer to check the directory every so often. I would (I am speaking from a Access2002 standpoint) use the file system object to scan the directory, do a transfertext on any file there, than move the file to a processed/backup directory when complete. Or I would create a VB script to scan the directory, and if there are file call Access which will process all the files and quit. Either is doable. We have used the second version here when they (network folks) did not Access running all the time on the server, we scheduled the VB script via AT on the NT server.

 

[mrkipling]

Thanks alot for your suggestions Fofa, I am going to try and do it the way first way you suggested, But I am unsure of how you use file system object . Is it possible for you to give me an exampe of how this works.

Thanks again

 

[FoFa]

I found this script I had for searching a file tree for a file done in MSAccess (I think A95):
Private Declare Function apiSearchTreeForFile Lib "ImageHlp.dll" Alias _
"SearchTreeForFile" (ByVal lpRoot As String, ByVal lpInPath _
As String, ByVal lpOutPath As String) As Long

Function fSearchFile(ByVal strFilename As String, _
ByVal strSearchPath As String) As String
'Returns the first match found
Dim lpBuffer As String
Dim lngResult As Long
fSearchFile = ""
lpBuffer = String$(1024, 0)
lngResult = apiSearchTreeForFile(strSearchPath, strFilename, lpBuffer)
If lngResult <> 0 Then
If InStr(lpBuffer, vbNullChar) > 0 Then
fSearchFile = Left$(lpBuffer, InStr(lpBuffer, vbNullChar) - 1)
End If
End If
End Function
And the attached zip file is a vbs (VB Script) file that shows how to use filesystem object (I don't have something in Access right now).
Hope one of them helps

 

[IMO]

mrkipling,
You can use the following code to import all .csv files from a Dir into a single table, or into separate tables. It will then (after importing) move the files to a specified "History" Dir.



Private Sub Command0_Click()

Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String
InputDir = "c:\YOUR DIR WHERE THE FILES LAND\"
ImportFile = Dir(InputDir & "\*.csv")
Do While Len(ImportFile) > 0
'tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1)) 'Use this to import each file into separate tables.
tblName = "YOUR TABLE NAME TO IMPORT INTO" 'I use this to import all my files into one table.
DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True
ImportFile = Dir
Loop
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
'If fs.FileExists("c:\YOUR DIR WHERE THE FILES LAND\*.csv") Then
fs.MoveFile "c:\YOUR DIR WHERE THE FILES LAND\*.csv", "c:\YOUR DIR WHERE THE FILES CAN BE STORED AS HISTORY\"

End Sub

Regards
IMO

 

[mrkipling]

Thanks Aaot for the help guys,

Imo, I am using your script, which works really well, except that i have it triggered by a form timer, now when there is no .csv file in the folder to process , the script throws up an error , do you know how to work round this ?

thanks again



Mark

 

[IMO]

Hi Mark,

Try this :

Create a listbox on your form and call it List20, set visible to 'No' and paste the following code in the ontimer event (not forgetting to change the Dir names as before)

Private Sub Form_Timer()

Dim strFile As String, strRowSource As String
strFile = Dir("C:\IMPORTCSV\")
strRowSource = strRowSource & strFile
Do Until strFile = ""
strFile = Dir
strRowSource = strRowSource & strFile & ";"
Loop
If strRowSource <> "" Then
Me.List20.RowSource = Left(strRowSource, Len(strRowSource) - 1)
Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String
InputDir = "c:\IMPORTCSV\"
ImportFile = Dir(InputDir & "\*.csv")
Do While Len(ImportFile) > 0
tblName = "History"
DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True
ImportFile = Dir
Loop
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
fs.MoveFile "c:\IMPORTCSV\*.csv", "c:\HISTORYCSV\"

End If

Let me know how you get on

IMO
End Sub