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

[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 hate our users, but just for giggles, "Tell the users to frag off" or an equivalent is not an acceptable answer

 

[Pat Hartman]

Remember GIGO - Garbage In, Garbage Out. You need to tighten up the requirements for the relational tables to prevent multiple names from being mushed together for example. So, convert the data that converts cleanly and let the users clean up the rest. You'll need to provide various queries to help them sort it out. Make sure they understand the goal and buy into it. If they won't spend the time to clean up their data (you can help them, but you cannot make any but the most obvious changes by yourself), make sure your management knows and make sure you put a letter in your personnel file so that it doesn't come back to haunt you later.

I was a consultant working on a massive multi-system, multi-company data consolidation project and discovered a quality team at my main client site that had as one of its goals cleaning up customer data. So I joined the team! Even though I wasn't an employee they were very happy to have me since I was able to help them with their goal. And, in the process, they helped me with mine

 

[Dugantrain]

As always, thank you for your comments, Pat. The only problem with getting a lot of external help with this project is that this is sort of a volunteer thing; I mean, of course I'm getting paid at work, but it was my idea to do this project as I was helping my friend out one day with her daily workload, saw the several Spreadsheets that she sifts through to get her needed data, and instantly knew how much a relational database system would help her as well as her co-workers that work with the same data. So, in essence, because this is my idea, I'm free to do what I want so long as nobody else has to touch it. The denormalization thing actually isn't quite as large an issue as I originally thought, I can just ship these fields to a text file and then ship them back to Excel in comma-delimited format, breaking apart any and every comma, space, "/", etc. That certainly won't be an end result, but after writing "Select Distinct" queries in Access, I'll be pretty close and the manual effort won't be nearly so over-whelming. My big concern is the output; the Project Managers won't want to see anything but the Spreadsheets that are already sent to them daily and to get to this from Access will require a whole lot more knowledge of Automation than me and my 6 months of professional development have. Such is the developer's lifestyle, I reckon

 

[Pat Hartman]

Surely if you can show some quantitative savings your management will buy into the process and give some support to your efforts.

 

[Dugantrain]

OK, here's an odd situation: the users of this system divide their daily tasks into "Core Tasks" and "Non-core" tasks. Everything that is included in their Core Tasks don't get billed to the client and everything that is non-core does get billed. All of the Spreadsheet stuff that I'd be saving them time with by developing a database is billable to the customer, so I think that this db might end up COSTING the department money! But I can't stand seeing my coworkers drowning in Excel, so as long as noone complains, I'm going forward with it.

 

[Pat Hartman]

That's my kinda' attitude. Good luck.

It sort of reminds me of my time at a large manufacturing company whose work was almost entirely for the government. All I ever heard at meetings was "that's government money" with the implication that we didn't need to worry about it. Finally I couldn't stand it any more and said, "you're right that's government money which makes it MY MONEY as well as YOUR MONEY and I want to worry about it."