How to read data from excel, convert to datatable or dataset?

Question

1.00/5 (1 vote)

See more:

http://csharpcorner.mindcrackerinc.netdna-cdn.com/forums/uploadfile/50cef0/11092017011035AM/ipmsgclip_s_1510206908_0.png

when i open the excel file above error is occurs, and another error inside the excel opening(Excel was able to open the file by repairing or removing the unreadable content) i click the yes and save as another file and upload the excel.but after the upload many lines are ignored how to fix it.

What I have tried:

i follow some online solutions and apply but not working.
if i copy the data and paste to new excel doc and upload no problem is occurs successfully uploaded.

Posted 8-Nov-17 19:23pm

Raja Ganapathy

Updated 8-Nov-17 19:56pm

Add a Solution

Comments

Sibasisjena 9-Nov-17 1:32am

The excel sheet might have corrupted. Please create a sample excel sheet and try to read that to datatable. Please find the solution given below.

Read-Excel-Sheet-Data-into-DataTable

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pandurang Somwanshi · Answer 1 · 2017-11-08T19:55:00

DataTable dtResult = null;
int totalSheet = 0; //No of sheets on excel file
using(OleDbConnection objConn = new OleDbConnection(@
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
{
objConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where!dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
totalSheet = dt.Rows.Count;
sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "excelData");
dtResult = ds.Tables["excelData"];
objConn.Close();
return dtResult; //Returning Dattable
}