Import CSV tables

[David]

Hi,

I want to import csv tables (not unlike a recent query) however while the
files will be in a known folder- their name is not known (variable)

i want to import all the files into one temp Access table- and would like a
column in the table to be populated by the name of the file from which it
came.

Any help would be really appreciated

 

[Ralph]

Sub ImportTmpCsvFiles()
Dim FilePath As String
Dim FileSpec As String
Dim Match As String

FilePath = "C:\Temp\"
FileSpec = FilePath & "*.csv"

Match = Dir(FileSpec)
'Make sure CSV file exists
If Len(Match) = 0 Then
MsgBox "No CSV Files Found.", vbInformation, Title
Exit Sub
End If

DoCmd.TransferText acImportDelim, "YourSpecNameHere", "Table1", FilePath &
Match, -1
DoCmd.RunSQL "UPDATE Table1 SET Table1.FileName = """ & Match & """ WHERE
Table1.FileName Is Null"
End Sub

 

[David]

Ralph,
Thanks for that- I copyed the code into the Vb editor- then set up a folder
c\temp
Ran macro expecting it firstly to tell me that it couldn't find a file- but
just got an "Action Failed" pop up about 20 times- even when I put in a csv
file it wouldn't work i'm using access03 would that be an issue?

 

[Ralph]

To run it from a macro you would need to change it to a function. Did you do
that? Do you have a Table1 with a field named Filename in it? Do you have a
file spec for importing the csv file? Try copying the following into a
module. Make sure you have a table that matches the csv file and a field to
update to the csv file name.

Function ImportTmpCsvFiles()
Dim FilePath As String
Dim FileSpec As String
Dim Match As String

FilePath = "C:\Temp\"
FileSpec = FilePath & "*.csv"

Match = Dir(FileSpec)
'Make sure CSV file exists
If Len(Match) = 0 Then
MsgBox "No CSV Files Found.", vbInformation, Title
Exit Sub
End If

'forgot the loop here
Do until Len(Match)=0
DoCmd.TransferText acImportDelim, "YourSpecNameHere", "Table1", FilePath &
Match, -1
DoCmd.RunSQL "UPDATE Table1 SET Table1.FileName = """ & Match & """ WHERE
Table1.FileName Is Null"
Match =Dir
Loop
End Function

 

[David]

Ralp
I posted that script into a module- used a macro to Open module (presume
that means run it?- Set up filename field and import spec- but can't get it
to work- could I send you a simple access & csv file to check what I did
wrong?

 

[macrojunkie]

Sub ImportTmpCsvFiles()
Dim FilePath As String
Dim FileSpec As String
Dim Match As String

 FilePath = "C:\Temp\"
 FileSpec = FilePath & "*.csv"

 Match = Dir(FileSpec)
 'Make sureCSVfileexists
 If Len(Match) = 0 Then
   MsgBox "NoCSVFilesFound.", vbInformation, Title
   Exit Sub
 End If

DoCmd.TransferText acImportDelim, "YourSpecNameHere", "Table1", FilePath &
Match, -1
DoCmd.RunSQL "UPDATE Table1 SET Table1.FileName =  """ & Match & """ WHERE
Table1.FileName Is Null"
End Sub

Hi, I found this post and tried it out, because I need this same exact
thing. When I put it together, I can actually get this to run (I
attached it to a command button on a form)
--------------------------------------------------------------------------------------
Private Sub Command0_Click()
Dim FilePath As String
Dim FileSpec As String
Dim Match As String

FilePath = "C:\test\"
FileSpec = FilePath & "*.csv"


Match = Dir(FileSpec)
'Make sure CSV file exists
If Len(Match) = 0 Then
MsgBox "No CSV Files Found.", vbInformation, Title
Exit Sub
End If

DoCmd.TransferText acImportDelim, "myspecs", "tblCSV", FilePath &
Match, -1
DoCmd.RunSQL "UPDATE tblCSV SET tblCSV.FileName = """ & Match & """
WHERE tblCSV.FileName Is Null"
Match = Dir


End Sub

-------------------------------

The thing is that it only goes through the first csv it hits and asks
me if I want to update 1 record. That is fine since that csv only has
1 record, but why didn't it do the rest of the csv's in the folder? I
tried the loop you had in the version you changed to a function but
that wouldn't compile. any suggestions? This is a great sub you put
together.

Thank you.