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?

 

[xenou]

1)
When you say you upload to the same table, are you leaving the data types as text? Or trying to change them back to Text, Long, Double?

2)
Are you creating an import specification? And saving it?

 

[gauntletxg]

I'm leaving everything defined as text.

I've been able to create import specs as a workaround, but I don't understand why everything is bombing out when importing into text fields. It doesn't happen in 2003 (not sure about 2007)

 

[xenou]

Ok. To me an import specification to be a requirement rather than a workaround - all the more so if a column has text and numbers in it (alphanumeric). I don't believe I run any import from text without them, unless perhaps it is a one off thing I won't be repeating.

 

[Norie]

Has Access kept all the fields as text?

Maybe it's doing it's check, if it can't determine the data type it resorts to what you've specified, ie text, but if it can it overrides that.

 

[gauntletxg]

Ok. To me an import specification to be a requirement rather than a workaround - all the more so if a column has text and numbers in it (alphanumeric). I don't believe I run any import from text without them, unless perhaps it is a one off thing I won't be repeating.

I don't think it should be a requirement, I'm just trying to import a field with both numbers and text into a text field and Access isn't liking it.

I think the problem is similar to what Norie described, Access trying to override the data type. When I create a new blank table and define the fields there, as opposed to creating a table through the import process and subsequently changing the data types, there are no problems. I wonder if this is some new 2007/2010 feature that I'm not used to, I'll need to play around some more.

 

[Norie]

It's not new as far as I know, Access has always been a bit unpredictable with importing.

I think using xenou's idea of using an import specification is a great one, especially if this is going to be a regular import.

 

[boblarson]

I don't think it should be a requirement, I'm just trying to import a field with both numbers and text into a text field and Access isn't liking it.

That is PRECISELY what an import specification is about - letting Access know what it should be using. Otherwise it has to guess and it isn't all that easy to guess sometimes. So, I echo the statement that an import spec is required (or going through the process but not saving the spec but using the interface to specify the field data types is crucial).

 

[boblarson]

I just noticed that you said you did change the datatypes and all, but that you wound up with import errors. So, I can't tell you exactly, without seeing the actual data you were trying to import, but now that I'm thinking about it more - this may actually be a bug in 2010 as it seems familiar. I just can't remember for sure.

 

[gauntletxg]

Correct, I am telling Access that it is a text field but it still kicks out every record with a data type conversion error. I am going to work on some more tomorrow when I am back at work, I think the issue is being caused by some combination of importing the same file and/or whether the table was created manually or through an import.

And this will not be a regular import, but I do similar type of imports all the time. We do a lot of one-off stuff at work in Access, so unfortunately it's not feasible to create import specs for all of them.