Read Files from Folder and Subfolders Using Wildcards + Add FileName & FilePath By using Serverless SQL Pool | Azure Synapse Tutorial for Data Engineers

📂 Read Files from Folder and Subfolders Using Wildcards + Add FileName & FilePath | Azure Synapse

In Azure Synapse Analytics, you can use wildcards like * and ** inside OPENROWSET to read multiple CSV files from a folder or even nested subfolders. This feature simplifies data ingestion and reduces manual work in scenarios with partitioned or log-based folder structures.

✅ Example 1: Read All CSV Files from a Folder

SELECT *
FROM OPENROWSET(
    BULK 'https://techbrotherssynapsestg.dfs.core.windows.net/synpasecontainer/input/*.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS [result];
  

📁 Example 2: Read from Subfolders

Use the wildcard pattern in subdirectories to read files recursively:

SELECT *
FROM OPENROWSET(
    BULK 'https://techbrotherssynapsestg.dfs.core.windows.net/synpasecontainer/Region/**/*.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS [result];
  

🧠 Adding File Name and File Path to Results

You can also include the full path and filename using filepath() in WITH clause:

SELECT
    filepath() AS [FilePath],
    *
FROM OPENROWSET(
    BULK 'https://techbrotherssynapsestg.dfs.core.windows.net/synpasecontainer/Region/**/*.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS [result];
  

💡 Benefits

• Zero-code pattern to ingest massive folder structures
• Perfect for event-driven and incremental datasets
• Supports Power BI, Dataflows, and external table scenarios

📺 Watch the Full Tutorial

Credit: This article was assisted by ChatGPT and Gemini for technical drafting and formatting.