Importing multiple csv files

[Arain]

I have 3000 csv files that i need to import into the data base as different
tabel. all the fields have to be text in the tabels and the tables name be in
capital. is there a way to do it faster than what i am doing right now by
importing one file at a time.

thanks

 

[Douglas J. Steele]

You can loop through all of the files in a folder using the Dir function.
That will give you the name of each of the files, and you can then use
TransferText to import each table.

Dim strFolder As String
Dim strFile As String
Dim strTable As String

strFolder = "C:\Some Folder\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
' Strip the .csv from the end of the file name
strTable = UCase(Left$(strFile, Len(strFile) - 4)
DoCmd.TransferText acImportDelim,, strTable, _
strFolder & strFile, True
strFile = Dir()
Loop

The "True" at the end of the TransferText statement assumes that each file
has the field names as the first row.

Note, though, that this is an EXTREMELY unusual thing to have to do. I can't
imagine any legitimate case where you'd be creating 3000 different tables in
an application in this way. What are the 3000 tables? Should they actually
be in a single table, with an additional field in the table representing the
source?

 

[Arain]

I am using the "|" as the delim how can i add this to this function and to
answer i dont know why my boss wants all the csv files in different tabel.
also all the fields in the csv file are to be imported as text.

Thanks

 

[Douglas J. Steele]

If you've using | as a delimiter, it's not a csv file! csv stands for
"comma-separated values".

Is each table the same format? If so, you can create a specification (by
manually importing one of the files and going through the Import Text
wizard).

 

[Arain]

Well the extension of the file ends with a csv and what you did with it just
imported the names of the files in the table but failed to import the data.
If i am doing it in access i can chose delim in the other field as "|" to get
the data in the right format in the table. my question is if its not comma
as delim can we chose
"|" ? also they are not in the same format and cant get that done.

 

[Douglas J. Steele]

You didn't answer the question.

Is each table the same? If so, you can create a specification and use that
specification in the TransferText statement.

If not, I believe you can go into the registry and change the Format key
under HKLM\Software\Microsoft\Jet\4.0\Engines\Text from the default
CSVDelimited to Delimited(|). Note, though, that I haven't actually tested
this.

 

[dbsavoy]

Your answer helps me as well. But instead of multiple tables, I want 200
excel spreadsheets (.csv) into one access db.

How can I use VBA to do this?

Thanks!
Dave

 

[Douglas J. Steele]

Rather than importing, I'd recommend that inside the loop you link to the
spreadsheet, run an Append query to take the data from the linked table to
the actual table then delete the linked table. If you use the same name for
the linked table each time, your Append query is simpler.

strTable = "TemporaryTable"
strFolder = "C:\Some Folder\"
strFile = Dir(strFolder & "*.csv")
Do While Len(strFile) > 0
' Strip the .csv from the end of the file name
DoCmd.TransferText acLinkDelim,, strTable, _
strFolder & strFile, True
CurrentDb.QueryDefs("NameOfAppendQuery").Execute, dbFailonError
CurrentDb.TableDefs.Delete strTable
strFile = Dir()
Loop