how to correct data upon import into access

[Barry]

I am importing/linking spreadsheets into a ms access(2000) database.
However, the format of the data of the columns I intend to join on
needs massaging first!

For instance:
Example 1
input field x may contain:
123456 or 0123456 or 00123456 - I would like the field to padded to 8
digits with leading zeroes.

Example 2:
input field y may contain:
nnnnn or Mnnnnn or MnnnnnX or ISMnnnnn - I would like the field to be
Mnnnnn.
Here we are stripping and/or adding trailing or leading characters.

What is considered the simplest method of correcting the data?
The import wizard does not seem to support data validation.

Regards,
Barry

 

[John Nurick]

Hi Barry,

As you say, the built-in import and linking routines don't give much
control. Very often the best thing to do is to import to a temporary
table and then use an append query to munge the data as it is moved to
your "main" or "permanent" table. Comments inline:

I am importing/linking spreadsheets into a ms access(2000) database.
However, the format of the data of the columns I intend to join on
needs massaging first!

For instance:
Example 1
input field x may contain:
123456 or 0123456 or 00123456 - I would like the field to padded to 8
digits with leading zeroes.

Ideally, store these as text in Excel by putting an apostrophe at the
start of each along with the leading zeros:
'00123456
'00004321
That way, they import cleanly (without apostrophes) to an Access text
field. Otherwise, import or link temporarily as numbers and use
Format([Field],"00000000") in Access to convert them to strings with the
leading zeros in the "main" table.

Example 2:
input field y may contain:
nnnnn or Mnnnnn or MnnnnnX or ISMnnnnn - I would like the field to be
Mnnnnn.
Here we are stripping and/or adding trailing or leading characters.

What is considered the simplest method of correcting the data?
The import wizard does not seem to support data validation.

Import to a temporary table and use functions such as InStr() and Mid()
in an append query to modify the string as it's moved to the permanent
table.