I'm bringing in a slew of files containing shipping data. There's a [Billed Weight] field that either returns a number or "LTR" for letter. In the import spec, I've defined the field as Long Integer so the "LTR"'s get wiped. "LTR" is very rare, maybe 3 or 4 show up per file. I go in with an update query and replace the nulls with 0.
As I'm bringing in several files at a time, there are several importError tables created as a result of each import. I'd like to prevent the importError tables from occurring in the first place.
The import takes place regularly, so my rationale was to omit the error reporting and run one query to update the field. The other method would involve two queries - one to change "LTR" to "0", one to change the designation of the field to long integer.
If it's impossible to skip the creation of the import error tables, I will write these two queries.
I don't know of a way to get rid of the import error table creation, but I've faced a similar problem. Keep your primary data table as-is.
Set up a "slush" import table with the billed weight field as text. Create an append query to update your primary data table, converting the text in the "slush" billed weight to number for the primary data table.
Then write a procedure or macro to
1. Import the file(s) to the "slush" table
2. Run the append query
3. Delete the records from the "slush" table