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.
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.
use this command line
docmd.transferspreadsheet acimport,,"temptable","c:\
ASKER
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
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
how big is the excel file?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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:
https://www.experts-exchange.com/questions/24912053/How-to-run-code-in-one-database-from-another-database.html
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:
https://www.experts-exchange.com/questions/24912053/How-to-run-code-in-one-database-from-another-database.html
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
mx
Then from Staging to Final, change your datatypes, etc.
mx