DataSet skips numbers in column

[dpdoug]

I have an app that uploads an excel spreadsheet reads it into a dataset and then uploads the dataset to a sql server table. It works perfect except for one thing.

One column in the spreadsheet has 'Item Numbers' that are all alphnumeric and numeric mixed. Even though the row is formatted as text (and the numbers are stored as text), the DataSet sees the numbers in the column as numbers and not as text, so only the 'Item Numbers' that begin with a character between A and Z go into the dataSet and the numberic values in the column are skipped.

the column looks like this:

ItemNo
----------------------
80977284 (not omitted!)
80977284 (omitted)
180-1260
80660101 (omitted)
80660101 (omitted)
AW-0070
AW1502
AW1261171N
AW900-0093
AW1504
AW180-1542
AW-0046
AW180-1542
AW900-0093
BAKSB22415C
BAKDPP1216
331743 (omitted)
331743 (omitted)
AW-1480
AW-1240
AWC0290814
AWHO271346
516931 (omitted)
80797347
BFIT211840
BFIT20308
287938 (omitted)
287938 (omitted)
80531503 (omitted)
80531503 (omitted)
80846740 (omitted)
BAKSB22855
BAKSB22846

How can I get the DataSet to accept the numbers as String values?

 

[ca8msm]

Could you use CStr to convert them to a String type?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[dpdoug]

I tried that by using a 2 dimension array instead of a dataset so that I could loop through it and convert it but I get the same thing.

It appears that it is the datareader that does it. I changed from a dataadapter to a datareader to see if that would help, but they both do the same thing.

 

[henslecd]

Is the problem getting the data into the dataset or getting the dataset to populate the SQL table correctly? If it is the SQL table then it could be that the column type of that table is numeric and therefore won't allow alpha chars.

 

[dpdoug]

The problem is getting all the data into the dataset. It obviously interprets that column as text and then when it loads the column from Excel to the dataset any string that doesn't have a a-z character in it, just pure numbers, is interpreted as numeric and it doesn't get loaded.

How can I get the dataset to accept the cells of numbers only, as a string of numbers?

 

[henslecd]

Ok I know what the problem is because I had the same problem when I do Excel to SQL 2000. But I got to figure out what I did. I'll get back to you...

 

[henslecd]

Ok go to Data Tab at the top. Select Text to Columns. Choose Delimted and hit Next. Ok here is where I don't remember what I did, but I will give you what I think I did. Check off all of the delimiters and hit Next. Choose Text as the Column Data Format. Hit Finish. Then it should bring everything in. If that doesn't work, I will actually try to DTS a spreadsheet in for you and figure it out.

 

[henslecd]

BTW this is all done in Excel.

 

[henslecd]

Just to clarify, I actually meant to say, uncheck all delimiters.

 

[dpdoug]

That does the exact opposite of what I want. If the numbers are already stored as text, it converts them to numbers stored as numbers.

If the numbers are stored as numbers, I need to make it so that the numbers are stored as text so that when the provider reads them it will recognize them as a string of text and not a numeric value.

If it recognizes them as s a numeric value, it will skip over the number, because it has already decided that the column is text, since it has found text there.