Combining 4 Excel spreadsheets into 1 Access Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone advise me how to import 4 different spreadsheets into 1 table in
Access 2003?

I have no trouble importing the first spreadsheet, but can't find the way to
import the remaining 3 into the first one.

I will also then need to check for duplicate records.

Thanks in advance for any suggestions.
 
Import them into separate tables (or, better yet, link to them rather than
importing), then write queries to check for duplicates before appending them
all to your final table.
 
Douglas J. Steele said:
Import them into separate tables (or, better yet, link to them rather than
importing), then write queries to check for duplicates before appending them
all to your final table.
Click to expand...

Could be done in one hit e.g.

INSERT INTO MyTable
(MyKeyCol, MyDataCol)
SELECT XL.MyKeyCol, XL.MyDataCol FROM (
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book1.xls;].[Sheet1$]
UNION
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book2.xls;].[Sheet1$]
UNION
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book3.xls;].[Sheet1$]
UNION
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book4.xls;].[Sheet1$]
) AS XL
LEFT JOIN MyTable T1
ON XL.MyKeyCol = T1.MyKeyCol
WHERE T1.MyKeyCol IS NULL

Jamie.

--
 
Jamie Collins said:
Douglas J. Steele said:
table

Import them into separate tables (or, better yet, link to them rather than
importing), then write queries to check for duplicates before appending them
all to your final table.
Click to expand...

Could be done in one hit e.g.

INSERT INTO MyTable
(MyKeyCol, MyDataCol)
SELECT XL.MyKeyCol, XL.MyDataCol FROM (
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book1.xls;].[Sheet1$]
UNION
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book2.xls;].[Sheet1$]
UNION
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book3.xls;].[Sheet1$]
UNION
SELECT
MyKeyCol, MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\Book4.xls;].[Sheet1$]
) AS XL
LEFT JOIN MyTable T1
ON XL.MyKeyCol = T1.MyKeyCol
WHERE T1.MyKeyCol IS NULL
Click to expand...

That'll eliminate the duplicates, but it's possible that the OP wanted to
know about duplicates between the 4 sheets.
 
Back
Top