Excel spreadsheet import to multiple tables 1

[HighLife]

I have a curly problem!

I have an Access database containing customer marketing information, which I've normalised and contains the following tables:

- CustDetail (customers name, email, address etc. PK=Autonumber)
- Industry (customer industries PK=Autonumber)
- CustIndustry (contains 2 foreign keys from Industry & CustDetail to link customers to multiple industries PK=CustID+IndustryID)
- Company (contains company names PK=Autonumber. CustDetail table has CompanyID foreign key to identify customers company)

This works fine and allows customers to be members of multiple industries as is usually the case in our experience. The database has been written for a web based front end for adding and modifying data.

The problem I have is that occasionally we have a need to perform a batch import of customers from an Excel spreadsheet. The spreadsheet contains all this customer data in one table.

I've fiddled with this most of the day but cant see how I can do it - the main problem being the link table "CustIndustry" which requires primary keys from the Industry table and the CustDetail table - both of which the spreadsheet doesnt contain. In the spreadsheet the industries are column headings with a 0 or 1 identifying if a customer is a member or not.

Also the company name - some companies in the spreadsheet already exist in the Access table "Companies" but then some dont. For the batch import to work, the query would have to check the company table for the company, if it doesnt exist - add it, then get the CompanyID and insert into the CustDetail record - for each customer in the spreadsheet?!

It all adds up to a nightmare SQL problem - well for me anyway! I'm thinking that if it is at all possible I may have to modify the spreadsheet layout to better accomodate an import - but to what?

If it looks to be too much trouble I will just insert the CustDetail table data, send the customers an email and get them to add their own company and industry details via the web front end.

Any help/hints much appreciated.

 

[SMerrill]

This is a typical many-to-many (m2m) importing situation.
There are probably several clever ways of importing this data.

If I were tackling this one, I'd probably do this:

(1) write Excel VBA code to add a CustomerIndex (1,2,3,4...) column alongside the CustomerName column.
Clever: Use the COLUMN() function.

(2) Write an IndustryIndex index row above the IndustryName row.
Clever: Use the ROW() function.

(3) Write more Excel code to stripe through each row and column looking for a "1" (which means that this customer is in this industry). For each 1 found in the data, I would write out a 2-column matrix containing the two integers.
Clever: Use the MATCH() function.

(4) Copy/Paste in Excel let's you transpose a matrix. So use it to transpose the industries matrix.

This results in three importable rectangles:

(A) (CustomerIndex, CustomerName)
(B) (IndustryIndex, IndustryName)
(C) (IndustryIndex,CustomerIndex) from Step 3 above.

(5) Import the three rectangles into Access; realizing that these are three temp tables.

Don't confuse your temp table indexes with your real indexes. Use VENN Diagrams to map out what you want to do with each overlapping region. Remember to make three diagrams with two circles each; one circle for the temp table and the other for the real table. Venn diagram concepts are implemented in Access with SQL joins.

(6) Validate the names by doing a join on the names. INNER JOIN gives those that are already there; a LEFT JOIN to either side with a WHERE IS NULL on the arrow-head side will give you the names yet to be imported/deleted.

I know this whole process is truly complex. But that's why they pay you the big bucks.

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[HighLife]

Thanks for that approach. I hadnt considered doing it that way, but will remember it for the next m2m import problem I come across!

I write in cfm and after a bit of brain racking, ended up writing a small import application I can reuse for importing this data. I figured the effort was worth it as there will be a need for future imports inthe same format.

First I import the spreadsheet into a new table in the database called ImportData.

Using a web browser I navigate to my cfm import application, then enter few extra details into a web form like the employee where the customers originated from and any additional comments to be recorded to the database.

I then hit a button and the code runs which loops through each record in the ImportData table and inserts/updates the corresponding tables in the database.

I'm not as familair with VBA and native MS Office functionality than I am with databases and web programming unfortunately, but this seemed to work well - or will once I iron out a couple of bugs!

Cheers

 

[SMerrill]

Very well. I'm convinced you can achieve an extreme amount of efficiency by using "set-based" relational theory instead of looping through the records and rows one element at a time.

I have enjoyed chatting with you about this.