Clean Out Imported and Linked Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What is the best approach to clean out data from various
sources (excel, linked tables, other databases) when
importing? Cleaning out data is not a one time occurance
but it is a daily requirment in my role as a report
specialist.

For Exp:
- Zip Code fields may be imported without dashs
- Data from some systems may include invisible lead
characters
- Percentages are imported in various formats
- Character case may be in all lower and I would like to
see them as "John Smith"

Should I use macros? VB? Find and Replace?
 
Hi,

Access macros aren't much use for this sort of thing.

Queries are the most important tool, much more powerful than Find and
Replace. Many jobs are easily done with an update query to modify values
in a field; sometimes it's better to add a new field to the table and
use one or more update queries to populate it with values derived from
another field or field. Or use append queries to move data from a linked
or temporary table into the table where you really need it.

By using VBA functions (including custom functions) in calculated
fields, setting criteria on calculated fields, and using the LIKE
pattern matching operator in criteria, one can accomplish a great deal
with simple queries. "Find unmatched" and "find duplicates" queries help
you relate new and existing data.

Sometimes it's easier to clean up the data before importing: for
instance, use Excel formulas to identify incorrect values in columns in
a spreadsheet so you can correct them before you import or link to
Access. If you have to do a lot of work tidying up text, it's worth
learning how to use regular expressions (high powered pattern matching)
in a language such as Perl or VBScript (Perl is more powerful but the
VBScript regular expression object can be integrated into VBA functions:
see http://www.mvps.org/access/modules/mdl0063.htm for an example.)
 
Back
Top