OleDB not supported in 64bit mode?

问题:

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.

回答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.

回答2:

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);
    }
}

回答3:

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

回答4:

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

回答5:

This is more an informational post for anyone that might be experiencing this issue (and for myself incase I have the same problem in the future and can't remember the solution :-)) It's kind of obscure but caused me a few hours of stress, so maybe it'll help someone else... Sorry if this is repeated (couldn't find it) or deprecated (some don't have the luxury of latest and greatest).

If you are using trying to use Jet 4.0 to access MS Excel documents (or other data files) on a x64-based server, you will have discovered that there is no support for this combination.

The only solution is to allow IIS to run 32-bit applications on Windows 64 and to install a supported db provider.

You'll need to install the driver, 64-Bit OLEDB Provider for ODBC (MSDASQL) that acts as a bridge: "The Microsoft OLE DB Provider for ODBC (MSDASQL) is a technology that allows applications that are built on OLEDB and ADO (which uses OLEDB internally) to access data sources through an ODBC driver. MSDASQL is an OLEDB provider that connects to ODBC, instead of a database. MSDASQL ships with the Windows operating system, and Windows Server 2008 & Windows Vista SP1 are the first Windows releases to include a 64-bit version of the technology." Download here : http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

This all works fine, but I came across two things that had me scratching my head (and stressing): 1) You need to allow 32-bit ASP.Net in IIS Web Service Extensions - Read ""http://www.textcontrol.com/blog/permalink/2006082101"" for instructions on both enabling 32-bit apps AND the IIS web service extension setup. 2) If you are using any registry keys under IIS x64, a new node will be added in the registry - Wow6432Node - into which you'll need to move/copy any relevant keys that were used under x64. i.e. We had a data key stored in HCLM\Software\CustomKey that was no longer available when 32-bit was enabled. We re-created the key under the Wow6432Node and all was good.

回答6:

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.

回答7:

Actually I think Linq is your best solution for this.

Something like....

IEnumerable<MyObj> ObjList = GetObjList(yourCSVFileNAme);

var qry = from o in ObjList
          where o.MyField == Something
          select o;

and your GetObjList method looks something like

Public IEnumerable<MyObj> GetObjList(string filename)
{
  // Obvioulsly you would have some actual validation and error handling
  foreach(string line in File.ReadAllLines(filename))
  {
    string[] fields = line.Split(new char[]{','});
    MyObj obj = new MyObj();
    obj.Field = fields[0];
    obj.AnotherField = int32.Parse(fields[1]);
    yield return obj;
  }
}