We help IT Professionals succeed at work.

Import Excel spreadsheet into temp database

I've got a client that wants to import some huge Excel spreadsheets into an Access database, but the column headers and column datatypes don't match the structure of my destination table.

My plan is to import the spreadsheet into a staging table in a temporary database, then use an Insert query to insert the data from that table into the destination table in the current database with a syntax similar to:

INSERT INTO tbl_FinalDest (field1, field2, field3)
SELECT iif(NZ([field1], "") = "", 0, cdbl([Field1]), Field2, Field3
FROM tbl_Staging IN 'C:\zz_Staging.accdb'

But I cannot seem to figure out the syntax to import the Excel spreadsheet into the staging database.

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Can't you just link to the XLS file, and import EVERYTHING as Text into your staging table?

Then from Staging to Final, change your datatypes, etc.

mx
Top Expert 2016

Commented:

use this command line

docmd.transferspreadsheet acimport,,"temptable","c:\folderName\yourExcel.xls",true,"SheetName!"
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
OK guys, that's what I'm doing now, but the inserts into the staging table in the applications backend are causing the backend database to bloat, quickly.

What I "meant" to say, is that I want to link the Excel spreadsheet to a temporary .accdb file, then transform the data into the staging table (also in the temp accdb file).  Then I'll link the staging table to my application and normalize the data into about a half dozen tables that are in my application back-end.

Dale
Top Expert 2016

Commented:
how big is the excel file?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
They are each on the order of 25 Megs, and there are about 3 dozen of them, but after importing only 6 of the files, my database size bloats to over 1 Gig.  If I compact the back-end, it shrinks back to about 200 Meg.

I'm planning on migrating the back-end of this thing to SQL Server Express, but would prefer to not have to stop and compact the front-end after ever couple of files.
Database Architect / Application Developer
Top Expert 2007
Commented:
"What I "meant" to say, is that I want to link the Excel spreadsheet to a temporary .accdb file, then transform the data into the staging table (also in the temp accdb file)."

Well, isn't that what I suggested ? Not sure how you stop the bloating, unless you control it all in code and do C&R's after every X imports ...

mx
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Joe,

I think what I'm going to do is create a "staging.accdb" file, which contains tbl_Staging, the transformation query, and a subroutine, then I'm going to run this code per your response at:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24912053.html
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Cool.  If you are ever out this way, stop in and see the point and click UI that our analysts use each morning to load 6 different dbs with 'reports' (actually XLS files) from IT.

mx