Import and convert a pivot table?

[ttomw]

I need to import data in this format:

The top row of numbers are AttributeIds

In order to import it into my DB I need to convert it into the following fields and records:

etc.

Any fields that are null should not get a record in the conversion.

Any ideas?
Thanks Much!
Tom

 

[namliam]

There are 2 ways to do this:
1)
Import your table into a temp table
Run append queries per column to get the imported table into the desired normalized format

2)
Write VBA to import the table directly, a little more complex though likely cleaner and no risk of bloating.

 

[ttomw]

There are 2 ways to do this:
1)
Import your table into a temp table
Run append queries per column to get the imported table into the desired normalized format

2)
Write VBA to import the table directly, a little more complex though likely cleaner and no risk of bloating.

Thanks for the suggestions.

The images I posted are just small subsets. The actual dataset has 26 attributes and ~27000 records. So while option 1 is doable, it wouldn't be very fun, especially if I have to reimport or maintain the data on regular basis.

I am hoping to find a tool or module or vba code that can handle this operation. I am a newbie to scripting...

 

[namliam]

There wont be any vba available to will cover your problem specificaly.

Both versions though will have issues with re-processing the same data (again).

The queries can be run automated as well either thru VBA or Macro

 

[kiwipeet]

What about importing the data

 

[kiwipeet]

Into a table and create a view which does the conversion on the fly?