?
Solved

Importing Multiple CSV Files in to Access

Posted on 2013-11-20
10
Medium Priority
?
12,752 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

765 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