Import Excel - Multiple Header Rows 1

[evalesthy]

I need to import an Excel spreadsheet into an Access table on a recurring basis. The spreadsheet (prepared by another company) has multiple headers embedded in the rows - the headers basically break the data into categories.
For example the first header at Row 1 might be Cars (and have columns for qty, price, etc.) then at row 102 there might be another header indicating the subsequent data is about Trucks, later at Row 295 the there might be a header indicating data is about Motorcycles. The columnar data does not change - just these horizontal header rows break the data into categories.. How do I import the data and handle these embedded headers?

 

[sxschech]

Do you want the headers removed from the data, or use them to create separate tables for each category?

 

[SkipVought]

You have a REPORT that you're working with. REPORTS don't import very easily. I'd pre-process the data, to separate the chunks. You have a phamtom field (vehicle type--cars, truckt, motor cycles) that must be a column in your table, unless you want separate tables for each vehicle type, which, IMHO, is stupid.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[evalesthy]

Unfortunately I'm stuck with the Excel file as is - it is prepared by a large national company we do business with. I need to be able to recognize each embedded header (row) that starts each chunk of data (as I described above). How would I recognize each header in the import file? Or, alternatively, what did you have in mind when you said Pre-process the data Skip? We cannot go in and edit the Excel sheet each time prior to import - that is not realistic.

Thanks

 

[PHV]

We cannot go in and edit the Excel sheet each time prior to import
Why not?
Do it with automation.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

We cannot go in and edit the Excel sheet each time prior to import - that is not realistic.

It is perfectly realistic, via VBA code. Might take a while to develop (hours) bit it would probably execute in seconds.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

AND it can be loads of fun!!

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Oh, not to mention, it can be done probably totally within Access if you prefer... run the code in Access to open the workbook(s) within Excel, do the pre-process stuff, then automatically import the fixed data when finished.

Oh, and while you're at it, you can make the computer talk to you, and tell you when it's finished!

--

"If to err is human, then I must be some kind of human!" -Me

 

[evalesthy]

I would prefer to open the spreadsheet from Access, preprocess it (deal with embedded header rows) and then import to a table.

Here is a rough sample

COMPA
DATE ACCTNUM LOC PARTNUM QTY COST
xx/mm/yy 12345 B1 9876 12 1.25
xx/mm/yy 34568 D3 7856 15 3.27
COMPB
DATE ACCTNUM LOC PARTNUM QTY COST
xx/mm/yy 56487 E1 5454 6 .58

In short, how would I deal with the embedded headers thru coding. Can someone point me in the right direction without taking up too much of anyone's time. Thanks.

 

[SkipVought]

Open the file for input

when you encounter this kind or record,
[tt]
COMPA
[/tt]
you store the value in a variable and then assign to the COMPANY field value.

This kind you ignore, or assign the FIRST time thru the loop to Field Names.
[tt]
DATE ACCTNUM LOC PARTNUM QTY COST
[/tt]
The detail format seems to be identical, so each of those values get assigned to the corresponding field.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[evalesthy]

Thanks - I will work on that and see what I come up with. Thanks.