0

I have to pull data from an excel file. I was using the below piece of code since last year:

OleDbConnection cnn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + destFile + "; Extended Properties=Excel 12.0;");
string qText = @"select * from [sheetXY$]";
OleDbCommand oconn = new OleDbCommand(qText, cnn);
cnn.Open();
OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
adp.Fill(dt);
cnn.Close();

Since yesterday I get this error message: enter image description here

I had Office 2010 pro on my machine. According to some google suggestions I installed microsoft database access engine, made no difference. I re-installed whole office, made no difference. Then I installed Office 2013, no go.

What could possibly go wrong from a day to another, and how shall I fix it? I'm out of idea.

fishmong3r
  • 1,414
  • 4
  • 24
  • 51
  • 2
    I was never successful reading Excel files in a _reliable_ way using OLEDB. Sometimes filled cells are empty, you have to fiddle with your registry http://stackoverflow.com/questions/4551203/help-with-a-oledb-connection-string-for-excel-files and funny other things can happen. Nowadays I use https://epplus.codeplex.com or Aspose Cells for such tasks. – Andreas Jul 15 '14 at 16:35
  • I had absolutely no issues till yesterday. I will try to find other, but similarly simple solutions. – fishmong3r Jul 15 '14 at 16:43
  • Are you running on a 64-bit operating system and if so are you running the 32-bit or 64-bit copy of Office? If the former, then you need to make sure that you have installed the 32-bit version of the OLE drivers and also make sure your program is compiled for x86 mode – sgmoore Jul 15 '14 at 16:45
  • I'm on a 64-bit Win7 running 32-bit office. – fishmong3r Jul 15 '14 at 16:49
  • Program compiled for x86. – fishmong3r Jul 15 '14 at 16:51
  • Compile it for `Any CPU` or `x64` and then you could use the `Microsoft.ACE.OLEDB.12` but only work for `xlsx` if you want to parse `xls` you would have the same but reverse problem. It's a weird problem on 64bits machines if you try to execute that in a x86 SO it would run just fine – frikinside Jul 18 '14 at 11:16
  • So how did it work for the past half year? I used it literally every day to parse a daily report? I only use it for xlsx. – fishmong3r Jul 18 '14 at 11:25
  • OK, tried to compile for `Any CPU` and `x64` as well, both returns same error. – fishmong3r Jul 18 '14 at 11:30
  • Just for curiosity, can you change the connection to oledb4 as below and check? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destFile + ";Extended Properties='Excel 8.0;HDR=YES;" – Kiran Hegde Jul 18 '14 at 11:41
  • @KiranHegde I tried and got: `Format of the initialization string does not conform to specification starting at index 98.` – fishmong3r Jul 18 '14 at 12:16
  • Is it xlsx file or xls? – Kiran Hegde Jul 18 '14 at 12:18
  • Did you uninstall something yesterday or today? If it worked yesterday clearly that driver was registered on your machine then, and it isn't today. At least not correctly registered. – Lasse V. Karlsen Jul 18 '14 at 13:13
  • There are 2 ADEs, for 64 and 32 bits. Perhaps you installed a wrong one? Also, to open connection with ADE you must use `Microsoft.Jet.OLEDB.4.0` provider and not `ACE`. – Sinatr Jul 18 '14 at 14:47
  • @LasseV.Karlsen I didn't install/uninstall anything. – fishmong3r Jul 18 '14 at 17:11
  • @Sinatr You can't install 32-bit ADE if you have 64-bit office and vice versa. I tried Jet.OLEDB as well, please read the comments. Also please note that the above code was working whether you think I must not use ACE or not. – fishmong3r Jul 18 '14 at 17:13
  • I suggest you to use the [Open XML SDK](http://www.microsoft.com/en-us/download/details.aspx?id=30425) as it is a smooth way for working with Word, Excel and Powerpoint files without needing to install the Microsoft Office Suite. – Martin Braun Jul 21 '14 at 06:08

2 Answers2

7

Save yourself time and headache and use EPPLUS instead. EPPLUS can be installed via Nuget and you don't have to deal with installing the Office Data access on other machine where your code runs.

Dbloch
  • 2,326
  • 1
  • 13
  • 15
  • This sounds great. Though I don't even know what Nuget is. Can you provide link to examples on how EEPLUS should be used to read xlsx files? – fishmong3r Jul 21 '14 at 07:01
  • Never mind. I found a great example. Now I can parse properly. But to be able to use this function EPPLUS.dll has to be in the same directory as my executable. So we have arrived to the question of installtion of it. Can you help me? – fishmong3r Jul 21 '14 at 10:16
  • What development tool are you using Visual Studio? If so, what version? – Dbloch Jul 21 '14 at 12:47
0

You can go for openXML SDK. Which requires no machine level dependency.

http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML

http://msdn.microsoft.com/en-us/library/office/gg575571(v=office.15).aspx

vishnu
  • 13
  • 1
  • 4