How to import two text file?

[Guest]

Hello,
I am new to access so please advice.
I have two sources of text file to be import into 1 table (Access2K, ODBC
link to SQL Server2K)
Table structure:
ClientID(PK) Agency DOB SubmitDay
(interger) (interger) (Date/Time) (Date/Time)

Text file#1 (Tab delimited)
1234 1 1/1/1980 12/1/2004
1235 20 12/20/2000 11/20/2004

Text file#2 (Tab delimited)
0222 02 121981 1212004
0223 11 12212000 11222004
Question:
1- How to I import text file#2 into table with designed Date/Time?
2- If the text file 1 and 2 the fields invalid date then how can I eliminate
them?
Thanks you for all advices,
Regards,
MN

 

[David Seeto via AccessMonster.com]

1- How to I import text file#2 into table with designed Date/Time?

Your best bet would be to import it into a temporary table like:
ClientID(PK) Agency DOB SubmitDay
(integer) (integer) (Text) (Text)

You could then write an append query to copy records from this table to your actual table, converting the "dates" in your temporary table to real dates, using a function like:
DateSerial(Right([DOB],4),Mid([DOB],3,2),Left([DOB],2))

To do this, however, you're going to need to enforce leading zeros in your input file: otherwise, you have no way of telling whether 1122000 = 11/2/2000 or 1/12/2000

2- If the text file 1 and 2 the fields invalid date then how can I eliminate them?

What do you mean by "invalid" and "eliminate them"? If your try to put something that isn't a date (eg. 1/21/2000) into a date/time field during an import, MS Access will still create the record, but substitute a Null value in that field and write out the error to an <TableName>_ImportErrors table.

If, however, the field is a valid date but not for the field in question (eg. Dates of Birth cannot be entered for future dates), the simplest solution is probably to set a Validation Rule on that field in the table design. Unfortunately, if you attempt to import a record with a date violating this rule, Access drops the whole record - if that's ok, fine. If not, you'll have to get more sophisticated, and use temporary tables and update queries to check for and eliminate invalid dates before appending the data into the proper table.

 

[Guest]

Thank you David for taking your time.
Regards to question #2:
Yes, invalid date look like 20/20/2004... I want to write these errors to a
different table, so I did not know it will create a error table - It is
answer my question. I will try it today. Thanks again.
Best Regards,
MN