Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Importing Multiple CSV Files in to Access

Posted on 2013-11-20
10
Medium Priority
?
13,644 Views
Last Modified: 2016-08-22
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.
0
Comment
Question by:Dooglave
  • 7
  • 2
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39664347
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
 
LVL 6

Author Comment

by:Dooglave
ID: 39664407
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
 
LVL 6

Author Comment

by:Dooglave
ID: 39664495
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:Dooglave
ID: 39664506
MsgBox "fil.Name is " & fil.Name

Returns 1.csv, sooo it's not the filename :(
0
 
LVL 6

Author Comment

by:Dooglave
ID: 39664528
WTH, I pasted back in the code that I said was working, now same error message.
0
 
LVL 6

Author Comment

by:Dooglave
ID: 39664535
Ok, whatever.   I closed access and re-opened it and now it works.. :)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39664543
try what will this return

MsgBox "fileName is " & fil.path
0
 
LVL 6

Author Comment

by:Dooglave
ID: 39664662
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
 
LVL 6

Author Comment

by:Dooglave
ID: 39664671
lol, I did the repair thing and it's 1KB now, trying again.  

Thanks for your help man
0
 

Expert Comment

by:Piru P
ID: 41766372
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question