Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
ADF - Excel compression
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(224.00000000000003, 77%, 31%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERA%3C/text%3E%3C/svg%3E)
Ryan Abbey
1,186
Reputation points
I want to set up a copy pipeline to allow dynamic processing of files, some of our files have been compressed and some haven't. With flat files, if there's no compression, then providing a null value suffices, however, in Excel, this generates an error "Object reference not set to an instance of an object."
Anyone managed to get around this (that doesn't involve having two copy activities...)?
PS For MS should you listen... in your "help" files on Excel, for range it states "- A3: reads a table starting from the given cell, dynamically detects all the rows below and all the columns to the right". This is not true, it generates an error 'Index was outside the bounds of the array.'.
Azure Data Factory
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(172.8, 70%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
MartinJaffer-MSFT 26,161 Reputation points2021-06-23T21:42:05.727+00:00 Hello @Ryan Abbey and welcome to Microsoft Q&A.
I am a little confused.
Is your source Excel, or flat file?
Which is compressed, your source or your sink?... providing a null value suffices ...
What are you providing a null value to?
I think you are asking how to parameterize compression. (see below pictures)
add a parameter to dataset
open and close the compression type and reveal theAdd dynamic contentoption
Use the parameter for compression type
Pass the compression type in through the copy activity to dataset
@dataset().compression
-
Ryan Abbey 1,186 Reputation points
2021-06-24T02:24:01.983+00:00 How to parameterise is not a problem, the issue is that we have set the Compression up as a parameter but it will only accept a valid compression algorithm as a value. If we try specify "None", "" or null, it fails, this of course prevents us from accepting either compressed or uncompressed Excel files for the copy process
-
MartinJaffer-MSFT 26,161 Reputation points
2021-06-24T19:22:34.45+00:00 Thank you for the clarification @Ryan Abbey .
This sounds like you have already tried everything I would have thought of. I will need to escalate the issue internally for more ideas.
If I do not get a positive solution from the developers, then I will add this as a feature request. I will let you know once I hear back, in either case.
Sign in to comment
Sign in to answer