locked
Problem Loading Data from Excel file RRS feed

  • Question

  • User523415940 posted

    hi all

    i am loading Data From one my Excel file to DataTable. My Excel file has data in Following Format:

    Vendor ID Invoice Number Invoice Date  Entity Invoice Amount
    Remarks GL Account
    5586   e5500222117 5/14/10  Entiti1                       3.000
    some 210000
    5586   5500231216 6/11/10  Entiti1                       4.000
    hello 210000
    5586
      5500250230 7/28/10  Entiti1                  681.905
    what 210000


    Problem is that when i load this data to DataTable, my DataTable shows nothing for InvoiceNumber e5500222117. it shows null instead of this value. although i have changed the format for my Cells which are comming under "Invoice Number"column to "Text" but still i am not getting any thing. rest of the two values (i.e. 5500231216 and 5500250230 ) it shows properly.


    Any suggestions....

    Tuesday, November 23, 2010 3:16 AM

Answers

  • User269602965 posted

    Thanks for the code

    I would not use SELECT * because you are leaving datatyping up to the data provider, Excel, etc.

    Excel at times does some strange things to numbers that should be text, etc.

    Try this to force your text datatype on InvoiceNumber column at the SQL level as you populate the datagrid.

    SELECT VendorID, CAST(InvoiceNumber AS VARCHAR(16)) AS InvoiceNumber, InvoiceDate, Entity, InvoiceAmount, Remarks, GLAccount FROM [Sheet 1$]

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 25, 2010 1:57 PM

All replies

  • User269602965 posted

    show us you load script from excel into data table 

    Tuesday, November 23, 2010 1:32 PM
  • User523415940 posted

    below is the code which i am using for loading data from Excel to DataTable


    DataSet myDataSet = new DataSet();
                OleDbCommand command = new OleDbCommand();
                OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "\\OSClientInvoices_UploadAR.xls" + ";Extended Properties=Excel 8.0");

                con.Open();
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("select * from [Sheet 1$]", con);
                System.Data.DataTable myDataTable = new System.Data.DataTable("myDataTable");
                myDataAdapter.Fill(myDataTable);
                myDataTable.Rows.Remove(myDataTable.Rows[0]);

                myDataSet.Tables.Add(myDataTable);
                con.Close();


    Wednesday, November 24, 2010 12:33 AM
  • User523415940 posted

    Is there any solution to my problem?

    Thursday, November 25, 2010 11:44 AM
  • User269602965 posted

    Thanks for the code

    I would not use SELECT * because you are leaving datatyping up to the data provider, Excel, etc.

    Excel at times does some strange things to numbers that should be text, etc.

    Try this to force your text datatype on InvoiceNumber column at the SQL level as you populate the datagrid.

    SELECT VendorID, CAST(InvoiceNumber AS VARCHAR(16)) AS InvoiceNumber, InvoiceDate, Entity, InvoiceAmount, Remarks, GLAccount FROM [Sheet 1$]

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 25, 2010 1:57 PM
  • User-1469158370 posted

    I would suggest to use ExcelDataReader available on codeplex.com. It will help you write easy bug free code.

    Thursday, November 25, 2010 2:04 PM