How to analyze and split data during or after importing

[Guest]

I am importing data from an Excel table with 19,000 lines of dirty data. When
I imported the data into Access 2002, some of it was cleaned up, but rather
arbitrarily. I need to fix it all either before and/or after importing.

Examples:
1. Phone numbers are in one column, but I need to split out the Area Code
into a separate column. At least Access stripped out the '()' and '-'
characters, leaving 10 digits in a Text column.
2. Zip Codes with leading 0's were stored as 4 digits. Zip+4 Codes with
leading 0's have 8 digits (stripped 0's and '-'). Again, I imported them into
a Text column.
3. Several names have the Middle Name in the First Name column.

 

[Guest]

Rick,
We have had the same problem you are facing. We tried every method we could
think of in terms of linking, importing, preformatting the spreadsheets, and
using existing tables. None of these approaches were completely successful.
Our solution was to use VBA to read the data directly from the Excel
spreadsheets and populate tables from that.
We did not use linking or importing. We used automation to open the
spreadsheet as an Excel object, read the data out of each cell, format it to
our needs and write the data into the Access tables.
This is a bit more tedious to create, but we have had good success with it.

 

[John Nurick]

I find it best take each case as it comes. Useful tactics include:

*Add columns to the Excel sheet and fill them with formulas that identify
rows or cells with problematic data so rare problems can be fixed manually.
Then delete these columns before importing.

*Add columns to the Excel sheet and use formulas in them to split the values
in other columns. When correct, use Copy, Paste Special|Values to replace
the formulas with the values. Then delete the no-longer-needed columns.

* Save the Excel sheet as a CSV file and import or link that into Access.
Access gives you much more control (e.g. of field types and sizes) when
you're importing or linking a text file.

* Treat the imported or linked table as temporary, and use an append query
to clean up the data further as you move it into your "main" table.