Query an Excel Spreadsheet
-
Good Morning, I am trying to extract a column of data from an Excel spreadsheet and pass it to a list box. I thought this was straight forward and I have found several very similar code samples through Google and Code Project search, but I cannot get any to work. I have created several variations of each with my own syntax (which is usually simpler) but I keep getting this error: ERROR: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." I have tried adding a piece of code that I found to make sure the process is not running (just guessing here...see Sample 1), but I do not think that it works either. Below are 2 different code snippets that produce the same error. I would appreciate it these can be corrected of if a new code sample can be demonstrated to cure this issue ot produce the desired result. Sample 1
private DataTable GetExcel(string fileName, string sheetName)
{
DataTable dt = new DataTable(sheetName);try { string ConStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\\"Excel 8.0;HDR=Yes;IMEX=1\\";", fileName); string Sql = "SELECT \* FROM \[" + sheetName + "$\]"; OleDbConnection Conn = new OleDbConnection(ConStr); OleDbCommand Comm = new OleDbCommand(Sql, Conn); OleDbDataAdapter da = new OleDbDataAdapter(Comm); Process\[\] process; process = System.Diagnostics.Process.GetProcessesByName("Excel"); if (process.Length > 0) { process\[0\].Kill(); } Conn.Open(); da.Fill(dt); Conn.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } return dt; }Sample 2
private void GetExcelInfo() { string theFile = textBoxExcelFile.Text; string ConStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + theFile + ";Extended Properties=Excel 8.0"; string sheetName = "Sheet1"; // Create the connection object OleDbConnection Conn = new OleDbConnection(ConStr); try { // Open connection Conn.Open();Do you have the file open in Excel?
-
Do you have the file open in Excel?
No. The app uses the openFileDialog to create the path and put it in a textbox so I can transfer it to the connection string. It looks like so: C:\Users\Pat\Documents\Visual Studio 2010\Projects\Perfect 2012\Perfect 2012\bin\Debug\Filename.xls Also, I ran the following code to determine all the running processes at the time of the call:
ArrayList allProcesses = new ArrayList();
foreach(Process p in processlist)
{
allProcesses.Add(p);
}No Excel processes were revealed. I am baffled. Thanks for your question...Pat
-
Good Morning, I am trying to extract a column of data from an Excel spreadsheet and pass it to a list box. I thought this was straight forward and I have found several very similar code samples through Google and Code Project search, but I cannot get any to work. I have created several variations of each with my own syntax (which is usually simpler) but I keep getting this error: ERROR: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." I have tried adding a piece of code that I found to make sure the process is not running (just guessing here...see Sample 1), but I do not think that it works either. Below are 2 different code snippets that produce the same error. I would appreciate it these can be corrected of if a new code sample can be demonstrated to cure this issue ot produce the desired result. Sample 1
private DataTable GetExcel(string fileName, string sheetName)
{
DataTable dt = new DataTable(sheetName);try { string ConStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\\"Excel 8.0;HDR=Yes;IMEX=1\\";", fileName); string Sql = "SELECT \* FROM \[" + sheetName + "$\]"; OleDbConnection Conn = new OleDbConnection(ConStr); OleDbCommand Comm = new OleDbCommand(Sql, Conn); OleDbDataAdapter da = new OleDbDataAdapter(Comm); Process\[\] process; process = System.Diagnostics.Process.GetProcessesByName("Excel"); if (process.Length > 0) { process\[0\].Kill(); } Conn.Open(); da.Fill(dt); Conn.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } return dt; }Sample 2
private void GetExcelInfo() { string theFile = textBoxExcelFile.Text; string ConStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + theFile + ";Extended Properties=Excel 8.0"; string sheetName = "Sheet1"; // Create the connection object OleDbConnection Conn = new OleDbConnection(ConStr); try { // Open connection Conn.Open();Is you're project being targeting "AnyCPU" and is your version of Windows 64-bit?? If so, then you can't use the Jet 4.0 provider. It's a 32-bit only piece of code and you can't mix 32- and 6-4bit code in the same process. Try setting your compile options to target x86 only.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
Is you're project being targeting "AnyCPU" and is your version of Windows 64-bit?? If so, then you can't use the Jet 4.0 provider. It's a 32-bit only piece of code and you can't mix 32- and 6-4bit code in the same process. Try setting your compile options to target x86 only.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
No. The app uses the openFileDialog to create the path and put it in a textbox so I can transfer it to the connection string. It looks like so: C:\Users\Pat\Documents\Visual Studio 2010\Projects\Perfect 2012\Perfect 2012\bin\Debug\Filename.xls Also, I ran the following code to determine all the running processes at the time of the call:
ArrayList allProcesses = new ArrayList();
foreach(Process p in processlist)
{
allProcesses.Add(p);
}No Excel processes were revealed. I am baffled. Thanks for your question...Pat
How about the SPACEs in the path?
-
Good Morning, I am trying to extract a column of data from an Excel spreadsheet and pass it to a list box. I thought this was straight forward and I have found several very similar code samples through Google and Code Project search, but I cannot get any to work. I have created several variations of each with my own syntax (which is usually simpler) but I keep getting this error: ERROR: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." I have tried adding a piece of code that I found to make sure the process is not running (just guessing here...see Sample 1), but I do not think that it works either. Below are 2 different code snippets that produce the same error. I would appreciate it these can be corrected of if a new code sample can be demonstrated to cure this issue ot produce the desired result. Sample 1
private DataTable GetExcel(string fileName, string sheetName)
{
DataTable dt = new DataTable(sheetName);try { string ConStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\\"Excel 8.0;HDR=Yes;IMEX=1\\";", fileName); string Sql = "SELECT \* FROM \[" + sheetName + "$\]"; OleDbConnection Conn = new OleDbConnection(ConStr); OleDbCommand Comm = new OleDbCommand(Sql, Conn); OleDbDataAdapter da = new OleDbDataAdapter(Comm); Process\[\] process; process = System.Diagnostics.Process.GetProcessesByName("Excel"); if (process.Length > 0) { process\[0\].Kill(); } Conn.Open(); da.Fill(dt); Conn.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } return dt; }Sample 2
private void GetExcelInfo() { string theFile = textBoxExcelFile.Text; string ConStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + theFile + ";Extended Properties=Excel 8.0"; string sheetName = "Sheet1"; // Create the connection object OleDbConnection Conn = new OleDbConnection(ConStr); try { // Open connection Conn.Open();Try removing the
conn.Open();line. When a data adapter fills, it open the associated connection, fills the DataSet, then closes the associated connection automatically. <-- Edit: nevermind, just tried your code and it works fine. You must have something holding onto that file (or permissions, password, etc.) that prevents it from opening.Jack of all trades ~ Master of none.
-
Try removing the
conn.Open();line. When a data adapter fills, it open the associated connection, fills the DataSet, then closes the associated connection automatically. <-- Edit: nevermind, just tried your code and it works fine. You must have something holding onto that file (or permissions, password, etc.) that prevents it from opening.Jack of all trades ~ Master of none.
Hello..Thank you for your input. I agree about the connection, but this was a copied (supposedly already working code), so I left it. I removed the connection commands as suggested, but the result is the same. Let me now if you have any other thoughts...Best, Pat OK, I just saw the edit...hmmm, what can I say. I'll keep looking, perhaps try a reboot. If I find the answer, I will post it. If not, I'll have to try a different way to get to the intended result....P.
-
Hello..Thank you for your input. I agree about the connection, but this was a copied (supposedly already working code), so I left it. I removed the connection commands as suggested, but the result is the same. Let me now if you have any other thoughts...Best, Pat OK, I just saw the edit...hmmm, what can I say. I'll keep looking, perhaps try a reboot. If I find the answer, I will post it. If not, I'll have to try a different way to get to the intended result....P.
-
The app doesn't create any Streams that use the file does it?
Jack of all trades ~ Master of none.
OOOOKKKK..........JOAT-MON YES...you are absolutely right. There was a Streamwriter object that was called when the file was imported. I normally enclose them in a using statement so that they will close automatically, but for some reason I did not do it this time. I have made the correction and it is working perfectly now. Thank you very much for your insight. I am marking your answer as THE answer. Kudows and Best Regards, Pat
-
OOOOKKKK..........JOAT-MON YES...you are absolutely right. There was a Streamwriter object that was called when the file was imported. I normally enclose them in a using statement so that they will close automatically, but for some reason I did not do it this time. I have made the correction and it is working perfectly now. Thank you very much for your insight. I am marking your answer as THE answer. Kudows and Best Regards, Pat