Solved

Importing Multiple CSV Files in to Access

Posted on 2013-11-20
10
12,063 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
10 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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