• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1043
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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