Excel import with x64 machine

[DC]

Hi,

I easily ported a .Net 2.0 c# app to x64, but there is one major
problem left: the app imports excel data via OleDB, and there are no
Jet drivers and no ODBC divers (other than SQL Server) available for
64 bit apps on the x64 platform.

I have x64, Sql Server (must support 2000 and 2005) and Excel sheets
(must be able to import mixed mode data), can someone point me into a
direction to get this solved without too much trouble?

TIA for any help,

Regards
DC

 

[Nicholas Paldino [.NET/C# MVP]]

DC,

Have you thought about running your app in 32 bit compatability mode?
This might be your only option.

 

[Mark Rae]

Have you thought about running your app in 32 bit compatability mode? This
might be your only option.

I had a similar problem recently with Jet rather than Excel, but the
principle is the same...

As I understand it, there are no 64-bit OleDb drivers for anything other
than SQL Server - therefore, it's not possible to write 64-bit apps which
interface with databases in this way...

In the end I had no choice but to force the app to compile in 32-bit after
which, of course, everything was fine...

 

[DC]

Thanks for the hints and sorry for an additional stupid question (I
don't know much about x64 architecture...): I guess when the app is
run in 32 Bit mode, it will also behave like it was run under Win32
and not address more than 2GB? That is no option then, since the
ability to use huge amounts of RAM is why the app was ported to x64 in
the first place.

I could not have been that hard to at least "bridge" the 64 Bit OleDB
Drivers to the 32 Bit OleDB Drivers (or ODBC drivers). This makes me
wonder how much dedication MS spents to x64 systems.

I will look for other solutions like write a litte 32 Bit Program that
only executes the Jet Imports (and communicates with the 64 Bit
somehow and I don't know how other than exchanging files) or have SQL
Server import the Excel data to a temp table, but it is a pity that
this is necessary. My app is a windows service, I wonder how deep the
porting issues would possibly be if I had to work with graphics and
printer drivers.

Regards
DC

 

[Mark Rae]

Thanks for the hints and sorry for an additional stupid question (I
don't know much about x64 architecture...): I guess when the app is
run in 32 Bit mode, it will also behave like it was run under Win32
and not address more than 2GB?

I believe so...

I could not have been that hard to at least "bridge" the 64 Bit OleDB
Drivers to the 32 Bit OleDB Drivers (or ODBC drivers).

How can you possibly know how hard it would have been...?

 

[Willy Denoyette [MVP]]

Thanks for the hints and sorry for an additional stupid question (I
don't know much about x64 architecture...): I guess when the app is
run in 32 Bit mode, it will also behave like it was run under Win32
and not address more than 2GB? That is no option then, since the
ability to use huge amounts of RAM is why the app was ported to x64 in
the first place.

Let me see, you are talking about porting, but you rely on a 32 bit
application to get your data from, this is not what I call porting, porting
means that you control the whole code base that should be ported. You are
accessing Excel data, right, excel is still 32 bit, you can't possibly have
worksheets larger than 2GB.

I could not have been that hard to at least "bridge" the 64 Bit OleDB
Drivers to the 32 Bit OleDB Drivers (or ODBC drivers). This makes me
wonder how much dedication MS spents to x64 systems.

What for? Excel (all Office for that matter) is still 32 bit, why would you
ever need JET 64 bit drivers for?

I will look for other solutions like write a litte 32 Bit Program that
only executes the Jet Imports (and communicates with the 64 Bit
somehow and I don't know how other than exchanging files) or have SQL
Server import the Excel data to a temp table, but it is a pity that
this is necessary. My app is a windows service, I wonder how deep the
porting issues would possibly be if I had to work with graphics and
printer drivers.

?? What do printer drivers and graphics have to do with all this is beyond
me.
What you need to do is plit your application into a 32 bit part and a 64 bit
part, use COM interop to cross the 64/32 bit boundary.
For instance, drop the code (just a simple class library compiled as 32 bit)
that retrieves the Excel data into a COM+ (System.EnterpriseServices) as a
"server type" application, and call that server methods from your 64 bit
Windows service. This is exactly why System.EnterpriseServices are made for.

Willy.

 

[DC]

How can you possibly know how hard it would have been...?

You are right, I was only guessing. However, it is a shame to look
into the 64 Bit ODBC panel and only find the SQL Drivers ("empty
dabase connection"). And if one successfully converts a project to x64
and then runs it proudly and finds out that there is not Jet drivers
for x64 available, one may think "DAMN, it couldn't have been so hard
to port those drivers, at least if you own the source you should be
able to get it done!"

 

[DC]

Hi Willy, thank you for the suggestion, the COM interop stuff is what
I will try if I fail to use SQL Server to carry out the imports for
me.

I am only importing small sheets, the > 2GB of RAM is required
elsewhere in the application. I agree that there is some sort of logic
concerning your explanation why there are no 64 Bit Jet/ODBC driver
other than the SQL Server drivers, but in my opinion that explanation
is more a possible excuse for Microsoft to not port the drivers. I
have to import Excel sheets into a x64 service, I am not asking for
legacy support of an age old application.

 

[Mark Rae]

Hi Willy, thank you for the suggestion, the COM interop stuff is what
I will try if I fail to use SQL Server to carry out the imports for
me.

These guys do 64-bit drivers for file-based data sources:
http://www.openlinksw.com/

Obviously, not for free...

 

[Willy Denoyette [MVP]]

Hi Willy, thank you for the suggestion, the COM interop stuff is what
I will try if I fail to use SQL Server to carry out the imports for
me.

I am only importing small sheets, the > 2GB of RAM is required
elsewhere in the application. I agree that there is some sort of logic
concerning your explanation why there are no 64 Bit Jet/ODBC driver
other than the SQL Server drivers, but in my opinion that explanation
is more a possible excuse for Microsoft to not port the drivers. I
have to import Excel sheets into a x64 service, I am not asking for
legacy support of an age old application.

MS has told us years ago that JET was going to "maintenance mode," that is,
no new versions would be made available, only SP's, JET is since then no
longer distributed with Office and is no longer part of MDAC either. That
said, the problem is not whether it can be ported or not, the problem is
that you would not be able to load JET64 into an Office client like Excel or
Access, unless MS ports Office to 64 bit.

Willy.

 

[Mark Rae]

MS has told us years ago that JET was going to "maintenance mode," that
is, no new versions would be made available, only SP's,

Yes indeed:
http://msdn2.microsoft.com/en-us/library/ms810810.aspx

JET is since then no longer distributed with Office and is no longer part
of MDAC either.

Correct. In fact, MDAC doesn't even exist in Vista, having been replaced
with WDAC:
http://msdn2.microsoft.com/en-us/library/ms692897.aspx

That said, the problem is not whether it can be ported or not, the problem
is that you would not be able to load JET64 into an Office client like
Excel or Access, unless MS ports Office to 64 bit.

Which is bound to happen sooner or later...

 

[Willy Denoyette [MVP]]

Yes indeed:
http://msdn2.microsoft.com/en-us/library/ms810810.aspx


Correct. In fact, MDAC doesn't even exist in Vista, having been replaced
with WDAC:
http://msdn2.microsoft.com/en-us/library/ms692897.aspx


Which is bound to happen sooner or later...

Well, the port is not the real issue, Office is a market driven product and
currently there is no market (desktop) for 64 bit Office, so I guess MS will
not release such product any time soon, but more importantly, there is no
real advantage in running a 64 bit version of office over the 32 bit
version, ever felt the need to load >1GB document in Word?, it takes ages,
the 64 bit version would even be slower.

Willy.