Answered by:
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....
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