Importing large text file

[Chris L.]

I'm importing a text file with 300,000 records of 51 columns each. The
method I'm using, is linking the file and creating an append query
onto a empty table (can't import directly because some columns are pre-
processed with VBA functions)

Upon finishing, there's an error message. The message is in spanish
but roughly says something like "Access has set 7 values to null
because of type conversion failure, and skipped 0 records because of
key infractions, 0 records because of blocking infractions and 0
records because of validation rules infractions .. Do you wish to run
the action query anyway?"

Clicking YES causes all the 300,000 records to be inserted into the
table, but of course there are 7 of them which are incomplete. (Or
maybe there's ONE record with 7 null columns, I don't know) How could
I try and find out which ones? (If this was a paste operation, there
would be a "paste errors" table with the error description, but
apparently there is no such thing on action queries, or is it?)

(And I tried linking and copying/pasting but the limit seems to be
65000)

If I insert each record individually using VBA, (cringe) will I be
able to trap this error to at least find out which record(s) are
causing trouble? Or is there some easier way?

Thanks in advance!
C.

 

[John W. Vinson]

"Access has set 7 values to null
because of type conversion failure

My *guess* is that there is either a Number or a Date field in the input file
which contains invalid characters. It will be tedious as all get-out, but try
creating a Query using the IsNumeric function to find non-number values in
each field which should be numeric:

SELECT * FROM linkedtextfile
WHERE Not IsNumeric(FieldX) OR Not IsNumeric(FieldY) Or Not IsNumeric(FieldZ)
....

If you have date fields, IsDate() will do the same thing.

John W. Vinson [MVP]

 

[Chris L.]

My *guess* is that there is either a Number or a Date field in the input file
which contains invalid characters.

Indeed, the problem was caused by wrong decimal separators in some
records.

Thanks!