Importing and appending excel worksheets into Access

Hi,

I'm working on a project which requires me to import a few hundred Excel worksheets into one Access table. Is there any was to automate this task, rather than using the import wizard each time?

The excel worksheets has approx. 30 fields (columns) each and are named tmprecs01-300, tmprecs02-300, tmprecs03-300, tmprecs04-300..... tmprecs-254-300.

Is this possible?   Thanks

msawill.
msawillAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tkuppinenCommented:
If you know the names of the spreadsheets you can use the TransferSpreadsheet method to import these sheets.  This is done through code and if you need an example I can post one for you.
0
PShieldsCommented:
Does the data change in the excel worksheets so you will have to import them again at a later date.  how about linking to them and then use a union query or append query to get all of the worksheets together.  If you use an union query any changes to excel would show up in the union query.
Phil
0
HANDeMANCommented:
You will need to create a function that does the following:

1. Determine the path where the spreadsheets are stored.  Obvious implications are: unique file names, where to find the path name.
2. Find the first file using the DIR$() function.
3. Create a loop that uses the DIR() function to find the next spreadsheet file. Exit if "".
4. Use the TransferSpreadsheet Method to import the data from the spreadsheet into a temporary table for data manipulation and logic processing or directly into an existing table.  Implications: unique values in the destination table, translations, etc.

Code available.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

abaldwinCommented:
A method that has saved me a lot of time and Headaches is similar to the post from HANDeMAN.  While looping through the directory where the files are stored (or moving them to a temporary folder insuring you only import the excel files you want), take the time in the code process to open the excel sheets and process each line or record, applying validation rules that exist in your database and checking for duplicates, blank lines etc.  Although this seems like a long process it sounds like you are in the middle of something that may repeat in a month or two.  Writing a good validating app up front will save you time and money later.
0
JimMorganCommented:
albadwin:  Welcome to EE.

Watching your first start in EE, at least in the Access topic, apparently you are pushing the wrong button when you make a comment.  You are pushing 'Answer' where you should be pushing 'Comment'.

Be careful in the future please.  All these questions that you responded to improperly have been taken out of the 'unanswered' area and so only the more knowledgeable experts will be able to respond.

Also there is the embarassment for the Asker to now go through and 'Reject' your answers so the question can be opened up again.

Jim
0
msawillAuthor Commented:
Thanks so much for responding to my question. here's some aditional info..

This is a one-time process that has to be done, and the worksheets have unique names( a sequential number - as part of the file name hence..)

 tmprecs01-300.xls
 tmprecs02-300.xls
 tmprecs03-300.xls
 tmprecs04-300.xls
 tmprecs05-300.xls
 ...
 ...
 tmprecs254-300  

Can you please send the vb code to accomplish this task??

Thanks.

msawill
0
JimMorganCommented:
Is the Access table already created?

If so then you can use a simple DO/LOOP to find all the files and import them.

If all of the files are sequentially numbered do this:

Sub ImportExcel()
    Dim strFileName as String
    Dim strExt as String
    Dim intCount as Integer
    Dim strTable as String
    Dim strPath as String
    strExcelExt = "-300.XLS"
    strTable = "name of access table to import to"
    strPath = "path to drive and directory holding excel files"
    intCount = 0
    Do
       intCount = intCount + 1
       If Len(Dir$(strPath & strFileName & Cstr(intCount) & strExt)) Then
          DoCmd.TransferSpreadsheet acImport,,strTable, strPath & strFileName & Cstr(intCount) & strExt
       Else
          Exit Do
       End If
    Loop

If they run a range but a number might be missing, use this:

Sub ImportExcel()
    Dim strFileName as String
    Dim strExt as String
    Dim intCount as Integer
    Dim intEnd as Integer
    Dim strTable as String
    Dim strPath as String
    strExcelExt = "-300.XLS"
    strTable = "name of access table to import to"
    strPath = "path to drive and directory holding excel files"
    intCount = 0  '(or starting # - 1)
    intEnd = 255  '(or ending # + 1)
    Do
       intCount = intCount + 1
       If intCount = intEnd Then Exit Loop
       If Len(Dir$(strPath & strFileName & Cstr(intCount) & strExt)) Then
          DoCmd.TransferSpreadsheet acImport,,strTable, strPath & strFileName & Cstr(intCount) & strExt
       End If
    Loop

You could use the DIR() to pull the next filename is the directory but I felt that sequence was important and so one of the above routines is best.  If you use the second routine and one file is not valid, the process will continue.

The first routine will stop with a missing file number.  If this happens, set intCount to the missing file number and run it again.  It will continue with the next.

Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HANDeMANCommented:
Likewise:

Add this function to a class module of a form or standard Module.

Function gsImportSS(vstrPathName As String, vstrTableName As String)
    Dim strFile As String, strPath As String
    strPath = vstrPathName & IIf(Right(vstrPathName, 1) = "\", "", "\")
    strFile = Dir$(strPath & "*.xls")
    While strFile <> ""
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, vstrTableName, strPath & strFile, True
        strFile = Dir
    Wend
    '
    '   Now that all of the data has been imported into a table (vstrtableName)in Access,
    '   run appropriate queries or methods to import/update records in the
    '   database using records in this table.
End Function

Run the function by providing the path where the spreadsheets exist and the table where you want to import the data into.  You can run the function from the Intermediate Window or from a form that provides the parameters from text boxes.

If the table doesn't exist, the table is created.  In this case, after the data is collected from all of the spreadsheets, you can provide whatever logic you wish using:
a) queries to append the records into one or more tables of the destination database.
b) logic to provide translation of data from the raw data to values to be stored.
c) reports to group and count records
d) anything else you want to do with the raw data before actually importing it into a production database (recommended).

Believe me...report on it, have it approved, then update your production database (its much safer this way!).
0
JimMorganCommented:
I recognize the merit in your method and even though msawill said that this is a one-time situation, experience has proven that there is not such thing as a one-time situation.

Depending on the wildcard file input would mean that the directory only contains the files that you want imported and no others (will really screw up the table) and that after the files are imported that the directory be cleared so the data is not duplicated.  Also is something happens in the import process, it can not be run again without emptying the table first or duplicate records will be added.  By naming the files, you cut down the risk of importing the same file more than once.  I noticed too that you are forcing Excel to send out its field names as the first line of each workbook.

You'll notice that I left out the spreadsheet type in my code.  msawill didn't indicate what version of Excel that the data was created in.  The default (none entered) is the most simple and will handle most spreadsheets.  If there is a particular version and the data has information that is unique to that version, then the spreadsheet types to user are:

acSpreadsheetTypeExcel3 (default)
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel97

Experience has shown that when dealing with Excel tables, Excel handles dates differently than Access.  When the table is generated at the first import, the date field might not be interpreted by Access correctly which may cause errors and no data imported.  If the table is designed first, then Access will follow the datatypes in the table and not try to guess what they are.

Another problem that might be run into, if a date field is null in the spreadsheet, no data will be imported for that record into Access.  It seems a little late to do this now, but null dates should be set to something like a 0 date - 01/01/1900 12:00:00 AM.  After the data comes in then these dates should be set back to null.

Jim
0
HANDeMANCommented:
Many valid points.  And many unknown specifications.  I believe a framework of logic has been provided to begin the process of creating a solution for msawill.  The code examples above in both cases should provide a template for the solution, not THE solution.  Let the user try the methodologies to find his/her own solution.  There has to be a learning process and if questions come up, they know where to go to find the answers.  
0
brewdogCommented:
one clarification: it sounds to me like msawill has a bunch of worksheets in one workbook rather than a bunch of different workbooks. Is that the case, msawill? If so, then you'll need to modify whatever code you choose to work with to point to a specific sheet in the workbook . . . which in this case simply means setting up a

For i = 1 to 255

Next i

structure, where you feed the name of the worksheet with the i variable.
0
msawillAuthor Commented:
hi,
Many thanks to to JimMorgan for and excellent solution to my problem - it worked perfectly (with some minor changes). I'm grateful also to the following ppl. who took time to respond and share their expert knowledge.

tkuppinen, PShields, HANDeMAN, abaldwin and brewdog.

Thanks again...

msawill.
0
JimMorganCommented:
msawill

You'er welcome.  Always glad to be of help.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.