OleDbDataAdapter and column casting while importing Excel to ASP.NET

Question

I'm importing an excel using c#. But CustomerOrderNR column contains these kind values:

But when I execute these codes:

    string QTam = @"SELECT Date, [Serial Number], [Status Code], Description, CustomerOrderNR FROM [Sheet1$]";

    DataSet ds = new DataSet();
    OleDbDataAdapter cmd = new OleDbDataAdapter(QTam, strConn);
    cmd.Fill(ds, "orders");

It is returning CustomerOrderNR column in System.Double data type. I want to change datatable column type but then it return exception like:

Cannot change DataType of a column once it has data.

Can't I change DataType of Column from Double to String in SQL statement?

What it the type of the column in Excel? If it's General you may want to explicitly set it to Text... — Vincent Vancalbergh, Mar 17 '11 at 15:56

score 2 · Answer 1 · answered Mar 17 '11 at 16:04

I'm not sure how you can force the datatype in the SQL, but you could chage the datatype afterwards in the dataset by cloning the dataset schema, and copy the data with ImportRow.

But rather you should probably change the datatype in Excel to string if you want to treat it as string.

score 2 · Answer 2 · edited May 23 '17 at 11:54

2

Mixed datatypes in excel are always problematic.

Heres an answer that might help
OleDB & mixed Excel datatypes : missing data

Heres another nice explanation

http://munishbansal.wordpress.com/2009/12/15/importing-data-from-excel-having-mixed-data-types-in-a-column-ssis/

edited May 23 '17 at 11:54

Community

1
1

answered Mar 17 '11 at 16:12

bic

2,201
26
27

score 2 · Answer 3 · answered Mar 17 '11 at 17:34

2

Look at your connection String. You may need to include ISAM=1 so excel knows to handle mixed columns as text.

answered Mar 17 '11 at 17:34

MAW74656

3,449
21
71
118

score 0 · Answer 4 · answered Nov 03 '14 at 09:17

if you have a predetermined list of fields and their expected datatypes, i believe, this should be the most maintainable approach:

create a xml schema as follows:

<?xml version="1.0" encoding="utf-16"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Table">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Field1" type="xs:int" minOccurs="0" />
              <xs:element name="Field2" type="xs:string" minOccurs="0" />
              <xs:element name="Field3" type="xs:date" minOccurs="0" />
              <xs:element name="Field4" type="xs:double" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

and read:

string query = "SELECT Field1, Field2, Field3, Field4 FROM [" + row["TABLE_NAME"].ToString() + "]";

DataSet ds = new DataSet();
ds.ReadXmlSchema(@".\MySchema.xsd");
OleDbDataAdapter data = new OleDbDataAdapter(query, Connection);
data.Fill(ds);
ds.Tables[0].TableName = row["TABLE_NAME"].ToString().Replace("$", string.Empty);`

the catch being that date format has to be valid for the system settings is in short dates as 'dd/mm/yy'

once the type is set, you can format as required.

OleDbDataAdapter and column casting while importing Excel to ASP.NET

4 Answers4