How to import Excel document programmatically into Azure SQL table?

Question

Our ASP.NET web app lets users import data from Excel. We're currently hosted on our own servers, so have access to the file system both from the web server and the SQL server. The process currently works like this:

Save uploaded file to temp folder on server
Execute T-SQL to read uploaded file directly from the file system to a SQL temp table via OLEDB
Execute T-SQL to read data from temp table and process as needed (e.g. compare to existing data and update/insert as appropriate)

Step 2 looks something like this:

Select * into #MY_TEMP_TABLE
From OpenRowSet(
  'Microsoft.ACE.OLEDB.12.0', 
  'Excel 12.0; Database=PATH_TO_MY_UPLOADED_FILE; HDR=YES', 
  'Select * From MY_WORKSHEET_NAME%')

This is very fast and straightforward compared to (for example) reading the file into a datatable in .NET using EPPlus and then inserting the data row by row.

We're in the process of making the app Azure-ready (Azure Website and SQL Database, not VM). We can upload the file to blob storage and get its contents into a byte array, but then we're stuck with the row-by-row processing approach, which is slow and brittle.

Is there a fast way to programmatically bulk upload Excel to SQL in Azure?

Simon W · Accepted Answer · 2014-09-08 12:28:23Z

1

I'd look at one of the commercial Excel components from the likes of ComponentOne. Read the spreadsheet's contents into memory and the write it into Azure SQL Database using standard ADO.Net techniques.

This will probably be more reliable and you can utilise retry logic for transient failures (http://www.asp.net/aspnet/overview/developing-apps-with-windows-azure/building-real-world-cloud-apps-with-windows-azure/transient-fault-handling).

Note that you need to be aware of Throttling behaviour in Azure SQL Database and how it might impact your app: http://msdn.microsoft.com/en-us/library/azure/dn338079.aspx

edited Sep 8, 2014 at 12:28

answered Sep 8, 2014 at 12:23

Simon W

5,4083 gold badges27 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to import Excel document programmatically into Azure SQL table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related