OleDB not supported in 64bit mode?

Question

I've been using Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0 to read in .csv, .xls, and .xlsx files.

I just found out that neither of these technologies are supported in native 64bit mode!

I have 2 questions:

1. What is the supported way to programatically read .csv, .xls, and .xlsx files in 64 bit mode. I just can't find answers to this anywhere.

2. If I can't read in all three file types, what is the best way to read in .csv files in a 64 bit environment?

Notes:

• I'm using .NET (3.5p1)
• This is a shrink wrap app; redistribution is a key factor.

Update:

I can use CorFlags to force the application to run in 32bit mode, which works, but is not desirable.

Solution 1

Here is a discussion of what to do about deprecated MDAC. I am afraid the answer is not very satisfying ...

These new or converted Jet applications can continue to use Jet with the intention of using Microsoft Office 2003 and earlier files (.mdb and .xls) for non-primary data storage. However, for these applications, you should plan to migrate from Jet to the 2007 Office System Driver. You can download the 2007 Office System Driver, which allows you to read from and write to pre-existing files in either Office 2003 (.mdb and .xls) or the Office 2007 (*.accdb, *.xlsm, *.xlsx and *.xlsb) file formats. IMPORTANT Please read the 2007 Office System End User License Agreement for specific usage limitations.

Note: SQL Server applications can also access the 2007 Office System, and earlier, files from SQL Server heterogeneous data connectivity and Integrations Services capabilities as well, via the 2007 Office System Driver. Additionally, 64-bit SQL Server applications can access to 32-bit Jet and 2007 Office System files by using 32-bit SQL Server Integration Services (SSIS) on 64-bit Windows.

Solution 2

SpreadsheetGear for .NET can read and write .csv / .xls / .xlsx workbooks (and more) and supports 64 bit .NET 2.0+. SpreadsheetGear can be distributed royalty free with your shrink wrap applications.

You did not specify whether your application is WinForms or ASP.NET but SpreadsheetGear works with either. You can see live ASP.NET (C# & VB) samples here, learn about the WinForms samples here and download a free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Solution 3

The main problem is that the Jet DBMS is a 32bit library that gets loaded into the calling process, so you will never be able to use Jet directly from within your app in 64bit mode. As Tim mentioned you could write your own csv parser, but since this is a shrink-wrap app you want something that will handle a wider range of formats. Luckily, there are a number of ways to talk 32-bit apps, so you can still use Jet with a trick.

I would write a little exe that was marked to run only in 32-bit mode. This exe would take a command line argument of the name of the file to read and the name of a temp file to write to. I would use Jet to load the csv/xls, then put the data into an array of arrays, and use the xml serializer to write the data to the temp file.

Then when I need to load/convert a csv/xls file, I would do the following:

object[][] ConvertFile(string csvOrXlsFile)
{
    var output = System.IO.Path.GetTempFileName();
    try
    {
        var startinfo = new System.Diagnostics.ProcessStartInfo("convert.exe",
            string.Format("\"{0}\" \"{1}\"", csvOrXlsFile, output));

        System.Diagnostics.Process proc = new System.Diagnostics.Process();
        proc.StartInfo = startinfo;

        proc.Start();
        proc.WaitForExit();

        var serializer = new System.Xml.Serialization.XmlSerializer(typeof(object[][]));
        using (var reader = System.IO.File.OpenText(output))
            return (object[][])serializer.Deserialize(reader);
    }
    finally
    {
        if (System.IO.File.Exists(output))
            System.IO.File.Delete(output);
    }
}

Solution 4

You could try the FileHelpers library for your flat-file parsing. Works amazingly well.

Solution 5

You can use Microsoft Access Database Engine 2010 Redistributable to read and write csv, xls access etc. There is a 32 and 64 bit version of each driver.

View more solutions

Comments

[John Weldon]

Thanks Tim! I was hoping to avoid 'rolling my own' csv parsing solution. I know it's not rocket science, but it is suprisingly subtle sometimes.

[LarryB]

Parsing a CSV file is not as easy as posted (text in parentheses, different separator char for different locales, ...) but there are lots of CSV parsers out there.

[binarydreams]

Looks like the 2007 Office System Driver is the way to go. The 25mb download is a bit steep, though.

[John Weldon]

Thanks Christopher... I did consider FileHelpers, but LGPL is still undesirable to include in a commercial code base as far as I can tell...

[Mark Brackett]

It's unclear to me that the 2007 Office System Driver's are actually x64, as opposed to just replacement for JET...Has anyone verified that?

[Josh]

No it is definitely x86 as I just ran into this limitation trying to add SharePoint as a linked server in MSSQL x64. Not applicable to the OP but the way I worked around it was to install a separate x86 instance that linked to the Oledb source and wrapped it with a bunch of views and the x64 instance linked to the x86 instance.

[Nate]

I like this option, it keeps things as clean as possible. Its rare that you'll need to be running x64 for converting a flat file to your internal data object; however, it is likely you'd want to take advantage of x64 once you have the data in your internal data objects.

[John Weldon]

Sounds interesting Joe. Thanks!

[John Weldon]

nice! I hoped something would come up sooner or later from MS

[Alex Nolasco]

According to the FileHelpers source code for handling Excel files, it makes use of AdoDB which is restricted to 32bit apps. See ExtractDataTable function for additional info.

[Samuel]

@AlexanderN I don't see the source code but I tried with a 64 bits application to read an Excel file and it works perfectly. The same problem with my own old code using Ado throw an exception.

[Samuel]

+1 for this nice library. I love the way I can automap the column of the excel file with properties of my class; it save me a lot of time!