Problem Loading Data from Excel file

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....

Answer 1

User269602965 posted

show us you load script from excel into data table 

Answer 2

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();

Answer 3

User523415940 posted

Is there any solution to my problem?

Answer 4

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$]

 

 

Answer 5

User-1469158370 posted

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