Using ADF, how to dynamicaly replace excel headers blank spaces with underscores?

DIAMANT Marius 0 Reputation points
2024-04-24T13:33:57.16+00:00

Hello,

I'm working on an ADF pipeline whose goal is to copy an Excel file to a blob storage as a parquet file. To do so, I'm using a copy data activity. This activity needs to be as parametrized as possible, meaning that the Excel file schema is not to be known.

My issue is: if one of the headers includes a forbidden character (like a blank space), the activity will fail (c.f. error message below*). And so, I'm looking for a way to replace those blank spaces with an underscore character. To do so, I could easily use a derived column from Data Flow but it needs the schema and headers names which is not possible in my context.

So far I could not find any bypass solution...

*ErrorCode=ParquetInvalidColumnName,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The column name is invalid. Column name cannot contain these character:[,;{}()\n\t=],Source=Microsoft.DataTransfer.Common,'

Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,481 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,529 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,713 questions
{count} votes