I'm using Azure Data Factory (ADF) on the portal. I use the latest Chrome browser to get to ADF. I'm not an admin and have no admin rights. I have an Excel file I want to read. I'm still fairly new to ADF. I'm guessing this could be done in a data flow. My overall plan for this project was to have one pipeline execute many data flows. Each data flow would be different steps in the whole process. We sometimes get Excel files with the columns in the wrong order, missing columns, added columns we do not want, etc, so I have to check about 10 column names in row 1 to make sure the column names match what we are expecting. If one column name is incorrect the program should trigger/display an error and stop. The tutorials I completed did not cover this. A Google search and AI question also did not help. In my data flow called "dfCheckHeaders" I have these transformations: My source transformation: My data source points to a single Excel spreadsheet with 1000s of rows. Allow Schema Drift is checked. The file is .XLSX. Next is a SELECT transformation. Under the tab "Select Settings" I only select the columns I need for this. Next is a CAST transformation to change one column from a string to a float type. Next is a sink. It points to a container called "hdrschecked" in my blob storage. In my search I did find a mention of using the "row_number()" function but the page wasn't clear if this was an ADF function of SQL function. Since I'm accessing an Excel spreadsheet, using row_number as an SQL function does not make sense. Can anyone help me or point me to a page or tutorial I can do for this? Thank you!

@Chuck Roberts Thanks for reaching out to Microsoft Q&A. To check the column headers in your Excel file using Azure Data Factory (ADF), you can indeed use a data flow. Here’s a step-by-step approach to achieve this: Steps to Validate Column Headers in ADF Create a Data Flow : In your ADF, create a new data flow (e.g., dfCheckHeaders ). Source Transformation : Add a Source transformation that points to your Excel file. Ensure Allow Schema Drift is checked to accommodate any changes in the column order or missing columns. Add a Derived Column Transformation : Use a Derived Column transformation to create a new column that checks if the expected headers are present. You can use an expression like: SQL iif(columnName1 == 'ExpectedHeader1' && columnName2 == 'ExpectedHeader2' && ..., 'Valid', 'Invalid') This will help you identify if the headers match your expectations. Filter Transformation : Add a Filter transformation to filter out rows where the derived column indicates ‘Invalid’. This will help you isolate any discrepancies. Conditional Split : You can also use a Conditional Split transformation to route the flow based on whether the headers are valid or not. If any header is incorrect, you can direct the flow to a sink that logs the error or triggers an alert. Sink Transformation : Finally, add a Sink transformation to store the results. You can point this to a blob storage container (like your hdrschecked container) to keep track of the validation results. Here’s a simplified example of how you might set up your derived column expression: iif( column1 == 'Header1' && column2 == 'Header2' && column3 == 'Header3' && column4 == 'Header4' && column5 == 'Header5' && column6 == 'Header6' && column7 == 'Header7' && column8 == 'Header8' && column9 == 'Header9' && column10 == 'Header10', 'Valid', 'Invalid' ) Resources For more detailed guidance, you might want to check the official Azure Data Factory documentation Hope this helps. Do let us know if you any further queries.

How to check text in row 1 of Excel file for about 10 column headers?

Accepted answer

phemanth 11,885 Reputation points Microsoft Vendor

2024-11-05T17:37:20.27+00:00
@Chuck Roberts

Thanks for reaching out to Microsoft Q&A.

To check the column headers in your Excel file using Azure Data Factory (ADF), you can indeed use a data flow. Here’s a step-by-step approach to achieve this:

Steps to Validate Column Headers in ADF

Create a Data Flow:

In your ADF, create a new data flow (e.g., dfCheckHeaders).

Source Transformation:

Add a Source transformation that points to your Excel file. Ensure Allow Schema Drift is checked to accommodate any changes in the column order or missing columns.

Add a Derived Column Transformation:

Use a Derived Column transformation to create a new column that checks if the expected headers are present. You can use an expression like:SQL
iif(columnName1 == 'ExpectedHeader1' && columnName2 == 'ExpectedHeader2' && ..., 'Valid', 'Invalid')

This will help you identify if the headers match your expectations.

Filter Transformation:

Add a Filter transformation to filter out rows where the derived column indicates ‘Invalid’. This will help you isolate any discrepancies.

Conditional Split:

You can also use a Conditional Split transformation to route the flow based on whether the headers are valid or not. If any header is incorrect, you can direct the flow to a sink that logs the error or triggers an alert.

Sink Transformation:

Finally, add a Sink transformation to store the results. You can point this to a blob storage container (like your hdrschecked container) to keep track of the validation results.

Here’s a simplified example of how you might set up your derived column expression:

iif( column1 == 'Header1' && column2 == 'Header2' && column3 == 'Header3' && column4 == 'Header4' && column5 == 'Header5' && column6 == 'Header6' && column7 == 'Header7' && column8 == 'Header8' && column9 == 'Header9' && column10 == 'Header10', 'Valid', 'Invalid' )

Resources

For more detailed guidance, you might want to check the official Azure Data Factory documentation

Hope this helps. Do let us know if you any further queries.
Please sign in to rate this answer.

1 person found this answer helpful.
Chuck Roberts 130 Reputation points

2024-11-06T10:58:57.2633333+00:00

I think this will work. I will have to try it though.

So help me understand. In the Derived Column step, if the headers in row 1 are not correct it will return 0 rows? Is that what I'm looking for?

For debugging purposes I want to display messages to a screen. For production purposes errors will go to a CSV file. How do I display a message to the screen if there is an error?

And how to I compare text while ignoring case? If the case is different that is ok.

So while a column header in row 1 should say "Shipment Date" we can also accept "shipment date".

phemanth 11,885 Reputation points Microsoft Vendor

2024-11-07T18:34:52.4633333+00:00

@Chuck Roberts

Let’s break down your queries regarding the Derived Column step and how to handle errors and case sensitivity in Azure Data Factory (ADF).

Derived Column Step and Row Output

In the Derived Column transformation, if the headers in row 1 do not match your expected values, the output will depend on how you set up your expressions. If you filter out invalid headers using a Filter transformation afterward, it will return 0 rows if all headers are invalid. This is indeed what you’re looking for to indicate an error condition.

Displaying Messages to the Screen

To display messages on the screen for debugging purposes, ADF itself does not have a built-in feature for real-time UI messages. However, you can use the following approaches:

Azure Logic Apps or Azure Functions: You can trigger a Logic App or Azure Function from your ADF pipeline to send notifications (like emails or messages) when an error occurs.

Web Activity: If you have a web service that can accept messages, you can use a Web Activity to send error messages to that service.

Debugging Output: During development, you can use the Data Preview feature in ADF to check the output of each transformation step, which can help you identify issues.

Writing Errors to a CSV File

For production purposes, you can set up a Sink transformation to write error messages to a CSV file in your blob storage. You can create a separate flow that captures any invalid headers and logs them into a CSV format.

Comparing Text While Ignoring Case

To compare text while ignoring case sensitivity in ADF, you can use the toLower() or toUpper() functions in your Derived Column expression. Here’s how you can modify your expression:

SQL

iif( toLower(column1)

This way, it will accept both “Shipment Date” and “shipment date” as valid inputs.

Summary

If all headers are invalid, the Derived Column step can lead to 0 rows if filtered correctly.

For debugging, consider using Logic Apps or Functions for notifications, as ADF doesn’t support direct screen messages.

Use toLower() or toUpper() for case-insensitive comparisons.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Chuck Roberts 130 Reputation points

2024-11-11T10:12:46.7633333+00:00

Thanks, I need a few days to implement all of these steps. My schedule can get very busy and I can change projects quite a bit sometimes. This is only one project of several that I have. My apologies as it was very difficult to find an Azure tutorial for a beginner like me, and the tutorial I was able to finish did not cover many things that I need.

Chuck Roberts 130 Reputation points

2024-11-11T10:26:25.2766667+00:00

It looks like the function I have to use is actually lower(). I got an error using "toLower()".

IIF(lower(column1) == lower('ColumnHeader') && ... )

Chuck Roberts 130 Reputation points

2024-11-11T11:39:55.42+00:00

Ok I'm having a new problem. My data flow looks like this:

Source.

Select Header row. This only gets columns I need to check the headers on.

Derived fields. This has the expression

iif( lower(TrackNum) == lower('Shipment Tracking Num') && lower(ShipDate) == lower('Shipment Date') && lower(NetCharge) == lower('Net Charge') && lower(NumPieces) == lower('Number of Pieces') //&& lower(Shipto1) == lower('Recipient Company') && lower(Shipto2) == lower('Recipient State') //&& lower(ShiptoCity) == lower('Recipient City') && lower(ShiptoState) == lower('Recipient State') , 'Valid', 'Invalid' )

But in this step I'm getting an error "Type mismatch" because it is running this expression on every row. Some rows have numbers in the column but row 1 has the column header which is a string. Just before this step I need to select only the first row by row number.

How would I do that?

Thanks you.

Chuck Roberts 130 Reputation points

2024-11-11T11:44:36.0433333+00:00

Wow, these comments are taking about 4-5 minutes to actually appear after I click Post. The MS AI on this forum says to use the TOP transformation in my data flow but there is no TOP transformation.

phemanth 11,885 Reputation points Microsoft Vendor

2024-11-11T18:56:36.5233333+00:00

Since you want to check the headers in the first row of your Excel file, you can achieve this by using a Filter Transformation or a combination of the Derived Column and Filter transformations. Here's how:

Add a Derived Column Transformation (optional):

Before you apply the header validation logic, you can add a Derived Column transformation to create a new column that indicates whether the row is the first row. You can use the row_number() function to achieve this.

Create a new column (e.g., RowNum) with an expression like:

sql 1row_number()

- This will assign a unique row number to each row in your dataset.

Add a Filter Transformation:

After the Derived Column transformation, add a Filter transformation to filter for only the first row.

Set the filter condition to:
1RowNum == 1

Derived Column for Header Validation:

Now, you can proceed with your Derived Column transformation to validate the headers, since the Filter transformation will ensure that only the first row is processed:
iif( lower(TrackNum) == lower('Shipment Tracking Num') && lower(ShipDate) == lower('Shipment Date') && lower(NetCharge) == lower('Net Charge') && lower(NumPieces) == lower('Number of Pieces'), 'Valid', 'Invalid' )

Type Mismatch Error: The type mismatch error occurs because the derived column expression is being evaluated on all rows, including those that contain numeric values in columns where you expect strings (like headers). By filtering for the first row before applying your validation logic, you avoid this issue.

No TOP Transformation: You are correct that ADF does not have a direct "TOP" transformation. Instead, using the row_number() function and filtering is a common approach to achieve similar functionality.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to check text in row 1 of Excel file for about 10 column headers?

0 additional answers

Your answer