Access 2010 - import insanity

[gauntletxg]

I have a text file containing an inventory listing. It has the following fields: part number, quantity, and cost. The part number field contains values that are both numeric and alphanumeric. I first imported the text file into a blank database, defining the data types during the import step (part - text, quantity - long, cost - double). When I did this none of the records were imported at all due to data type conversion errors.

I then repeated the import step, and this time defined every data type as text. This worked perfectly. However, if I delete all of the records from the table that was just created, and try to import the same text file into that table Access doesn't like it and kicks every single record out due to data type errors. I've never seen this before - importing and creating a new table with every field as text works fine, but importing the same file into the same table that was just created doesn't work at all.

Any one ever come across this before?

 

[bobw111]

Access bases its determination of what the column/field type should be based on the first record it encounters during an import or paste in.

In prior versions once you changed the format and saved the redesigned table, new records imported or pasted in using the re-designed table definition.

In Access 2010 it reverts to the "first record" determination no matter what the table design is.

To me this is definitely a bug.

Established Table design should always take precedent over any built-in rules or, for that matter, any import specs, format files, or templates.

Anything else violates the consistency aspect of ACID.

 

[Bruno Stabile]

Access bases its determination of what the column/field type should be based on the first record it encounters during an import or paste in.

In prior versions once you changed the format and saved the redesigned table, new records imported or pasted in using the re-designed table definition.

In Access 2010 it reverts to the "first record" determination no matter what the table design is.

To me this is definitely a bug.

Established Table design should always take precedent over any built-in rules or, for that matter, any import specs, format files, or templates.

Anything else violates the consistency aspect of ACID.

I've had the same problem. Access would automatically determine data types for itself, despite using an import spec and saving the data into an existing table. I had to resort to have the client add a bogus record to the top of the spreadsheet prior to importing, then have the import process remove the bogus record before performing validation and storing the data in the production tables.

Has anyone found a better solution?

 

[gdesreu]

I've had the same problem. Access would automatically determine data types for itself, despite using an import spec and saving the data into an existing table. I had to resort to have the client add a bogus record to the top of the spreadsheet prior to importing, then have the import process remove the bogus record before performing validation and storing the data in the production tables.

Has anyone found a better solution?

Yeah your solution is the only solution I have found to this most costly time consuming error causing horrific problem brought on by someone thinking they know what you want to do with your data more than you do.
10
10x
imported to a text field will import the 01 but give you an import error and remove the 01x even though your field spec is set to text.
10x
10
imports just fine to the same text field.
This dummy record bandaid is getn old.