Solved

Batch import of excel files to Access

Posted on 2014-04-02
10
13,258 Views
Last Modified: 2014-09-04
How can I take an entire directory of 125 different excel files and import them into an Access database without doing it one at a time.  The file names are all different but the "results" area I am wanting to pull out is the same in all of them.  Is there a way to run through a directory, pull in the data, change the file name to zz____  , then go to the next file?
0
Comment
Question by:paigeclements
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 400 total points
ID: 39973634
If the data in them is the same in all of them that is fairly easy:
Public Function Import_Multi_Excel_Files()

Dim InputFile As String
Dim InputPath As String

InputPath = "C:\ExcelPath\"
InputFile = Dir(InputPath & "*.xls")

Do While InputFile <> ""

    DoCmd.TransferSpreadsheet acImport, , "MyTableName", InputPath & InputFile, True '< The true is for column headers
    InputFile = Dir
Loop

End Function

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39975262
Just importing the files this way will be a problem unless the sheet names are different since that is what Access uses to name the resulting table.

If you want the data to end up in the same able, I would link to the sheets rather than importing them and run an append query to copy the data into the target table.  Using this method, having a common sheet name works to your advantage.

And finally, don't forget to consider the other Excel extensions.  It's not just .xls anymore.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 100 total points
ID: 39975501
Building up what Jim posted, you need to avoid processing zz files and rename the workbooks after you import them.
Public Function Import_Multi_Excel_Files()

Dim InputFile As String
Dim InputPath As String

InputPath = "C:\ExcelPath\"
InputFile = Dir(InputPath & "*.xls")

Do While InputFile <> ""
    If InputFile Like "zz*" Then
    Else
        DoCmd.TransferSpreadsheet acImport, , "MyTableName", InputPath & InputFile, True '< The true is for column headers
        Name InputPath & InputFile As "zz" & InputFile 
    End If
    InputFile = Dir
Loop

End Function

Open in new window

0
 

Author Comment

by:paigeclements
ID: 39976113
Thank you guys!!  We made the template so it is just xls.  Don't even ask why this wasn't done via survey monkey or some other survey package - too many non-technical people to fight with and survey monkey doesn't do tables.  Anyway - all of the files are named differently - they each have the name of the hospital and then the month/year of submission. (such as BaptistHealth_Feb2013.xls)

There is a tab they fill in and then a hidden tab with a named range called "results" in all of them.  To link all 125 sheets to access would take just as long as me importing them one by one and I have to do this monthly so I would have to relink every spreadsheet monthly.

The code above is great and should be just what I need.   It allows me to loop through the directory, pull the results, and rename the file.  And of course I wasn't going to name them all zz - it would be zz+filename.  And I can  input the directory to import and specify where to put the table.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

Expert Comment

by:PatHartman
ID: 39976152
The suggestion was to link them one at a time and import the data into a single table and then delete the link and move on and link the next one and import that, NOT to link them and leave them that way.  

At some point you need to get all the data into a single table and the linking method avoids having to have separate tables for each spreadsheet.  The number of  which will multiply every month.  You are not going to let them accumulate are you?  If you import the separate sheets, you now need to create separate append queries or do that in a code loop to get all the data in one place.  If you don't consolidate the data, you end up with an ever growing union query that will break when it reaches its limit which is far lower than 125.  Not to mention the bloat caused by importing all the data into separate tables before appending it to the consolidation table.  You will need to compact and repair after each monthly process.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39976200
It might be easier if you changed the file extension from .xls to .zz, rather than prepend the file name.

When you tweak the code, you will need to reference the results named range.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39976213
As an alternative to linked tables, you can use the IN clause in a query that points to an external data source such as an Excel worksheet or named range.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39976211
Pat,

Part of the DoCmd.TransferSpreadsheet command is to specify the table name as I do in my example.

DoCmd.TransferSpreadsheet acImport, , "MyTableName", InputPath & InputFile

Open in new window


Since I didn't know that he had multiple worksheets in the SS, he will have to specify the range portion which is farther into the TransferSpreadsheet options.
0
 
LVL 1

Expert Comment

by:dougf1r
ID: 40304477
I would like to try implementing the Accepted Solution above. However, as I am relatively new to coding in Access, I am unsure of where to copy and paste this code to for executing in Access. Any help on this fundamental knowledge would be most appreciated. Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now