Merging Tons of Contact Databases all with different formatting

[pqburg923]

I finished up a project recently where different individuals were maintaining their own separate excel files. The excel files were essentially contact lists.



There are a number of issues with the excel files which is why I have a feeling ACCESS may be helpful to merge all these excel files, clean them and store them in some sensible manner. The issues are:

1. Each person collected different information. Person A collected name, email, address. Person B collected name, email, city.

2. Each person titled their headers differently (ie "email" vs. "email address")

3. In the email column, people sometimes entered multiple emails in one excel cell

4. In the email column, people sometimes wrote john.doe@gmail rather than john.doe@gmail.com

I have almost no experience with access, but would like to understand how you guys would proceed managing this task. My thought was to try importing each of the excel files and somehow having access clean up and organize all this data for me. I'm not sure how to approach this and what are the critical steps. Thanks for your feedback.

[NoellaG]

Hi,

- first of all, look at all the fields people created and decide which information you want to keep.
- Then design the tables you need for storing the information you want to keep. Use the normalisation rules (3th normal form will do) to guide you along the design process.
- Then you can import the excel data into worktables and use action queries to clean them up and pump them into your Access tables.

Don't expect a straightforward smooth job. Importing non-structured Excel data into a relational database is an adventure. I'm always amazed when I see how creative people can be when entering data...

succes
NG