Importing data from a txt file and input masks

L

Liz J

Hi

I have a table that I have set up and I want the Customer ID to have an
input mask of LLL0000 (I think that is 3 mandatory letters followed by 4
mandatory numbers).

I set up the input mask and then I need to import data to the table from a
txt file. I am using the import text wizard for this that I find in File,
Get external data.

The data comes into the table fine but I need it to trap any Customer ID
that does not conform to the input mask and it is not doing so.

How can I set an input mask (or validation) that will trap any Customer IDs
that do not follow the correct pattern?

TIA

Liz
 
D

Douglas J. Steele

Input masks only work with forms, not importing data.

You'll have to write a query that goes against the data and identifies any
invalid data.
 
L

Liz J

Thanks for the reply Doug, much appreciated.

Is there nothing I can do in the validation setup that would trap this?

I need this for an assessment my students are doing at school and the sort
of query you mention would, I think, be out of their range.

TIA

Liz
 
D

Douglas J. Steele

The only validations that occur during import check that the values are
correct for the data type, and that they don't violate referential integrity
(if set).

It's a fairly simple query to check for invalid values, though:

WHERE CustomerID Not Like "[a-z][a-z][a-z][0-9][0-9][0-9][0-9]"
 
L

Liz J

I imported my data and the record with the incorrect CustomerID was present.
(FREO149 instead of FRE0149). I then set up a query on the customer table
with the criteria you suggested and ran the query but the record was not
found. The query returned no records.

Am I being particularly daft tonight?

Liz

Douglas J. Steele said:
The only validations that occur during import check that the values are
correct for the data type, and that they don't violate referential
integrity (if set).

It's a fairly simple query to check for invalid values, though:

WHERE CustomerID Not Like "[a-z][a-z][a-z][0-9][0-9][0-9][0-9]"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Liz J said:
Thanks for the reply Doug, much appreciated.

Is there nothing I can do in the validation setup that would trap this?

I need this for an assessment my students are doing at school and the
sort of query you mention would, I think, be out of their range.

TIA

Liz
 
D

Douglas J. Steele

Couldn't tell you whether you're being daft <g>.

Double-check that you typed the WHERE statement correctly: I just tested on
your sample data, and that criteria returned FREO149 as not being
acceptable.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Liz J said:
I imported my data and the record with the incorrect CustomerID was
present. (FREO149 instead of FRE0149). I then set up a query on the
customer table with the criteria you suggested and ran the query but the
record was not found. The query returned no records.

Am I being particularly daft tonight?

Liz

Douglas J. Steele said:
The only validations that occur during import check that the values are
correct for the data type, and that they don't violate referential
integrity (if set).

It's a fairly simple query to check for invalid values, though:

WHERE CustomerID Not Like "[a-z][a-z][a-z][0-9][0-9][0-9][0-9]"


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Liz J said:
Thanks for the reply Doug, much appreciated.

Is there nothing I can do in the validation setup that would trap this?

I need this for an assessment my students are doing at school and the
sort of query you mention would, I think, be out of their range.

TIA

Liz


Input masks only work with forms, not importing data.

You'll have to write a query that goes against the data and identifies
any invalid data.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


"Liz J" <thejordanschangethistoanatsignclara.net> wrote in message
Hi

I have a table that I have set up and I want the Customer ID to have
an input mask of LLL0000 (I think that is 3 mandatory letters followed
by 4 mandatory numbers).

I set up the input mask and then I need to import data to the table
from a txt file. I am using the import text wizard for this that I
find in File, Get external data.

The data comes into the table fine but I need it to trap any Customer
ID that does not conform to the input mask and it is not doing so.

How can I set an input mask (or validation) that will trap any
Customer IDs that do not follow the correct pattern?

TIA

Liz
 
L

Liz J

Hi Doug
Many thanks, that worked well. I think I left the WHERE in even though I
was putting it in the criteria box in the query design.

thanks for your help, much appreciated.

liz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top