Importing Multiple CSV Files in to Access

So I read through all the previous post I could find but none of the sample code worked.

I don't need anything fancy.

Just want to import all the files in a directory to 1 table, not necessary named .csv at the moment.  I can make sure all the files in the directory are relevant, so no checking is needed.

I have 3,500 files :)

They are delimited with a comma for the fields I'm interested in.
Later I want to filter out some of the fields but for now I just want to start with some basic working VB code.
LVL 6
DooglaveAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
to accomplish this using automation, you need to create an import specification first


To create the Import Specification
1) Click on external data > text file which then opens another window called "Get external data - Text file"
2) Use radio button to select "Import the source data into a new table in the current database"
3) specify the source of the file using the browse procedure then click OK
4) Choose radio button to select delimited format and then click next
5) this window allows you to choose delimiter and text qualifier and if first row contains names - click next
6) This window allows you to type the name of the field in the Field Name column, choose data type, and if you want field indexed - also can choose to skip field - then click next
7) This window allows you to add primary key or designate field as such - click next

8) Click on ADVANCED button

9. in the import specification window
type the name of the field in the Field Name column
(here you can use the field names of the destination table, specify data type,
check the box Skip if you do not want to import the column)


10 click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acImportDelim, "ImportSpecificationName", "TableName", "C:\yourCsv.csv", True
0
 
DooglaveAuthor Commented:
sweet, that sounds simple.  Thank you.

BTW, I modified this one a little, and it seemed to work.
got rid of the fancy, select folder thing.
and set ".TransferText acImportDelim, , DestTable, fil.path, False" to False as I think it was balking before because I have no headers in the file.. Anyhow, I'm going to do what you said and see if I can make this magic happen on a whole bunch of files.

Sub DoImport()

    Dim fso As Object
    Dim fld As Object
    Dim fil As Object
    Dim FldPath As String
   
    Const DestTable As String = "imptable"
   
    FldPath = "C:\Users\me\Documents\Logs\test"

   
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FldPath)
   
    With DoCmd
        .SetWarnings False
        For Each fil In fld.Files
            If UCase(Right(fil.Name, 3)) = "CSV" Then
                .TransferText acImportDelim, , DestTable, fil.path, False
            End If
        Next
        .SetWarnings True
    End With

    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing
   
    MsgBox "done"

End Sub

Open in new window

0
 
DooglaveAuthor Commented:
Same Single file I tested on before is now returning error:
Run-time error '3049':
Cannot open database ". It may not be a database that your application recognizes, or the file may be corrupt.

I have a single file in this directory called "1.csv" for testing

Sub DoImport()

    Dim fso As Object
    Dim fld As Object
    Dim fil As Object
    Dim FldPath As String
   
    Const DestTable As String = "FABLogs"
   

    FldPath = "C:\Users\me\Documents\Logs\test"
   
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FldPath)
   
    With DoCmd
        .SetWarnings False
        For Each fil In fld.Files
            If UCase(Right(fil.Name, 3)) = "CSV" Then
                .TransferText acImportDelim, "FABImportLogs", DestTable, fil.path, False
            End If
        Next
        .SetWarnings True
    End With

    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing
   
    MsgBox "done"

End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DooglaveAuthor Commented:
MsgBox "fil.Name is " & fil.Name

Returns 1.csv, sooo it's not the filename :(
0
 
DooglaveAuthor Commented:
WTH, I pasted back in the code that I said was working, now same error message.
0
 
DooglaveAuthor Commented:
Ok, whatever.   I closed access and re-opened it and now it works.. :)
0
 
Rey Obrero (Capricorn1)Commented:
try what will this return

MsgBox "fileName is " & fil.path
0
 
DooglaveAuthor Commented:
It's because the file size is already 1.99GB.  And when I delete the table, the size stays.

Must be hiding somewhere else.  But this is not cool!
0
 
DooglaveAuthor Commented:
lol, I did the repair thing and it's 1KB now, trying again.  

Thanks for your help man
0
 
Piru PCommented:
Hello Rey,
Thanks for your script, it works for me, however, i was trying to see if i can modify the column header names to the actual in the text/.csv files. of if your script can pick up the header from the text file itself. Any help on that please?
Cheers,
Piru
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.