XML Schema datatype

[richard]

Hi,

I am retrieving data from an Excel S'sheet using ADO.Net. I am using a
Schema with that dataset.

One column has a datatype of xs:string in the Schema.

I have formatted the column to be of type Text. However because the fields
in that column on the Ss'sheet can be numeric, Excel sets that cell to be
numeric rather than a Text. When I fill my dataset a NULL value is used
rather than the string version of the number.

Is there a different datatype I can use in my Schema to overcome this or
another way to resolve this?

Cheers,
Richard

 

[scorpion53061]

Don't set the column as numeric in excel.

Set it as "Text" in excel and then try it.

 

[richard]

Yes thats what I did do - but when a field has a value such as 12345 - excel
treats it as a numeric.
Other fields can be of only letters , or a mixture of letters and numbers.
These are fine as they remain as Text and are retrieved into the Dataset
correctly.

 

[David]

Yes thats what I did do - but when a field has a value such as 12345 - excel
treats it as a numeric.
Other fields can be of only letters , or a mixture of letters and numbers.
These are fine as they remain as Text and are retrieved into the Dataset
correctly.

Assuming you're bringing in the sheet using Jet, add IMEX=1 to the
extended properties of your connection string to treat all columns as
text.

"Provider=Microsoft.Jet.OleDB.blah.blah.blah;Extended Properties=\"Excel 10.0;IMEX=1\"";

Basically the extended properties need to be quoted, you do that
slightly differently in different languages.

 

[richard]

Hey Thanks David.....It's worked.

I thought that if I had used - IMEX=1 - the numeric values would not have
come across. But it converts all the fields to text automatically.

Excellent...thanks for your help.