CODEWITHSUNDEEP
asp.netexcel
teledextri
teledextri

Reputation: 125

Import a Excel sheet into MS SQL database

I have Excel 2007 and Visual Web Developer Express 2010. I would like to import Sheet1 of an xlsx file the reader then add the data to a dataset and placing that data in a MS SQL database.

    string ExcelConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";
    string SQLConStr = "got connection string that works";

    OleDbConnection ExcelConnection = new OleDbConnection(ExcelConStr);
    using (ExcelConnection)
    {
        string sql = string.Format("Select * FROM [{0}]", "Sheet1$");
        OleDbCommand command = new OleDbCommand(sql, ExcelConnection);
        ExcelConnection.Open();
        using (OleDbDataReader dr = command.ExecuteReader())
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SQLConStr))
            {
                bulkCopy.DestinationTableName = "dbo.databaseName";            
                bulkCopy.WriteToServer(dr);
            }
        }
    }

I need something like bulkcopy that is free and easy to use if someone may make a recommendation.

Upvotes: 1

Views: 420

Answers (1)

Shirish
Shirish

Reputation: 1250

Without bulk copy it will also work.. try this it will work 100% for .csv and .xlsx both... I m using it.. 

    string header = "No";
    string sql = string.Empty;
    DataTable dt = new DataTable();
    string pathOnly = string.Empty;
    string fileName = string.Empty;

    pathOnly = Path.GetDirectoryName(path);
    fileName = Path.GetFileName(path);

    if (IsFirstRowHeader) { header = "Yes"; }     

String cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=" + header + ";IMEX=1;\""; 

  OleDbConnection con = new OleDbConnection(cs);
  if (con.State == ConnectionState.Closed) con.Open();

        #region use to find sheet and fire query on sheetname
        DataTable dtsheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        String[] excelSheets = new String[dtsheets.Rows.Count];
        int i = 0;
        // Add the sheet name to the string array.
        foreach (DataRow row in dtsheets.Rows)
        {
            excelSheets[i] = row["TABLE_NAME"].ToString();
            i++;
        }
        if (extension == ".csv") sql = "SELECT  * FROM [" + fileName + "]";
        else sql = "SELECT  * FROM [" + excelSheets[0] + "]";
        #endregion

  OleDbCommand command = new OleDbCommand(sql, con);
  OleDbDataAdapter adapter = new OleDbDataAdapter(command);
  dt.Locale = CultureInfo.CurrentCulture;
  adapter.Fill(dt);
  con.Close();

Upvotes: 7

Related Questions