Import Excel Spreadsheet into Access

[Guest]

My US-based company imports containers of products from overseas.
I am a trying to take a large excel spreadsheet that is in a matrix format
like this:

ContID WDFG TRBN GHWR
ShpCod1 12345 56445 4818
ShpCod2 7898 8878 4566
ShpDt 27-Apr 14-Apr 21-Apr
ArrivDt 28-May 28-May 4-Jun
ProductA 10 0 0
ProductB 0 0 2
ProductC 0 53 2


....And import it into an Access table in a normalized format like this:

Item ContID ShpCod1 ShpCod2 ShipDt ArrivDt Qty
----- --------- --------- ----------- --------
-------- -----
ProductA WDFG 12345 7898 27-Apr 28-May 10
ProductA TRBN 56445 8878 14-Apr 28-May 0
ProductA GHWR 4818 4566 21-Apr 4-Jun 0
ProductB WDFG 12345 7898 27-Apr 28-May 0
ProductB TRBN 56445 8878 14-Apr 28-May 0
ProductB GHWR 4818 4566 21-Apr 4-Jun 2
ProductC WDFG 12345 7898 27-Apr 28-May 0
ProductC TRBN 56445 8878 14-Apr 28-May 53
ProductC GHWR 4818 4566 21-Apr 4-Jun 2

I can get part of the way in Excel using the the "transpose" feature under
the "paste special" function, but then there's a lot of manual cut/paste. I
do not know VBA or SQL. Any suggestions on a less labororious method is
greatly appreciated!

 

[Guest]

Why don't you try linking to your table. Once that is done, use your linked
table to build a query or queries to get things formatted the way you want
them and then use a make table query to actually pull your data into access.