how to correct data upon import into access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barry - 15 Jan 2004 21:46 GMT

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

Reply to this Message

John Nurick - 16 Jan 2004 05:55 GMT

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
[quoted text clipped - 5 lines]
>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:
[quoted text clipped - 4 lines]
>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.

>Regards,
>Barry

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message