CODEWITHSUNDEEP
sqldatabaseexcelms-access
John
John

Reputation: 23

Excel Import Into Access

I have 3 spreadsheets that I want to import into Access but I"m having a problem figuring it out. I'm not even sure it can be done the way I want to do it.

One Spreadsheet looks like this:

StoreNum, SKU_1, SKU_2, SKU_3, ..., SKU_1000  
1, 0, 100, 25, ..., 1  
2, 5, 250, 15, ..., 5  
3, 78, 25, 12, ..., 15

How can I import this so I have StoreNum, SKU, Qty?

The others are easy for me to understand

StoreNum, Address1, Address2, City, State, Zip

and

Sku, Description, Notes, Size

Upvotes: 2

Views: 2910

Answers (2)

Desert Spider
Desert Spider

Reputation: 778

When you open your Access database click;

File

Get External Data

Import

And follow the Wizard Steps.

A few caution points. Make sure that all of your excel sheets have the same headers, and do not contain any Null, DIV, or any other type of errors. Happy importing!!!

Upvotes: 0

HansUp
HansUp

Reputation: 97131

I think you want to store the spreadsheet data in Access like this ...

StoreNum  SKU Qty
       1    1   0
       1    2 100
       1    3  25
       1 1000   1
       2    1   5

If that's what you want, keep reading. If it's not what you want, please clarify what you want.

In Access, create a link to the Excel worksheet and name that link ExcelSource.

Then create a SELECT query similar to this:

SELECT
    StoreNum,
    1 AS SKU,
    SKU_1 AS Qty
FROM ExcelSource
UNION ALL
SELECT
    StoreNum,
    2 AS SKU,
    SKU_2 AS Qty
FROM ExcelSource
UNION ALL
SELECT
    StoreNum,
    3 AS SKU,
    SKU_3 AS Qty
FROM ExcelSource
UNION ALL
SELECT
    StoreNum,
    1000 AS SKU,
    SKU_1000 AS Qty
FROM ExcelSource;

If the result set from that SELECT query gives you what you need, convert it to an "append query" to store those data in a table named YourTable. The SQL for that query will be the SELECT SQL preceded by an INSERT INTO ... section:

INSERT INTO YourTable (StoreNum, SKU, Qty)
SELECT
    StoreNum,
    1 AS SKU,
    SKU_1 AS Qty
FROM ExcelSource
UNION ALL
SELECT
    StoreNum,
    2 AS SKU,
    SKU_2 AS Qty
FROM ExcelSource
UNION ALL
SELECT
    StoreNum,
    3 AS SKU,
    SKU_3 AS Qty
FROM ExcelSource
UNION ALL
SELECT
    StoreNum,
    1000 AS SKU,
    SKU_1000 AS Qty
FROM ExcelSource;

If your Excel worksheet includes 1000 SKU columns, create a series of smaller append queries, each of which uses a manageable subset of those 1000 columns.

This could also be done with VBA code rather than a query. I don't want to lead you through that option because I don't even know if I'm on the right track here.

Upvotes: 1

Related Questions