Comments by AU Jase (Top 27 by date)

this solution I know, it is automation with interop, its slow and is not thread safe. OLEDB is superior, fast and can do multithreading but at the cost of only <256 columns.

You would think Microsoft with 16384 column support in excel 2007 and later would back it up with their ACE.OLEDB driver also supporting 16k columns, but no, OLEDB is still in the dark ages !


There surely is a way, some suggest convert the .xlsx to .csv and then read, but what do you use to convert >256 .xlsx to csv ?

Also tried Open XML SDK from microsoft, ClosedXML having issues with object not set to reference (null) trying to read .xlsx as XML

Out of ideas !

My workaround was trying to use a SELECT a range, but its not working, so you are saying the SELECT range will not work ?

There are so called "solutions that say to read in 2 seperate parts and re merge the 2 data tables"?

When I select 3 fields, from an excel file with 5-10 columns, SELECT [fieldA],[fieldB],[fieldC] FROM [Sheet1$] it works, but then I use a file >255 columns and try SELECT * FROM [Sheet1$C1:E10] it does not work

I cannot use threading with interop ! I need to use OLEDB

So Dave,

this works : have 2 builds, one x86, the other x64
the setup installer, you add code to tell it which to distribute and you bundle the appropriate Access 2010 OLEDB x86/x64 redistributable ?

Thanks Dave,

Finally on the same understanding, what would be great is a mechanism to hook into the vs config manager during install time and after detection tell it to compile x86 or x64. Is it possible? If it is I need only one binary exe, if can hook on build time even better, does c# have pre processor directives like c/c++ ?

Dave , I know where you are coming from, I was thinking using the setup/installer too but my problem is: scenario : 64bit win7 , using config any CPU , no worries x64, if the machine has 32bit office 2010, you go try to install 64bit access components it will throw an error incompatible/ need to uninstall 32bit components first. If you can figure out office bitness then this hassle will be avoided

could you explain to me if a target machine has 32bit office 2010 meaning they have 32bit OLEDB, and if they are running x64 win7, how it should work and not fail (due to build being x64 due to config manager detecting 64bit OS and the 64bit OLEDB is missing ?)

I need a process to target the OLEDB version and hook into config manager in VS to tell it eg. OLEDB32 found, compile x86, if OLEDB64 found (office 2010 x64) then if 64bit OS ( cant run in x86), leave as is

OLEDB also I am referring as Office 2010 Access Components/Runtime, whatever ACE Driver

So I need to only worry , IF 64BIT OS (IF 32BIT OLEDB, compile X86) else compile X64 (BUILD, forgive me wording)

Dave,

You not listening to me too buddy. If I was the sole user, then I will just install the right pre requisite to make OLEDB work. But eventually I will deploy it to an office environment with mixed Operating Systems on Windows, could be as old as XP, could be vista 32/64 could be win7 32/64, if its 64 bit, there ARE NO GUARANTEES that if you got win7 64, you install office 2010 64, so I am just covering all bases , trying to figure out the multitude of configurations and if there is a universal way to do it. In an ideal 32bit environment no worries it can only run 32bit OLEDB so I just build for 32bit x86, but if its 64bit, 2 scenarios , 32bit oledb or 64, if I complile to config, and it is running 64bit OS, and oledb is 32, I am stuffed, as the config will detect x64 and then build to that.

I think I will need to investigate the setup and deployment option in VS and find how to do scripting or stuff, someone mentioned the Win32API to try and detect using http://stackoverflow.com/questions/495244/how-can-i-test-a-windows-dll-to-determine-if-it-is-32bit-or-64bit

or use the registry.

Please understand my dilemma !

64bit exe trying to access 64bit access oledb where none exists
If I change to x86 build , fixed! But if deployed to another target where 64bit oledb exists it will fail as then build is x86, see my dilemma, no universal solution

Tried you int detection if left on default config it's 64bit on 64bit OS as expected but my office is 32bit so it still fails

I understand that, just how do we know if 32 or 64bit office is installed so the appropriate binary is used, cannot use 64bit exe if 32bit access oledb is on target machine, if x64 OS no guarantees 64bit access oledb is installed !

Just covering all bases

could you probe the installed Office 2010 OLEDB registry entry to see if its 64 or 32 bit and use it as a conditional exe to run either the 32 or 64bit version of your source exe ? If so , how would this be achieved ?

Hi Dave,

I see where you are coming from, but this is the problem I am thinking about:

1. You have 32 bit Ace Excel OLEDB 12.0 driver installed, yet you have a 64bit binary of the program in c# (incompatible) on win 7 x64. so you have a problem
(by virtue of installing 32 instead of 64bit office 2010)

In order to solve this issue you will need the x86 build.

So do you:

In the setup project (installer) configure it to check and then copy the relevant x86 or x64 prebuilt binaries across ?

or do you have a routine in your code to check and tell configuation manager to compile the appopriate build ?

The reason for asking is : x64 build using OLEDB 12.0 will fail if using 32bit installed OLEDB (office 2010) and vice versa

the connection string is generic for either 32/64 bitness, is it decided by the exe build and not the connecting string ? on a 64bit exe, with a 32bit installed drivers, I need to manually change to 32bit to get it to work, and if I was using 64bit drivers with 32bit exe I need to change it to 64bit to get it to work, regardless the connection string stays the same

else I get ISAM, ACE, OLEDB exceptions

some pseudo code:

[list : aa,bb,cc]
[DATA TABLE : COLUMNS aa,bb,cc, etc with rows 0--n]
I want to write list(n) spreadsheeets based on for (0) column AA of datatable,etc
so I do in a loop
WRITE DATA TO EXCEL (FROM LIST) , Create Excel Interop/OLEDB , etc
WRITE TO EXCEL data in datatable based on LIST column criteria
DISPOSE EXCEL INTEROP/OLEDB, etc
then loop

and all the above is in a function call within a backgroundworker thread

i get issues with empty datatable, row in datatable deleted exceptions

so basically Dave,

using OLEDB , SQL to CREATE TABLE (COLUMN HEADINGS) where Table is the worksheet
(creates the worksheet using the oledb connection string)
with a loop on the datatable rows
then using INSERT INTO TABLE (xxx,xxx) with OLEDB
and executing in a loop ?
then the sql non query execute
this can then be moved to a background worker thread ?

Dave,

Basically in a nutshell what I am trying to achieve:

a list of items which are the files for writing to excel files (1...n) meaning n files

in a function I loop using a foreach to write to excel

If I use a backgroundworker thread with profiling turned on, it works !
If I use a backgroundworker thread without profiling it fails.

I appreciate your answer, I just need to clarify:

1. Can I use oleDB to write, and it will be thread safe ?
2. When I manually click an item to save (n) and not in a batch (loop) 1..n, it works in the backgroundworker thread.

Could the Excel object creation and disposing (which I have used) cause these problems ?

so it would be better to use oledb and use INSERT INTO ? would that solve the problem vs Excel COM Interop ?

is there a way to retrieve a specific range in excel (like GetRange("A1")?

what happens if you want to select a range,
to drop a column, drop rows ?