0

All the CSV files are all in the same format. I want each CSV file to be in it's own table, with the table name being the same as the filename.

How can I do this?

DavidPostill
  • 153,128
  • 77
  • 353
  • 394
soandos
  • 24,206
  • 28
  • 102
  • 134

2 Answers2

2

I'm giving back to the community after wasting four hours of my time figuring out a specific issue, but answering your question at the same time. All other code was gathered from other resources. This code will use the transfertext function to pull multiple csv from a directory into their own separate tables with the FILENAME as the table name. Running files with the same filename will let you append to an existing table (handy when you have 30 differently named files, but retain the same name from month to month). Access 2010

Where I had trouble: the darn tables would not append when running the same named file. It would always create a new table and append a 1 2 3 etc. whatever_csv whatever_csv1 whatever_csv2. ***You must strip the period out of the filename. Even though access automatically changes it to an underscore when it creates the table it sees the filename and table are differently named. This applies to any characters not allowed by Access.

Truncate the last four of the filename to remove .csv using strTable = Left(strFile, Len(strFile) - 4)

*****Here is the answer to your question*****

Option Compare Database 
Option Explicit 

Function DoImport() 

 Dim strPathFile As String 
 Dim strFile As String 
 Dim strPath As String 
 Dim strTable As String 
 Dim blnHasFieldNames As Boolean 

 ' Change this next line to True if the first row in CSV worksheet 
 ' has field names 
 blnHasFieldNames = True 

 ' Replace C:\Documents\ with the real path to the folder that 
 ' contains the CSV files 
 strPath = "C:\Documents\" 

 ' Replace tablename with the real name of the table into which 
 ' the data are to be imported 

 strFile = Dir(strPath & "*.csv") 


 Do While Len(strFile) > 0 
       strTable = Left(strFile, Len(strFile) - 4) 
       strPathFile = strPath & strFile 
       DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames 


 ' Uncomment out the next code step if you want to delete the 
 ' EXCEL file after it's been imported 
 '       Kill strPathFile 

       strFile = Dir() 

 Loop 


End Function
physics2010
  • 3
  • 1
physics2010
  • 36
  • 2
1

If your csv files have the same format, usually is better to keep them in a single table. For example, you can have 2 tables one with the name of your files and one with their content.

For importing the contents of multiple csv I usually prefer VBA. There are 3 main options:

  • DoCmdTransferText
  • File System Object
  • I/O open statement

Since all your csv files have the same format, the easiest path is the first.

For using DoCmd TransferText you need to specify an schema. You can create that schema importing manually just one file and when the option of saving the schema appears you just save it. Then, using the Dir() function with the extension "*.csv" you loop through your directory and import all your files.

HTH

Diego
  • 111
  • 1