What’s the best way to import an Excel-file to SQL Server on a 64 bit Windows platform in ASP.NET?

Question

What's the best/smoothest solution to import an Excel-file to SQL Server, on a 64 bit Windows system?

What I'm specifically after is to import an Excel 97-2003 file, to a SQL 2005 database, from an ASP.NET/C# page. The import data will demand users to use a "template", so the data looks the same every time.

Previously the system has used Microsoft Jet OleDb 4.0 for this kind of import, but now it has moved to a 64 bit environment. I know Jet can run on 64 bit, if IIS is run in 32 bit mode, but that is in my opinion not an option.

So, what are the 64 bit alternatives here?

Answer 1

You could upload the file and save it in a specific folder and then have a Windows service or other process that runs in 32-bit and in the background. This background process monitors the upload folder and imports newly Excel files into SQL Server.

The disadvantage is that you don't have direct reponse to the user.

Answer 2

I would do this by reading the excel file into a DataTable object and then using SqlBulkCopy to store the information. This would be fast and relatively simple to code.

Answer 3

You should use Integration Services. Create an Integration Services package that load the Excel file (relatively easy thing to do; you can even use the Import and Export wizard), and then call the package from asp.net

Answer 4

Another way to read Excel into an ADO.NET DataTable:

http://www.aspspider.com/resources/Resource510.aspx

Although I would also recommend just using an SSIS package to do the work.