OleDbDataAdapter and column casting while importing Excel to ASP.NET

11,924

Solution 1

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

Solution 2

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.

Solution 3

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/

Share:
11,924
uzay95
Author by

uzay95

Love this game (walking, talking as a programmer) :)

Updated on June 05, 2022

Comments

  • uzay95
    uzay95 over 1 year

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

    20283
    20213
    20625
    50749-50
    30687
    31975
    82253
    

    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?