Unable to copy data/excel file from sharepoint location using sharepoint connector

%3CLINGO-SUB%20id%3D%22lingo-sub-2339636%22%20slang%3D%22en-US%22%3EUnable%20to%20copy%20data%2Fexcel%20file%20from%20sharepoint%20location%20using%20sharepoint%20connector%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339636%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20My%20requirement%20is%20to%20copy%20excel%20file%20from%20sharepoint%20to%20azure%20blob%20and%20I%20followed%20the%20below%20steps%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStep1%3A%20Created%20the%20pipeline%20with%20copy%20activity%26nbsp%3B%3C%2FP%3E%3CP%3EStep2%3A%20Configured%20the%20source%20settings%20in%20the%20copy%20activity%20as%20per%20steps%20followed%20in%20the%20below%20Link%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconnector-sharepoint-online-list%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconnector-sharepoint-online-list%3C%2FA%3E%3C%2FP%3E%3CP%3EPlease%20Note%20that%20I%20am%20unable%20to%20perform%20this%20step%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EDataset%3A%20choose%20the%20format%20you%20want.%20To%20copy%20file%20as-is%2C%20select%20Binary%20type%3C%2FLI%3E%3C%2FUL%3E%3CP%3EExcept%20this%20I%20performed%20all%20the%20other%20steps%3C%2FP%3E%3CP%3Ewhile%20clicking%20on%20the%20preview%20data%20on%20source%20data%20set%20in%20copy%20activity%20I%20am%20getting%20the%20following%20error%3C%2FP%3E%3CP%3E%3CSPAN%3E%22%3CSTRONG%3EOnly%20'.xls'%20and%20'.xlsx'%20format%20is%20supported%20in%20reading%20excel%20file%20while%20error%20is%20'%20at%20System.Threading.Tasks.Task.ThrowIfExceptional(Boolean%20includeTaskCanceledExceptions)%20at%20System.Threading.Tasks.Task.Wait(Int32%20millisecondsTimeout%2C%20CancellationToken%20cancellationToken)%20at%20Microsoft.DataTransfer.ClientLibrary.MultipartSequentialReadSource.%3CREADBUFFERSINSEQUENTIAL%3Ed__5.MoveNext()%20at%20Microsoft.DataTransfer.ClientLibrary.TransferStream.ReadInternal(Byte%5B%5D%20buffer%2C%20Int32%20offset%2C%20Int32%20count)%20at%20Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte%5B%5D%20buffer%2C%20Int32%20offset%2C%20Int32%20count)%20at%20Microsoft.DataTransfer.ClientLibrary.SizeBoundedTransferStream.Read(Byte%5B%5D%20buffer%2C%20Int32%20offset%2C%20Int32%20count)%20at%20Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte%5B%5D%20buffer%2C%20Int32%20offset%2C%20Int32%20count)%20at%20ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.Fill()%20at%20ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeByte()%20at%20ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeInt()%20at%20ICSharpCode.SharpZipLib.Zip.ZipInputStream.GetNextEntry()%20at%20NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource..ctor(ZipInputStream%20inp)%20at%20NPOI.OpenXml4Net.OPC.ZipPackage..ctor(Stream%20filestream%2C%20PackageAccess%20access)%20at%20NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream%20in1)%20at%20NPOI.Util.PackageHelper.Open(Stream%20is1)%20at%20NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream%20is1)%20at%20Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String%20fileExtension%2C%20TransferStream%20stream)'.%20One%20or%20more%20errors%20occurred.%20The%20credential%20to%20read%20http%20file%20is%20invalid.%20The%20remote%20server%20returned%20an%20error%3A%20(401)%20Unauthorized.%3C%2FREADBUFFERSINSEQUENTIAL%3E%3C%2FSTRONG%3E%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ewhile%20configuring%20the%20sink%20settings%20in%20the%20copy%20activity%20we%20observe%20that%20it%20is%20not%20supporting%20for%20excel%20format.%20However%20we%20tried%20with%20binary%20and%20csv%20format%20but%20no%20luck!%20Please%20help%20in%20this%20regard%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, My requirement is to copy excel file from sharepoint to azure blob and I followed the below steps,

 

Step1: Created the pipeline with copy activity 

Step2: Configured the source settings in the copy activity as per steps followed in the below Link

https://docs.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list

Please Note that I am unable to perform this step 

  • Dataset: choose the format you want. To copy file as-is, select Binary type

Except this I performed all the other steps

while clicking on the preview data on source data set in copy activity I am getting the following error

"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.<ReadBuffersInSequential>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. The credential to read http file is invalid. The remote server returned an error: (401) Unauthorized."

while configuring the sink settings in the copy activity we observe that it is not supporting for excel format. However we tried with binary and csv format but no luck! Please help in this regard

0 Replies