How to read files from sub folders in Azure data factory

Question

How to read files from sub folders in Azure data factory

ankit kumar 101

I have a blob container with a parent folder and multiple sub folders and each folders having files. I would like to get the file names only (and not the sub folder name) and rename the file names.

I see that you can use get metadata activity but how can I make it dynamic when you don't have same layer of sub-folders. what i mean is i have a structure something like this:

ParentFolder --> SubFolder1 --> Test.csv
ParentFolder---> SubFolder2 --> Test2.json
ParentFolder---> Subfolder2---> SubFolder3---> Test4.dat
ParentFolder---> Subfolder4--> Subfolder5---> Test3.txt

Using ADF I need to rename Test.csv, Test2.json, Test4.dat, Test3.txt, Is it possible to read only the files inside a Parent folder , considering I will have multiple Parent Folders , each having different structure.

Thanks ,
zzz

Haris R 0 Reputation points

2023-11-28T11:04:39.71+00:00

Hi @HarithaMaddi-MSFT,

The Child pipeline gif is not visible. Can you please share once again

2 answers

Your answer

Haris R 0 Reputation points

2023-11-28T11:04:39.71+00:00

Hi @HarithaMaddi-MSFT,

The Child pipeline gif is not visible. Can you please share once again

Answer 1

HarithaMaddi-MSFT 10,146

Hi @ankit kumar ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

This is complicated to achieve in data factory if the folder structure is dynamic and also there is no activity directly available to rename the file name in data factory. Below GIF shows an workaround approach to loop through folders and separate files and folders in them. Later, the files can be passed to child pipeline which can use dataflow to copy the files with new name and delete the source file using the option shown in snap below. Since we cannot use foreach inside a foreach , parent and child pipelines are used here.

Parent Pipeline:

Dataflow option in child pipeline:

I would recommend to upvote below feedback items that are related to this requirement. Also, please post any new idea in this feedback forum that is closely monitored by data factory product team and will be implemented in future releases.

4041042-rename-blobs-without-needing-to-copy-them
39756814-add-foreach-loop-nested-capability

Hope this helps! Please let us know if this is not aligning with the requirement and we will be glad to assist further.

HarithaMaddi-MSFT 10,146 Reputation points

2020-10-07T06:24:30.913+00:00

Hi @ankit kumar ,

We have not received a response from you. Please let us know if above answer addressed the requirement and if not, we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
ankit kumar 101 Reputation points

2020-10-07T11:11:12.71+00:00
@HarithaMaddi-MSFT

In Execute Pipeline "DeleteFilePipeline" I am asked to select a pipeline, You have selected Child Pipeline, Can you show me the activities inside child pipeline. I have further 2 questions:

after copying are we retaining the folder structure or its going to be a flat structur?

If I have file with different format , like csv, json, dat and any other extension, so binary format will be the way to go, and data flows dont support Binary format. how do we handle then?

Thanks
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-07T14:02:33.02+00:00

Hi @ankit kumar ,

Please find below GIF showing the child pipeline design. If file formats can be different, copy activity is preferred but we need to add delete activity in addition to copy activity to delete the source file as inbuilt capability to delete files is not available in copy. The folder structure can also be passed as parameter to child pipeline which can be used in the dataset while copying the file that helps in retaining the folder structure.

Hope this helps! Please let me know for further queries and we will be glad to assist.
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-08T07:17:03.807+00:00

Hi @ankit kumar ,

We have not received a response from you. Please suggest if above responses helped implementing the requirement. Otherwise, let us know and we will continue to engage with you on the issue.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
HarithaMaddi-MSFT 10,146 Reputation points

2020-10-09T15:41:54.65+00:00

Hi @ankit kumar ,

We still have not heard back from you. Following up to check if above responses helped implementing the requirement. Otherwise, let us know and we will continue to engage with you on the issue.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Thulasi Arumugam 20 Reputation points

2023-03-02T23:33:55.8666667+00:00

Hi @HarithaMaddi-MSFT

I have the same question as ankit kumar . can you please help me out.

I have a blob container with a parent folder and multiple sub folders and each folders having files. I would like to get the file names only (and not the sub folder name) and rename the file names.

I see that you can use get metadata activity but how can I make it dynamic when you don't have same layer of sub-folders. what i mean is i have a structure something like this:

ParentFolder --> SubFolder1 -->subfolder->123.pdf
ParentFolder---> SubFolder2 --> subfolder--->234.pdf
ParentFolder---> Subfolder2---> SubFolder3---> Test4.pdf
ParentFolder---> Subfolder4--> Subfolder5---> Test3.pdf

Using ADF I need to rename 123.pdf,234.pdf,Test4.pdf,test3.pdf as 123_data.pdf, 234_data.pdf...Can you please help me out

Answer 2

Hi @HarithaMaddi-MSFT

I have the same problem. I have a blob storage and read files in sub level event hub capture. Follow my solution and it's working, would it be correct? Could it improve?

A problem I identified was when an error occurs within the until task does not stop

Create variables: filename string, pos string, posAux string, pathFolder array, pathFolderAux array, containeer string

Pipeline:

Inicialize conatineer variable with root path.

Append variable Container (pathFolder): @variables('container')

2) create task until
UntilFolder with condition: @greaterOrEquals(int(variables('pos')), length(variables('pathFolder')))

  2.1) Set variable Containeer (container): @variables('pathFolder')[int(variables('pos'))]  
  2.2) Set variable PosAux (posAux): @variables('pos')  
  2.3) Set variable Pos (pos): @string(add(int(variables('posAux')), 1))  
  2.4) Get Metadata File: directory = @variables('container'), filename = *.avro and Field list = Child items

  2.5) FilterFile  
         2.5.1) If Condition File: Expression = @greater(length(activity('FilterFilename').output.value), 0)  
                   2.5.1.1) If true: read file  

  2.6) FilterFolder: items = @activity('Get Metadata File').output.childItems and Condition = @equals(item().type, 'Folder')  
         2.6.1) Set variable PathFolder Aux(pathFolderAux): @variables('pathFolder')  
         2.6.2) Set variable PathFolder(pathFolder):

@union(variables('pathFolderAux'),
split(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
string(activity('FilterFolder').output.value)
, ',"type":"Folder"','')
,'"name":',concat(variables('container'), '/'))
,'"','')
,'{','')
,'}','')
,'[','')
,']','')
,',')
)

Share via

How to read files from sub folders in Azure data factory

2 answers

Your answer