Combining 4 Excel spreadsheets into 1 Access Table

[KCAtkins]

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.

 

[Douglas J. Steele]

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.

 

[Jamie Collins]

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.

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.

--

 

[Douglas J. Steele]

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.

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

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