CODEWITHSUNDEEP
sql-serverazure-sql-databaseazure-storageazure-blob-storage
SUMguy
SUMguy

Reputation: 1673

Excel to SQL Table(s)

I am trying to take data that is in an Excel workbook which lives in Azure Blob Storage (also, the Excel file is updated manually every day and then exported to Blob Storage via MS Flow) and then export that data into some tables in Azure SQL database. I cannot use SSIS packages as that would be too costly. Anyone know of a way to get this done without SSIS? I've looked into OPENROWSET and linked servers, but both are "not supported in my version of SQL server." I've also considered converting the Excel file to CSV and then using ADF, but I can't figure out how to convert it to CSV in the blob... (without uploading manually)

Upvotes: 0

Views: 267

Answers (1)

Brando Zhang
Brando Zhang

Reputation: 27997

I've also considered converting the Excel file to CSV and then using ADF, but I can't figure out how to convert it to CSV in the blob... (without uploading manually)

According to your description, I suggest you could try to use azure webjob or azure function to achieve your requirement.

By using these two services, you could enable blob trigger(when new file is added into the blob) or time trigger(trigger the function every day) to execute the function to export the data from blob storage to the azure sql databse directly.

More details, you could refer to below codes(Web job codes) and article:

Webjob blobtrigger, webjob timer trigger.

//remember install the DocumentFormat.OpenXml from the Nuget Package. public class Functions {

        public static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            string value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }


        public static void ExportExcelToDatabase ([BlobTrigger("excel/testexcel.xlsx")] Stream blobStream, TextWriter log)
        {
            log.WriteLine("Start export excel to azure sql database");


            string connectionStr = "{sql database connection string}";

            //This is the excel table column name 
            List<string> columns = new List<string>() { "Name", "Class", "Score", "Sex" };
            string tableName = "StudentScore";


            DataTable dt = new DataTable();
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(blobStream, false))
            {
                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>();

                foreach (Cell cell in rows.ElementAt(0))
                {
                    dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }

                foreach (Row row in rows.Skip(1))
                {
                    DataRow tempRow = dt.NewRow();

                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }

                    dt.Rows.Add(tempRow);
                }
            }



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

            log.WriteLine("End export excel to azure sql database");


        }
    }

Upvotes: 1

Related Questions