How to Load Multiple CSV Files to Multiple Tables According to File Name in Azure Data Factory

Issue: How to Load Multiple CSV Files to Multiple Tables According to File Name in Azure Data Factory

In this article we are going to learn how to load multiple .csv files to different tables according to the file name in the Azure data factory, Each file will be loaded to a separate table according to File Name. If the table already exists, then it will be loaded without dropping and recreating. IF a table does not exist, then it will create a new table and then load the data from CSV File.

How to create a pipeline:

Go to the Azure Data Factory, then click on Author tab, then click on + Button to create a new pipeline.



Then find and drag the Meta Data Activity.


Click on the Get metadata activity, then go to the Dataset, and click on the + New button to create a new dataset.



Find and select the Blob storage and then click on continue.


Then select the file format which is .CSV and then click on continue.

Name the dataset then select the linked service if you already created, otherwise create a new linked service and select, then select the file path from where you have to input your files, then select the none for import schema and then click on ok.




Click on field list, then select the child items to get the list of files.



Now find and drag the foreach loop activity, then connect it with the Get Metadata activity.


Click on Foreach loop activity, then go to the settings tab and then click on Add dynamic content.



Click on the Getmetdata to add the expressions, then after output add .childitems and then click on finish.


Click on the pencil sign and it will bring you inside the Foreach loop activity.



Inside the Foreach loop activity, find and bring the copy data activity, then go to the source tab and then click on the + New button to create a new dataset.


Select the Azure blob storage and then click on continue.


Then select the CSV file as file format and then click on continue.


Name the dataset, then select the linked service which we have selected earlier, then select file path, then select none for import schema, and then click on ok.




Inside the Source tab click on the open button then create a parameter and then select that parameter in the file name tab.


Now inside the source tab click on the value tab to add the dynamic content.


Then go to the sink tab and click on the + New button to create a new sink dataset.


Then select the Azure SQL Database and click n continue.


Name the sink dataset, then select the linked service, if already created or create a new linked service then select none for import schema and then click on ok.



Click on the Open and then create a parameter for the table name which will come from our foreach loop.


Now go to the file name tab then click on the Add dynamic content and then select the dynamic content and click on finish



Then select none as we don't have any store procedure, then click on Auto-create table.



Then go back to the pipeline and Debug.




Video Demo: How to Load Multiple CSV Files to Multiple Tables According to File Name in Azure Data Factory.


2 comments:

  1. very nice post, Thanks for sharing great content, do my homework assignment online There are two ways of loading data in Azure Data Factory: using a script or using a table. In the first case, we use a script that will be executed when the job is run.

    ReplyDelete
  2. A task is an errand or a piece of work assigned to somebody as a feature of a task or course of study. The tasks assist understudies with paid research paper learning in a scholarly setting and in the process honing the author's exploring and composing abilities and widening their comprehension on the issue or subject investigated on.

    ReplyDelete