Import data into SQL Server from an Azure Excel blob file

Question

I have an MVC web application that allows user to upload an Excel files to Azure cloud storage, and then the application uses that Azure-stored Excel blob file to import data into SQL Server.

I follow the sites http://www.codeproject.com/Tips/752981/Import-Data-from-Excel-File-to-Database-Table-in-A

and

Upload Excel File and Extract Data from it and put that data in database using MVC asp.net

to do my application. However, the example from the site http://www.codeproject.com/Tips/752981/Import-Data-from-Excel-File-to-Database-Table-in-A lets users upload file to web server where application is deployed not Azure storage, and the contents of "fileLocation" variable (please see the below codes) looks like (relative to web-server-hosted application path C or whatever drive) "C:\MyWebApplicationFolder\MyApplicatioName\Content\Excel_blob.xlsx"

My question: for Azure storage Excel blob files, how can I specify the value of "fileLocation" and "excelConnectionString" variables? Please see my code comments starting with phrase "// *** How can I can do this with Azure storage codes?" below.

Codes from http://www.codeproject.com/Tips/752981/Import-Data-from-Excel-File-to-Database-Table-in-A

[HttpPost]
public ActionResult Upload(HttpPostedFileBase file)
{
    DataSet ds = new DataSet();
    if (Request.Files["file"].ContentLength > 0)
    {
        string fileExtension =  System.IO.Path.GetExtension(Request.Files["file"].FileName);

        if (fileExtension == ".xls" || fileExtension == ".xlsx")
        {
            string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;  // *** How can I can do this with Azure storage codes?

        if (System.IO.File.Exists(fileLocation))
        {
            System.IO.File.Delete(fileLocation);
        }
        Request.Files["file"].SaveAs(fileLocation);
        string excelConnectionString = string.Empty;

        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +     fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";  // *** How can I can do this with Azure storage codes?


        //connection String for xls file format.
        if (fileExtension == ".xls")
        {
            excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +   fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";     // *** How can I can do this with Azure storage codes?
        }
        //connection String for xlsx file format.
        else if (fileExtension == ".xlsx")
        {
            excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +   fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";     // *** How can I can do this with Azure storage codes?
        }  

        ...

I'm in the same ballpark as you, problem-domain-wise. I think the bigger issue here is that the OLEDB drivers referenced in the connection strings above are not present in the Azure website environment - no Office applications/drivers are installed. — oflahero
– oflahero, Commented Sep 3, 2015 at 13:10
So even if you got the file location right (on Azure, you have write access to the root of your app folder and below ('~/' and down from there) the bigger issue then is the actual drivers themselves. — oflahero
– oflahero, Commented Sep 3, 2015 at 13:12

Ann Yu · Accepted Answer · 2016-08-08 09:38:01Z

Maybe you can download blob file from Azure to your server disk first and then import it to your db. You can download the full project here.

Downloading:

container.CreateIfNotExists();
CloudBlockBlob blob = container.GetBlockBlobReference(excelName);
blob.DownloadToFile(filePath, FileMode.Create);

Read the file to data table:

DataTable dt = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false))
{
    //Get sheet data
    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
    IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
    string relationshipId = sheets.First().Id.Value;
    WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
    Worksheet workSheet = worksheetPart.Worksheet;
    SheetData sheetData = workSheet.GetFirstChild<SheetData>();
    IEnumerable<Row> rows = sheetData.Descendants<Row>();

    // Set columns
    foreach (Cell cell in rows.ElementAt(0))
    {
        dt.Columns.Add(cell.CellValue.InnerXml);
    }

    //Write data to datatable
    foreach (Row row in rows.Skip(1))
    {
        DataRow newRow = dt.NewRow();
        for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
        {
            if (row.Descendants<Cell>().ElementAt(i).CellValue != null)
            {
                newRow[i] = row.Descendants<Cell>().ElementAt(i).CellValue.InnerXml;
            }
            else
            {
                newRow[i] = DBNull.Value;
            }
        }
        dt.Rows.Add(newRow);
    }
}

Use bulk copy to insert the data to db

 //Bulk copy datatable to DB
 SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr);
 try
 {
     columns.ForEach(col => { bulkCopy.ColumnMappings.Add(col, col); });
     bulkCopy.DestinationTableName = tableName;
     bulkCopy.WriteToServer(dt);
 }
 catch (Exception ex)
 {
     throw ex;
 }
 finally
 {
     bulkCopy.Close();
 }

Collectives™ on Stack Overflow

Import data into SQL Server from an Azure Excel blob file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related