Help with Import from Excel Spreadsheet

I have an unusual looking Excel spreadsheet that is produced by our IT
department. I am looking for some help as how to configure the import
into Access.

The layout of the Excel spreadsheet is listed below. I am using the
pipe character to indicate that the data is in the next cell.
However, the address information (lines 2,3,4) is not separated across
different cells other than by separate rows. Also note that there is
a string of dashes serving as a sort of record delimiter:


Customer Number(n) | Sales Person | Initial Order Date| Line of
Business | Geographic Region
William Brown|
123 Main Street|
Rockledge FL 32955|

------------------------------------------------------------ ------------------------------------------------
Customer Number(n+1) | Sales Person | Initial Order Date| Line of
Business | Geographic Region
Ernie Simon|
541 W. Main Street|
Suite 231|
Cocoa Beach FL 32912|
------------------------------------------------------------ ------------------------------------------------
Customer Number(n+2) | Sales Person | Initial Order Date| Line of
Business | Geographic Region
Dave Johnson|
552 Harbor Drive|
Port Richey FL 32119-9818|

Initially I am thinking I should load to two different tables
(tbl_Customer) and (tbl_AddressDetails) as such:

tbl customer
CustomerNumber
SalesPerson
InitialOrder
LineofBusiness
GeographicRegion

tbl_AddressDetails
CustomerNumber
Address1
Address2
CityStateZip

but I would be happy to load into one table if that would be easier

tbl_customer
CustomerNumber
SalesPerson
InitialOrder
LineofBusiness
GeographicRegion
Address1
Address2
Address3
CityStateZip



I am stumped as to where to begin with this! Ultimately this would be
an automated process to load monthly customers, and I believe I can do
this with VB rather easily, but I can't seem to grasp the best
solution for defining the process.

All help will be greatly appreciated!

Thanks,

Svenman

Re: Help with Import from Excel Spreadsheet

Hi Svenman,

I'm not really understanding the layout of the spreadsheet here, but
are you saying that the City State Zip is all in one cell?

If so, you can parse out the column using excel, so it will map
correctly in Access when you import. There's a way to do that using
vba as well, but honestly, I'm not sure of the procedure.

As for the table structure, it all depends on whether or not you're
going to have more than one address for each customer number. If
there's even a slight chance that there might be, I would make two
tables, one with customer info, one with address info, and join them
on the customerID number.

HTH,
Kate