There and Back Again (Excel->Access->Excel)

[Dugantrain]

I have an open question for those that have had a lot of experience with data migration back and forth from Excel and Access. I'm just getting started on a brand new project which will put data from several different spreadsheets into a custom-designed Access database. The problem is that whoever designed the spreadsheets knows nothing about db logic and the data is a nightmare (from a developer's stand-point, the spreadsheets actually look pretty nice). Many of the fields are completely denormalized. For example, one "Techs" column may have several techs in the same column, some written as "John Doe, Jane Doe", some as "Doe, John/Doe, Jane", some as "Tech #34", etc. Getting the Access table structures together was no problem at all, took me about 2 or so hours to do and I'm confident that the database will eliminate several hours A DAY's worth of manual effort. The problem is that these Spreadsheets have been around for a while now and I'm looking at a Parent table with about 10,000 records with the child tables having between 1-5 related records each and a third subtable with maybe 1-4 records. That's a lot of data, too much to start over with and too ugly for import. I know that you more experienced guys and gals have probably had experience with this sort of thing as Excel and Access are joined at the hips, so how do you approach such a daunting task?
Note: I know that we all despise our users, but just for giggles, "Tell the users to frag off" or an equivalent is not an acceptable answer :wink:

 

[Cortici]

Hi Dugantrain,

I've had to deal with this situation, so maybe this will help.

I like to use reference sheets. Create a new sheet in the spreadsheet, and have it consist entirely of references to this denormalized data. Then use the reference sheet to organize the data properly. Then have Access just refer to these reference sheets. You could even have several reference sheets, each representing a normalized table.

Oh yeah, and tell your users to stop organizing the data in a stupid manner...

HTH,

 

[A_Pitts]

I recently tackled a similar problem and this is how I handled the problem of multiple versions of the same name.
In Excel, turn on autofilter and open the dropdown selection for the column with the different names. This will give you every unique occurence of a name in that column. In an out of the way location on the sheet, list all the different names in a column with the correct and unique name in a column to their right.
You can then go to the first empty column to the right of your data list (let's say F) and enter this formula:

=VLOOKUP(A1,G1:H50,2,FALSE)

A is the column with the many different names.
G is the column with all the different possibilities.
H is the column with the correct name with 50 indicating the end of the list.
Copy the formula down as far as necessary.
Column F should now contain the correct names. Select this list and copy using PasteSpecial Values into column A.
After you have checked everything, you can delete the formula that you used to save room.
Then setup a Data Validation to limit entry to just the correct and unique names.