CODEWITHSUNDEEP
c#excel
Rachel Fong
Rachel Fong

Reputation: 720

"Expected )" when trying to open an excel file

I am currently trying to retrieve data from an excel file stored on my server machine. Here's the code.

public void ImportDataFromExcel(string excelPath)
{
  string connString ="server=D0123;uid=abc;pwd=abc@123;database=MYDB";
  string SqlTable = "xx_fields";
  string excelQuery = "Select FieldName,FieldType,Length,Decimal from [Sheet1$]";

  try {
     string excelConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+excelPath+";Extended Properties="+"\"Excel 12.0;HDR=Yes;IMEX=1\"";

     OleDbConnection oleDbconn = new OleDbConnection(excelConnStr);
     oleDbconn.Open(); 
     OleDbCommand oleDbcmd = new OleDbCommand(excelQuery,oleDbconn); 
     OleDbDataReader dr = oleDbcmd.ExecuteReader();
     SqlBulkCopy bulkCopy = new SqlBulkCopy(connString); 
     bulkCopy.DestinationTableName = SqlTable;

     while(dr.Read()) {
       bulkCopy.WriteToServer(dr);
     }

     oleDbconn.Close();
  } catch(Exception e) {
    Response.Write("<script>alert('Error : '" + e.Message + ");</script>");
  }
 }

However, I keep getting an error: "Expected )" every time I am trying to open the excel connection string. I tried changing:

string excelConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+excelPath+";Extended Properties="+"\"Excel 12.0;HDR=Yes;IMEX=1\"";

to 

string excelConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+excelPath+";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""";

My Excel file is formatted as such:

FieldName   FieldType   Length   Decimal
Name         String        10       
Date         Numeric        8        0
Address      String        40 
Age          Numeric        2        0
Price        Numeric        8        2

I still get the same error. What gives? On a last note, this C# code is running on a private engine, so no MS Visual Studio cannot be used or other libraries such as MS Office Interop.

Upvotes: 0

Views: 204

Answers (1)

Tun Zarni Kyaw
Tun Zarni Kyaw

Reputation: 2119

the connection string should be 

string excelConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
excelPath + ";Extended Properties=" + "\"Excel 12.0;HDR=Yes;IMEX=1\"";

UPDATED:

http://www.connectionstrings.com/ace-oledb-12-0/ shows this 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Your excelPath should be physical path on your server. You can use Server.MapPath() to get the physical path. But, for this function excelPath is the parameter, so that, you need to make sure excelPath before calling to this function.

Upvotes: 1

Related Questions