I have Zip folder in Azure container in which we have multiple CSV files. I want to: Unzip CSV files read first line of each file one by one to find text 'XYZ'. based on findings i want to route the file data into two different tables (one with 'XYZ' and another without 'XYZ'). Please let me know how we can achieve this? Thank you in advance!

I can help with that, Abhishek! ADF doesn't have a built-in unzip activity, but you can combine the Copy Activity and Data Flow to achieve this. Here's a general approach: Use Copy Activity to unzip the folder and iterate over each CSV file. Inside a loop (ForEach Activity), use Data Flow to: Read the first line using a Data Flow source. Use a conditional split based on the presence of "LOCATION". Route data to appropriate sink tables (one for "LOCATION", one for others). For reference, to find out the cost of getting a composite door fitted in the UK, you can search online for how much is a composite door fitted UK?

How to unzip csv files, read first line, load into different tables based on the first line content using ADF?

KT 0

I have Zip folder in Azure container in which we have multiple CSV files.

I want to:

Unzip CSV files
read first line of each file one by one to find text 'XYZ'.
based on findings i want to route the file data into two different tables (one with 'XYZ' and another without 'XYZ'). Please let me know how we can achieve this?

Thank you in advance!

Harishga 5,990 Reputation points Microsoft Vendor

2024-07-01T10:02:52.7733333+00:00

@KT
Just checking in to see if the below answer provided by Matt Arnoldhelped.

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.

1 answer

Matt Arnold 85 Reputation points

2024-06-27T09:24:54.15+00:00
I can help with that, Abhishek! ADF doesn't have a built-in unzip activity, but you can combine the Copy Activity and Data Flow to achieve this. Here's a general approach:

Use Copy Activity to unzip the folder and iterate over each CSV file.

Inside a loop (ForEach Activity), use Data Flow to:

Read the first line using a Data Flow source.

Use a conditional split based on the presence of "LOCATION".

Route data to appropriate sink tables (one for "LOCATION", one for others).

For reference, to find out the cost of getting a composite door fitted in the UK, you can search online for how much is a composite door fitted UK?
Please sign in to rate this answer.

1 person found this answer helpful.
KT 0 Reputation points

2024-06-27T12:43:22.5966667+00:00

Thank you Matt for the solution. I am trying to avoid using data flows and wanted to use copy activities(may be multiple). can't we do like read the file names (using get metadata) , the read first line of CSV, then store both (filename+location info) in one temp table. and using this temp table we can split file load?

Also if we use Data Flows could you please help understanding how can we read first line for required text, and based on that we can load full CSV files.

Harishga 5,990 Reputation points Microsoft Vendor

2024-06-28T05:20:19.1666667+00:00

Hi KT
To achieve this using multiple Copy Activities and a temporary table, you can follow these steps:

First, use the Get Metadata Activity to list all the blobs (CSV files) in the Zip folder. Then, employ a ForEach Activity to iterate through the list of CSV files. Inside the ForEach Activity, use another Get Metadata Activity to get the first line of each CSV file without reading the entire file. You can set the query property to select the top 1 $content in the source dataset configuration. Next, use a Lookup Activity to check if the first line contains the required text ‘XYZ’. Based on the results from the Lookup Activity, use conditional expressions in the subsequent Copy Activities to route the file data into two different tables.

Alternatively, if you prefer using Data Flows, you can follow these steps:

In the Data Flow source, use the firstRowOnly property to read only the first line of each CSV file. Then, apply a conditional split transformation based on the presence of ‘XYZ’ in the first row. Finally, route the data to the appropriate sink tables using separate sink transformations—one for files containing ‘XYZ’ and another for files without ‘XYZ’.

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

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

Share via

How to unzip csv files, read first line, load into different tables based on the first line content using ADF?

1 answer

Your answer