Import CSV tables

  • Thread starter Thread starter David
  • Start date Start date
D

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
 
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
 
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?
 
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
 
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?
 
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
Click to expand...


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.
 
Back
Top