Data Import conversion errors

[RECrerar]

I'm trying to import data from Excel into and existing Access table, one column has a series of values, most of these are numbers such as 831, 2123 etc, however some are a mixture of numbers and letter such as 61C.

I have formatted all the cells in this column to be text and yest I am still getting data type conversion errors on any value that is not purely numeric. DOes anyone know why and how I can get round this?

 

[ajetrumpet]

The errors are probably occurring because of the data type defined already by Access in the existing table. Check that first...

 

[RECrerar]

The data type defined by Access is text. I think it is because when importing data Access looks at the first 10 rows to determin the data type. In my case, none of the values that have letter in them occur in the first 10 rows and hence Access is seeing them as numeric even though I have formatted the cells to be text.

I plan to get round this by first sorting the excel data to ensure that any non-numeric entries are at the top (the order of the data is not important in the database).

I believe there is a way to force Access to import things as text, can this be done for specific columns or only for the whole spreadsheet as there are columns that i require as numeric and data and hence would not want these imported as text?

If not, I will go ahead with my sorting method and hopefully that will solve the issue

 

[ajetrumpet]

I think it is because when importing data Access looks at the first 10 rows to determin the data type.

That is exactly right, but you said you were importing the data into a previously-created table, which changes the rule (I think).

I believe there is a way to force Access to import things as text, can this be done for specific columns or only for the whole spreadsheet as there are columns that i require as numeric and data and hence would not want these imported as text?

I am not sure. You may try changing the data in Excel that you want to be text. Preceed the data in "text" cells with an apostrophe. That forces text in Excel, and it might in Access, but I've never done it, so you'll have to try.