Problem related Excel file connection string

[NK sharma]

Hello everyone,

I am importing excel file using oledb. I am using following connection string but it is giving me an error saying that" External file is not in expected format"

my conection sting is as folow.

if(firstRowIsTitle)
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
else
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";


i tried following con. strings as well


strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + filePath + ";"+"Extended Properties=\"Excel 8.0;HDR=YES\"";
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;"+"Data Source="+ filePath +";"+"Extended Properties=\"Excel 8.0;HDR=YES\"";


But it was the same.

Please help me

my file is 97-2003 xls file but it was working for other xls files.

string strConn;
if(firstRowIsTitle)
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";
else
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text\"";

// strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + filePath + ";"+"Extended Properties=\"Excel 8.0;HDR=YES\"";
//strConn = "Provider=Microsoft.ACE.OLEDB.12.0;"+"Data Source="+ filePath +";"+"Extended Properties=\"Excel 8.0;HDR=YES\"";
using (OleDbConnection oleDB = new OleDbConnection(strConn))
{

DataSet DS = new DataSet();
string[] items = new string[10];

oleDB.Open();// Error is while opening oledb

// Get the name of the first worksheet:
DataTable dbSchema = oleDB.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception ("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows [0] ["TABLE_NAME"].ToString ();

// Now we have the table name; proceed as before:
OleDbCommand dbCommand = new OleDbCommand ("SELECT * FROM [" + firstSheetName + "]", oleDB);

//OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Feuille1$]", oleDB);

DataTable dataTable = new DataTable();
OleDbDataReader reader = dbCommand.ExecuteReader();


Please help me


Thank you

NK

Continue reading...