Solved

Importing Multiple CSV Files in to Access

Posted on 2013-11-20
10
12,355 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 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