We're already syncing files from on-premises network into our SPO (SharePoint Online) using OneDrive for Business.
For a fast mock-up we decided to automatically read an Excel file into our Web App's Azure SQL database.
I've been using Azure Data Factory for this task and I've been really amazed how mature this tool is.
I've started with this tutorial, it was really helpful.
In the task CopyDataFromSPO I've decided to store the Excel file in my already existing blob storage (sink).
Afterwards, I created a data flow named SaveExcelFileToDb where I read the Excel file from my blob and save it to my Azure SQL database. I always want to recreate the table with new data. This way I can easily workaround merge conflicts. However, this is a really simple solution for only demonstration purposes. In a real production environment there should be some additional and more fault-tolerant steps necessary.
Top comments (1)
Hi, hoping you can help I get the below error when trying to preview data from source dataset on copy data task:
Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
at Microsoft.DataTransfer.ClientLibrary.MultipartSequentialReadSource.d__5.MoveNext()
at Microsoft.DataTransfer.ClientLibrary.TransferStream.ReadInternal(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.DataTransfer.ClientLibrary.SizeBoundedTransferStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte[] buffer, Int32 offset, Int32 count)
at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.Fill()
at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeByte()
at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeInt()
at ICSharpCode.SharpZipLib.Zip.ZipInputStream.GetNextEntry()
at NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource..ctor(ZipInputStream inp)
at NPOI.OpenXml4Net.OPC.ZipPackage..ctor(Stream filestream, PackageAccess access)
at NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream in1)
at NPOI.Util.PackageHelper.Open(Stream is1)
at NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream is1)
at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.
One or more errors occurred.
Failed to read data from http server. Check the error from http server：The remote server returned an error: (403) Forbidden.
The remote server returned an error: (403) Forbidden.
. Activity ID