Advanced OLEDB Select for Excel Sheet

Trying to perfrom a select statement with a WHERE clause

Page 1 of 1

4 Replies - 75024 Views - Last Post: 02 August 2010 - 04:48 AM Rate Topic: -----

#1 someone1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 28-April 08

Advanced OLEDB Select for Excel Sheet

Posted 28 April 2008 - 11:18 AM

I'm trying to load in an XLS sheet and filter the results. I have a DeletedFlag column and want to only select those rows in which this column is set to "False"

here is my code:

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
		{
			DataTable dtXLS = new DataTable("XLS");
			try
			{
				string strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
				OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
				SQLConn.Open();
				OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
				OleDbCommand selectCMD = new OleDbCommand("SELECT * FROM [" + sheetName + "$] WHERE " + column + " = @" + column, SQLConn);
				selectCMD.Parameters.Add("@" + column, OleDbType.VarChar).Value = value;
				SQLAdapter.SelectCommand = selectCMD;
				
				SQLAdapter.Fill(dtXLS);
				SQLConn.Close();
			}
			catch (Exception e)
			{
				Console.WriteLine(e.ToString());
			}

			return dtXLS;
		}



It keeps breaking when trying to Fill by dataTable:
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Commandbehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Commandbehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Commandbehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(Commandbehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, Commandbehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, Commandbehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Nationwide_QUpdate.NWQUpdate.LoadXLS(String strFile, String sheetName, String column, String value) in C:\...\NWQUpdate.cs:line 440

I'm not sure what is wrong! Please help.

Is This A Good Question/Topic? 0
  • +

Replies To: Advanced OLEDB Select for Excel Sheet

#2 naiairex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 25-August 08

Re: Advanced OLEDB Select for Excel Sheet

Posted 25 August 2008 - 11:26 AM

I got the same excpetion when I try to read Excel 4.0 format files with these code.
			...
			System.Data.DataTable dbSchema = new System.Data.DataTable();
			OleDbConnection conn;
			OleDbDataAdapter da;
			DataSet ds;

			string sfile = "C:\\a.xls";
			string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sfile + ";" + "Extended Properties=Excel 8.0;";

			conn = new OleDbConnection(strConn);
			conn.Open();
			// Get all sheetnames from an excel file into data table
			dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

			if (dbSchema != null || dbSchema.Rows.Count > 0)
			{
				// Loop through all worksheets
				for (int i = 0; i < dbSchema.Rows.Count; i++)
				{
					string sheetname = dbSchema.Rows[i]["TABLE_NAME"].ToString();

					da = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "]", strConn);
					da.TableMappings.Add("Table", "dt_Excel");

					ds = new DataSet();
				  [color=#FF0000] da.Fill(ds);[/color]
				   ....
				}
			}
			conn.Close();

		}



Regards



View Postsomeone1, on 28 Apr, 2008 - 11:18 AM, said:

I'm trying to load in an XLS sheet and filter the results. I have a DeletedFlag column and want to only select those rows in which this column is set to "False"

here is my code:

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
		{
			DataTable dtXLS = new DataTable("XLS");
			try
			{
				string strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
				OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
				SQLConn.Open();
				OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
				OleDbCommand selectCMD = new OleDbCommand("SELECT * FROM [" + sheetName + "$] WHERE " + column + " = @" + column, SQLConn);
				selectCMD.Parameters.Add("@" + column, OleDbType.VarChar).Value = value;
				SQLAdapter.SelectCommand = selectCMD;
				
				SQLAdapter.Fill(dtXLS);
				SQLConn.Close();
			}
			catch (Exception e)
			{
				Console.WriteLine(e.ToString());
			}

			return dtXLS;
		}



It keeps breaking when trying to Fill by dataTable:
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Commandbehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(Commandbehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(Commandbehavior behavior)
at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(Commandbehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, Commandbehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, Commandbehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Nationwide_QUpdate.NWQUpdate.LoadXLS(String strFile, String sheetName, String column, String value) in C:\...\NWQUpdate.cs:line 440

I'm not sure what is wrong! Please help.

Was This Post Helpful? 0
  • +
  • -

#3 someone1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 7
  • Joined: 28-April 08

Re: Advanced OLEDB Select for Excel Sheet

Posted 25 August 2008 - 11:52 AM

Hey,

Its been a while but i got this to work:

private DataTable LoadXLS(string strFile, String sheetName, String column, String value)
		{
			DataTable dtXLS = new DataTable(sheetName);
			try
			{
				string strConnectionString = "";
				if(strFile.Trim().EndsWith(".xlsx")) {
					strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
				} else if(strFile.Trim().EndsWith(".xls")) {
					strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
				}
				OleDbConnection SQLConn = new OleDbConnection(strConnectionString);
				SQLConn.Open();
				OleDbDataAdapter SQLAdapter = new OleDbDataAdapter();
				string sql = "SELECT * FROM [" + sheetName + "$] WHERE " + column + " = " + value;
				OleDbCommand selectCMD = new OleDbCommand(sql, SQLConn);
				SQLAdapter.SelectCommand = selectCMD;
				
				SQLAdapter.Fill(dtXLS);
				SQLConn.Close();
			}
			catch (Exception e)
			{
				Console.WriteLine(e.ToString());
			}

			return dtXLS;
		}


Was This Post Helpful? 1

#4 bluebear1608  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 82
  • Joined: 03-June 08

Re: Advanced OLEDB Select for Excel Sheet

Posted 30 July 2010 - 05:52 AM

i know this post is a while ago, but for anyone reading, it would probably be better to use
if(Path.GetExtension(FileName).ToLower() == ".xlsx") 
{
    strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", FileName);
}
else if (Path.GetExtension(FileName).ToLower() == ".xls") 
{
    strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", FileName);
}



instead of

if(strFile.Trim().EndsWith(".<span class="searchlite">xlsx</span>")) 
{
   strConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
} 
else if(strFile.Trim().EndsWith(".xls")) 
{
   strConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);
}


Was This Post Helpful? 0
  • +
  • -

#5 bluebear1608  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 82
  • Joined: 03-June 08

Re: Advanced OLEDB Select for Excel Sheet

Posted 02 August 2010 - 04:48 AM

not quite sure why its showing up as

Path.GetExtension(FileName).ToLower() == ".<SPAN class=searchlite>xlsx</SPAN>")

but its suppose to be

Path.GetExtension(FileName).ToLower() == ".xlsx")
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1