• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1088
  • Last Modified:

How to save Excel data to DataTable in c#?

Hi, I need to save data from excel file to Data base.
So first I need to save from Excel file to DataTable and then after manipulation finally save to Data base.
File name is DatabaseList.xlsx and saved on E drive.
Now please provide C# code to import data from Excel file to datatable.
0
Minesh Shah
Asked:
Minesh Shah
9 Solutions
 
dj_alikCommented:
Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory
http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

0
 
Kalpesh ChhatralaSoftware ConsultantCommented:
Sample Procedure

private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                     FileName + ";Extended Properties=\"Excel 8.0;HDR=" + 
                     HDR + ";IMEX=1\"";

    DataSet output = new DataSet();

    using (OleDbConnection conn = new OleDbConnection(strConn)) {
        conn.Open();

        DataTable schemaTable = conn.GetOleDbSchemaTable(
          OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

        foreach (DataRow schemaRow in schemaTable.Rows) {
            string sheet = schemaRow["TABLE_NAME"].ToString();

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
            cmd.CommandType = CommandType.Text;

            DataTable outputTable = new DataTable(sheet);
            output.Tables.Add(outputTable);
            new OleDbDataAdapter(cmd).Fill(outputTable);
        }
    }
    return output;
}

Open in new window

0
 
mayank_joshiCommented:
this code will import the excel to datatable:-
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C:\\Book2.xls;"  // Change the path here
DataTable dt = new DataTable();
//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
da.Fill(dt);

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Kalpesh ChhatralaSoftware ConsultantCommented:
Change Connection String as per your Office Version
below string for Access 2007
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

Open in new window


private static DataSet ImportExcelXLS(string FileName, bool hasHeaders)
{
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                     FileName + ";Extended Properties=\"Excel 8.0;HDR=" + 
                     HDR + ";IMEX=1\"";

    DataSet output = new DataSet();

    using (OleDbConnection conn = new OleDbConnection(strConn)) {
        conn.Open();

        DataTable schemaTable = conn.GetOleDbSchemaTable(
          OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

        foreach (DataRow schemaRow in schemaTable.Rows) {
            string sheet = schemaRow["TABLE_NAME"].ToString();

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
            cmd.CommandType = CommandType.Text;

            DataTable outputTable = new DataTable(sheet);
            output.Tables.Add(outputTable);
            new OleDbDataAdapter(cmd).Fill(outputTable);
        }
    }
    return output;
}

Open in new window



0
 
mayank_joshiCommented:
the above code is for xls(excel 2003 and before) file.
for xlsx(excel 2007) you will have to use:-


String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\DatabaseList.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"; 

DataTable dt = new DataTable();
//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
da.Fill(dt);

Open in new window

0
 
mayank_joshiCommented:
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
0
 
mayank_joshiCommented:
you may change Sheet1 to the  name of the sheet you are using.
0
 
kumarnimavatCommented:
You may use the link given below to import excel file to sql database using c#.net!

http://forums.asp.net/t/1336761.aspx/1?Import+Excel+File+into+SQL+server+2005+in+ASP+NET+using+c+
0
 
mayank_joshiCommented:
sorry there were some errors in my code.i've corrected it:-

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\DatabaseList.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
DataTable dt = new DataTable();
//You must use the $ after the object you reference in the spreadsheet
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
da.Fill(dt);

Open in new window


"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

you may change Sheet1 to the  name of the sheet you are using.
0
 
Minesh ShahSharePoint & all about itAuthor Commented:
Thanks, its working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now