SQL Bulk Copy to SQL Server with Data Modifications in Excel

excel sqlbulkcopy sql-server


I'm researchingSQLBulkCopy in order to import thousands of entries from an Excel document into SQL Server. I keep coming across publications that use the data as is, directly. Before doing the action, I must make various adjustments and validations to the data in the Excel sheet.SQLBulkCopy . Is this even a possibility? From the overloads, I would predict that I may alter the data and produce a sizableDataTable and importantly,DataTable with WriteToServer .

1/11/2012 6:33:46 PM

Accepted Answer

Most likely, you want a DataReader or DataSet that you can modify or validate again before importing.

You might benefit from this program - http://exceldatareader.codeplex.com/

1/11/2012 6:36:57 PM

Popular Answer

This is effective for me:

    public ActionResult Create(HttpPostedFileBase file)
        string strConnection = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;

        //file upload path
        var fileName = Path.GetFileName(file.FileName);
        // store the file inside ~/App_Data/uploads folder
        var path = Path.Combine(Server.MapPath("~/App_Data/uploads"), fileName);

        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        DataTable dt = new DataTable();

        dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        if (dt == null)
            return null;

        String[] excelSheets = new String[dt.Rows.Count];
        int t = 0;
        //excel data saves in temp file here.
        foreach (DataRow row in dt.Rows)
            excelSheets[t] = row["TABLE_NAME"].ToString();

        OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);

        string query = string.Format("SELECT * FROM [{0}]", excelSheets[0]);

        OleDbCommand cmd = new OleDbCommand(query, excelConnection);
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "[FSM].[DFS_Akustik]";

        ViewBag.view_dfs_akustik = dbman.View_DFS_Akustik.ToList();
        return View();

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow