VBA Import Routine for Data Transformation

[GDTRFB]

Question: How can I transform data from columns to rows and auto-populate foreign keys using VBA from a spreadsheet import data source?
Scenario
I have a situation where I have to import data from a MS Excel spreadsheet into an Access DB.
The spreadsheet data is in a flat file format.
The DB destination table has the following one-to-many relationship where PK = primary key, FK = foreign key, Fld = field/column
tblDestination: PK, FK, Fld_A, Fld_B, Fld_C
In terms of the destination table (tblDestination) configuration, the import spreadsheet is populated in a format where R = record/row:
PK, FK, R1, R2, R3, R4……..R12
There are 12 known entries for each FK.
By that I mean for a given row in the spreadsheet, there are 12 columns in the spreadsheet that will become rows in the destination table (tblDestination). This will always be the case for this particular Parent/Child combination.
The PK in tblDestination is an Autonumber field so nothing will be imported in the DB.
Solution Objective
I would like to:
1. Import the spreadsheet into a temporary table (tblTempImport)
2. Run a VBA routine on the table which:
a. Transposes the data into proper format for tblDestination (i.e. columns > rows)
b. Assigns the correct FK for each row in tblDestination

I’ve never done anything like this before and was wondering if anyone has solved a similar problem.
Many thanks for your suggestions

 

[Cronk]

Importing data from a de-normalized spreadsheet is something I've encountered any number of times over the years.


Your thinking is on the right track. Firstly import all of the spreadsheet data into a temporary table, with an autonumber PK. I normally in the first instance import into text fields, maybe dates as well if possible.


I say "if possible" because the spreadsheet may not have some cells in a proper date format.



Cleanse your data. Make sure that a column that should be numeric does not contain non numeric data.


Use queries wherever possible to normalize your data. Group on the relevant columns to create lookup tables with an autonumber PK. Then join the look up table to the temporary import table to create data tables with the foreign key from the lookup table(s).


Once you get into it, and maybe after a couple of false starts, it should all come together for you.


Come back if you hit a brick wall, preferable with a small data sample.

 

[The_Doc_Man]

My question is, when you pivot the elements, your row that is PK, FK, FldA, FldB, etc. becomes (in field 1 of the resultant table) PK, FK, R1=FldA, R2=FldB, ...R10=FldJ. But what, if anything, is in the second field? Or, stated another way, what was in the spreadsheet's 2nd row BEFORE you pivoted?

I would also point out that in Access treatment, the PK and FK can no longer be treated as keys of the same kind as they were before the pivot. So I think we need to know what else is being pivoted when you do this.

 

[Dreamweaver]

I used to import chart data from txt and excel ill see if I can find those functions but it was 10 years ago I do remember one thing I had to import all the data into a temp table.

 

[GDTRFB]

Hi all, thanks for the suggestions. hopefully to better illustrate what i'm trying to do, I've attached a spreadsheet with some dummy data.

 

[The_Doc_Man]

From your picture, you are doing a partial pivot, not a complete one. You will have to program this using VBA and some form of Excel Application Object.

Use the SEARCH features of this forum to look up the topic of Excel Application Object. In essence, you would have to read the Excel file one row at a time and extract what you want into your table one detail record at a time.