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

DIAMANT Marius 0

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,'

Harishga 3,505 Reputation points Microsoft Vendor

2024-04-25T06:39:04.2533333+00:00

Hi @DIAMANT Marius

Welcome to Microsoft Q&A platform and thanks for posting your question here.

Yes, the error message you provided states that the column name in the Excel file contains a forbidden character, such as a blank space, which is causing the Copy Data activity to fail.

One possible solution is to use the Schema Drift feature in Mapping Data Flow. This feature allows you to handle changes in the schema of your data source dynamically, without having to know the schema in advance.

To do this, you can create a new Mapping Data Flow activity in your pipeline and select the Excel file you want to copy to blob storage in the Source transformation. Then, in the Projection tab, select the option 'Allow schema drift'.

Next, in the Derived Column transformation, use the replace() function to replace blank spaces with underscores.

After that, in the Sink transformation, select the blob storage where you want to copy the parquet file. In the Mapping tab, map the columns from the Derived Column transformation to the Sink transformation. Finally, save and publish your Mapping Data Flow activity.

This will replace blank spaces with underscores in the headers of the Excel file and copy it to blob storage as a parquet file, even if the schema and headers names are not known in advance.

Reference
https://learn.microsoft.com/en-US/azure/data-factory/control-flow-expression-language-functions#replace
https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-schema-drift

I hope this information helps you. Let me know if you have any further questions or concerns.
Emi Zhang-MSFT 22,011 Reputation points Microsoft Vendor

2024-04-25T08:07:54.0666667+00:00

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Harishga 3,505 Reputation points Microsoft Vendor

2024-04-26T05:05:45.9633333+00:00

@DIAMANT Marius
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
DIAMANT Marius 0 Reputation points

2024-04-26T07:49:49.2+00:00

Hello, thank you for your answer, I think it is the right way to get to the solution. However, I still have an issue an the debug menu is not as helpful as I'd like:

The data flow is falling but I cannot visualize where, the only message error I have is coming from Monitor Pipeline Runs. Still, I don't understand why this error happening.

ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression 'body('<DataFlowName>ComposeRuntimeVariables')?.<DataFlowName>72a8ae7012c4f77b23245e9db3fe816?.source1.connection_secret'
Harishga 3,505 Reputation points Microsoft Vendor

2024-04-29T05:58:28.72+00:00
@DIAMANT Marius
Based on the error message you provided, it seems that there is an issue with the expression used in the Data Flow activity. The error message states that the expression is unable to be parsed, which means that there may be a syntax error or an issue with the way the expression is structured.

To resolve this issue, you can try the following steps:

Check the expression used in the Data Flow activity and ensure that it is correctly formatted and free of syntax errors.

Verify that the Data Flow activity name does not contain any spaces or hyphens, as this can cause issues with the expression evaluation.

Check the activity monitoring to verify the expressions used in the activity.

If the issue persists, try to recreate the Data Flow activity and ensure that the expression is correctly formatted.

I hope this information helps you. Let me know if you have any further questions or concerns.