Question Type Conversion Failure

[ldprenti]

I am importing a large excel spreadsheet into Access, and i have several fields that are numeric codes, i.e. 2857, 1158, 1011. . .etc. When I imported the spreadsheet, Access interpreted these fields as numbers, but for some reason, some of these entries are periodically import errors due to Type Conversion Failure (about 88,000 of 330,000 rows). I cannot tell why the entries that come up as type conversion failure would be registered as that, as all entries in this field are in the same format. There are even cases where the same code has multiple entries and is registered as type conversion failure in some lines and not in others.
My question is, does it matter that many of these are registering as type conversion failures? From what I can tell, it is not having any impact on the data, and I can't foresee it becoming a problem.

 

[Fuga]

Hi,

well I would be somewhat alarmed by it. If you get importerrors then some data isn´t being imported, which can´t be good, right?

As for the conversion failure, make sure you know what data it is that is causing it, and then check it in excel. I´ve had my fair share of this kind of problem and sometimes excel and access doesn´t work like it should. For instance, there can be a problem importing formatted data. One way to check what the data actually look like is to check it in a text editor.

Fuga

 

[gemma-the-husky]

you sometimes cant tell in excel. a so-called number may be formatted as text eg with a ' at the front. Also an excel number may be out of range for access - so what data type are you importing to (integer or long)

you also say you have over 300,000 rows. So, that isnt just one spreadsheet is it?

Also, maybe its not the integer thats the problem. maybe there is another column with invalid data. if your import table has some constraint that wont accept null values in anouther column, the import of the whole row may be refused because of that.

you need to establish some rows which aren't coming in, and check it carefully. Can you add a sequence number column. that might make it easier to review

Its issues like this, that make using excel less than perfect, although sometimes its unavoidable. A csv is ALWAYS better to use than excel, in my experience, as you get much more control over the import.

 

[Fuga]

Hi again,

I just want to add that in sweden there can be problems with the .csv format because of the langauge settings (decimal sign etc, causing numbers to be interpreted as text and not being imported). They are usually easy to fix, but by then you may have lost some data.

I don´t know where you´re from idprenti, but check carefully what´s happening like Gemma the husky says and you´ll see you´ll figure it out.

Fuga.