Support for Excel

Hello,

I'm curious whether LightSpeed can support Excel (xls, xlsx). Like other databases, it has certain limitations and one would not expect it to be used as a replacement of a high performance database server like SQL Server or MySQL.

That said, it is one of the most common formats for importing and exporting data. Here are a couple of scenarios where such a feature would be of tremendous value:

1. Export one or more queries to an Excel workbook that users can use to perform offline ad-hoc analytics.
2. Provide the user with a convient format to which we can "export" a copy of their data from the database
3. Seeding a database (MySQL, SQL Server, Postgres) from an Excel Workbook (each worksheet represents a table).
4. When testing the system, seed the "real" database with an Excel Workbook before each test. Currently I'm doing this with a SQL script, which isn't very useful for QA (they love Excel) and poses a maintenance problem as I have to modify the SQL script every time I change schema. The latter is slower than doing it in Excel.

Thanks, Werner

No, we dont have an Excel provider but the idea does sound very useful particularly with your approach for test data. We are extracting the providers to be pluggable for 5.0 so this would present an opportunity to be able to create one of these. I like the sound of this one so I will have a look at creating one as a test case.

Nothing available for now though Im afraid.

Jeremy

Hi Werner,

I'm not sure if it will help but you can look at the Mindscape.LightSpeed.Profiling.Interceptor class and override the CreateConnection and CreateCommand and ExecuteCommand methods.

I'm not sure just thought I would post it.

Cheers,

Johan

Hi Johan,

Interesting idea. I know I can access Excel workbooks using ODBC and OleDb and something like this may work. It seems that those require Excel to be installed. This may be great for a desktop application.

That isn't really practical on a server. One way LightSpeed can support this is to wrap a library like Aspose.Cells allowing excel to be supported even if Excel is not installed.

Werner

The "Jet" ADO.NET provider talks to Excel -- no install needed.

http://support.microsoft.com/kb/316934

You could use Dapper to load the LightSpeed models. Or IUnitOfWork.FindBySql.

My understanding is that OLE DB for Jet is only supported on 32bit applications. Office 2010 (which comes with 64bit flavor) introduced some support. Makes it rather impractical for server usage.

There's a 64-bit redistributable. Forum doesn't like the link; find it in this post:

http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

From the downloads page it states that the Access Database Engine 2010 Redistributable is not intended:

To be used by a system service or server-side program where the code will run under a system account, or will deal with multiple users identities concurrently, or is highly reentrant and expects stateless behavior. Examples would include a program that is run from task scheduler when no user is logged in, or a program called from server-side web application such as ASP.NET, or a distributed component running under COM+ services.

For purposes of testing (aka seeding the database) this may work. Whether it will work on a server or not for exports warrants further testing. However, given that warning, I'm a tad reluctant to even try.

Here's the same file on a different link with gentler qualifications -- basically says "use this to move data around ... anything else is asking for trouble."

http://www.microsoft.com/en-us/download/details.aspx?id=13255

The whole scenario is a bit odd. Seems like you're in a unique pain point between XML and CSV, enjoying the worst of both worlds.

Everyone loves Excel (for good reason). Supporting it is the pain point.