How do I dynamically read multiple file names?

King Java 565

I am trying to read name of about 20+ csv files, and ingest data dynamically.

Below is diagram of how I ingest data from each csv file individually.

User's image

So, I have at least 20+ pipelines for each csv files.

Below is how each csv files' name look like:

User's image

If I have a small character for variation, I think using Parameters like below screenshot would work, but I need to insert long characters for each file.

Example (of short variation that I use for other case): User's image

What is ideal way to ingest about 20+ csv files that have similar pattern of file names, except part like locations (ex: California, Oregon, Arizona etc.) in the middle of file name?

Should I create a csv file that has a list of locations and somehow read it thru inside Parameter values?

phemanth 11,465 Reputation points Microsoft Vendor

2024-11-19T22:31:04.2+00:00
@King Java

Thanks for using Microsoft Q&A forum and posting your query.

To dynamically read and ingest data from multiple CSV files with similar naming patterns, you can streamline your process significantly. Here’s a structured approach :

Use a List of Locations

Create a CSV file (or a simple list) that contains all the location names (e.g., California, Oregon, Arizona). This will allow you to easily iterate through the locations when constructing your file names.

Construct File Names Dynamically

You can use a programming language like Python to dynamically generate the file names based on the locations. Here’s a simple example using Python:

import pandas as pd # List of locations locations = ['California', 'Oregon', 'Arizona'] # Add more locations as needed # Base file name pattern base_file_name = "data_{}_2024.csv" # Adjust the pattern as per your file naming convention # List to hold DataFrames dataframes = [] # Loop through each location to read the corresponding CSV file for location in locations: file_name = base_file_name.format(location) try: df = pd.read_csv(file_name) dataframes.append(df) print(f"Successfully read {file_name}") except FileNotFoundError: print(f"File {file_name} not found.") # Optionally, concatenate all DataFrames into one all_data = pd.concat(dataframes, ignore_index=True)

Parameterize Your Pipeline

If you are using a data pipeline tool (like Apache Airflow, Azure Data Factory, etc.), you can parameterize the location names. This way, you can pass the list of locations as parameters to your pipeline, which will then construct the file names dynamically.

Error Handling

Make sure to include error handling (as shown in the example) to manage cases where a file might not exist. This will help you avoid breaking your pipeline if one file is missing.

Considerations for Scalability

If you anticipate needing to add more locations or file types in the future, consider storing your location list in a database or a configuration file. This will make it easier to manage and update.

Hope this helps. Do let us know if you any further queries.
King Java 565 Reputation points

2024-11-19T23:52:30.65+00:00

@phemanth Thank you so much for your help. I like your approach using Python.

Can you possibly illustrate the pipeline with some image files? I am not sure which Activities I need to use for each steps (like getting data from csv file of list) & run Python code.
phemanth 11,465 Reputation points Microsoft Vendor

2024-11-20T17:40:23.2066667+00:00
@King Java Pipeline Overview

Activity: Copy Data

Use this activity to read the CSV file containing the list of locations. You can set the source to your CSV file and the sink to a temporary storage (like Azure Blob Storage or a Data Lake).

Activity: ForEach

This activity will iterate over the list of locations obtained from the previous step. Inside this loop, you can define the activities to process each location.

Activity: Set Variable

Use this activity to construct the file name dynamically based on the current location. You can set a variable like fileName using an expression that formats the base file name with the location.

Activity: Copy Data

Inside the ForEach loop, use another Copy Data activity to read the CSV file corresponding to the constructed file name. Set the source to the dynamically generated file path.

Activity: Azure Function or Databricks Notebook

If you need to run additional processing with Python, you can use an Azure Function or a Databricks Notebook activity. Pass the data from the previous step as input to your Python code.

Concatenate DataFrames (Optional)

If you are using Databricks, you can concatenate the DataFrames within your Python code after reading all the CSV files.
King Java 565 Reputation points

2024-11-21T00:08:22.2133333+00:00

@phemanth I was able to finish the first part (where it copies data from a csv file to a blob storage), but I am lost on the next steps.

Bottom is screenshot that I have so far:

So, you mentioned about creating "Variable". And you also mentioned about using expression.

I am not sure how to add expression here (inside Variables).

Can you possibly send me screenshots of what you have inside "ForEach1" and "Copy data2"?

If you are good, I would like to have a meeting with you privately to go each step by step or if you have any good YouTube video (I doubt there will be at this details), that would be great.

Bottom is another screenshot of what I have, but I doubt this is correct.

Also, regards to using Azure Function with Python, that is just another option (not required), correct?
phemanth 11,465 Reputation points Microsoft Vendor

2024-11-21T18:10:23.94+00:00
@I am just asking java programming language

Go to the Variables tab in your pipeline.

Click on New to create a variable.

Give it a name (e.g., currentLocation) and set its type (e.g., String).

Using Expressions

To set the value of a variable using an expression:

In the Set Variable activity, select the variable you created.

In the Value field, you can use an expression. For example, if you want to set it to the current location in a loop, you might use:JSON
@item()
This assumes you’re iterating over a list of locations in a ForEach activity.

ForEach Activity

In your ForEach activity:

Set the Items property to the array of locations you want to iterate over.

Inside the ForEach, add activities like Set Variable to set the current location, and then add your Copy Data activity to copy data for that location.

Copy Data Activity

In the Copy Data activity:

Set the Source to point to your CSV file. You can use an expression to dynamically construct the file path using the variable you set earlier:JSON
@concat('path/to/your/csv/'

Set the Sink to your blob storage.
King Java 565 Reputation points

2024-11-21T23:58:55.7666667+00:00

@phemanth

Thank you for your help.

I am confused over where I should use "Set Variable" because on your first reply, I did not see 'Set Variable' on the images. I am not sure where "Set Variable" should be located.

I am guessing we are still doing "ForEach" and put "Copy data" inside, correct?

Can you possibly post some of images (especially where Variable, expression etc. are placed) so that I could replicate?

Bottom is what I have, but I am not sure I am on the right track or not.

Thank you!

Share via

How do I dynamically read multiple file names?

Your answer