Importing from Excel to SQL Server c#

Question

I am trying to import data from an Excel file to a table in SQL Server but it is throwing errors.

            string ssqltable = "mytable";

        string myexceldataquery = "select * from [Order Form$]";
        try
        {
            string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;Persist Security Info=False;data source='C:\Users\usiddiqui\Desktop\myexcel.xlsx';extended properties=" + "\'excel 12.0;hdr=yes;\';";
            string ssqlconnectionstring = "server=SANJSQL-DEV;Database=db; User Id=user; Password=pass;";

            string sclearsql = "delete from " + ssqltable;
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
            bulkcopy.DestinationTableName = ssqltable;
            bulkcopy.BatchSize = 100;
            bulkcopy.WriteToServer(dr);
            //while (dr.Read())
            //{
            //    bulkcopy.WriteToServer(dr);
            //}
            dr.Close();
            oledbconn.Close();
            label1.Text = "File imported into sql server."; 
        }
        catch (Exception ex)
        {
            //handle exception 
        }

the error comes on oledbconn.Open(); this is the error

Could not find installable ISAM.

The Excel connection string misses the initial double quote _@"....;Extended Properties=\"....\"" — Steve, Feb 10 '16 at 16:11
I changed my excel connection string to string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=C:\\Users\\usiddiqui\\Desktop\\myexcel.xlsx;extended properties==" + "\"excel 12.0;hdr=yes;\""; but still same error — Umm E Habiba Siddiqui, Feb 10 '16 at 16:16
Check out this question: http://stackoverflow.com/questions/8243008/format-of-the-initialization-string-does-not-conform-to-specification-starting-a . Your connection string is incorrect somehow. — Tophandour, Feb 10 '16 at 16:49
my sql string is fine because before OLEDBConnection I am deleting data from sql table which is executing fine, I checked all data was deleted from table. — Umm E Habiba Siddiqui, Feb 10 '16 at 16:58
nothing to do with the error, but I'm pretty sure that your "while" loop is going to cause your import to skip the first row. take out the "while" and just call bulkcopy.WriteToServer(dr). — Bruce Dunwiddie, Feb 10 '16 at 17:02
Why don't you use SSIS for this? It will be a *lot* faster. In any case, instead of using OLEDB to read the file as if it were a database, use a .NET library like EPPLus. To use OLEDB you need to have the proper driver installed, one that actually matches the bitnes of your executable (ie x86 or x64). — Panagiotis Kanavos, Feb 10 '16 at 17:19

score 0 · Answer 1 · answered Feb 10 '16 at 17:05

You're intermixing escape syntaxes. You're using @ to switch into multiline, but then you're using \ to escape. Don't escape the \'s and remove the final \ .

string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=C:\Users\usiddiqui\Desktop\myexcel.xlsx;extended properties=excel 12.0;hdr=yes;";

Importing from Excel to SQL Server c#

1 Answers1