# Load filename along with data using Azure Blob Storage Load

**URL:** <https://matillioncommunity.discourse.group/t/load-filename-along-with-data-using-azure-blob-storage-load/1736>\
**Category:** Matillion ETL\
**Created:** [April 27, 2022, 6:48pm UTC](https://matillioncommunity.discourse.group/t/load-filename-along-with-data-using-azure-blob-storage-load/1736 "2022-04-27T18:48:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![SR018652](https://avatars.discourse-cdn.com/v4/letter/s/97f17d/32.png) [@SR018652](https://matillioncommunity.discourse.group/u/SR018652)\
**Post date:** [April 27, 2022, 6:48pm UTC](https://matillioncommunity.discourse.group/t/load-filename-along-with-data-using-azure-blob-storage-load/1736/1 "2022-04-27T18:48:32Z")

</div>

Hi All,

I have an ETL, which extracts data from XML files which I am staging in Azure Blob Storage and loads into Snowflake tables. I am using Azure Blob Storage Load and SQL Query components to achieve this.

As there are multiple files in blob storage, I want the filename also to be loaded along with the data in Snowflake tables.

Please help me in achieving this requirement?

&nbsp;

The job runs successfully but now I want filename as well to be loaded in the tables.

&nbsp;

Below is the snapshot of components I am using currently:

&nbsp;

 ![0694G00000Kqk5DQAR_0D54G00008QucaWSAR](https://us1.discourse-cdn.com/flex002/uploads/matillioncommunity/original/1X/ae01af5df673ad97c2e93e6857c639be6314242e.png)

Thank you

---

<div class="post-metadata">

**Author:** ![ChrisHastie-InterWorks](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/chrishastie-interworks/32/41_2.png) [@ChrisHastie-InterWorks](https://matillioncommunity.discourse.group/u/ChrisHastie-InterWorks)\
**Post date:** [April 28, 2022, 11:10am UTC](https://matillioncommunity.discourse.group/t/load-filename-along-with-data-using-azure-blob-storage-load/1736/2 "2022-04-28T11:10:33Z")

</div>

Hey [@SR018652](https://matillioncommunity.discourse.group/u/SR018652)​ , if you want to ingest the filename as well then I would recommend using a SQL component instead to leverage a Snowflake [COPY INTO](https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#copy-into-table) statement. You will need to enter your field names etc manually with **$** references to their ordinal position, but with this approach you can also query system fields like **METADATA$FILENAME** and **METADATA$FILE\_ROW\_NUMBER**. You can read more about this on Snowflake's documentation [here](https://docs.snowflake.com/en/user-guide/querying-metadata.html).

So you may end up with a statement such as the following (apologies for badly formatted SQL in this window):

COPY INTO table1(filename, file\_row\_number, col1, col2)

FROM (

SELECT

METADATA$FILENAME,

METADATA$FILE\_ROW\_NUMBER,

t.$1,

t.$2

FROM @mystage1/data1.csv.gz (file\_format = \> myformat) t

);

---

<div class="post-metadata">

**Author:** ![SR018652](https://avatars.discourse-cdn.com/v4/letter/s/97f17d/32.png) [@SR018652](https://matillioncommunity.discourse.group/u/SR018652)\
**Post date:** [April 29, 2022, 7:22pm UTC](https://matillioncommunity.discourse.group/t/load-filename-along-with-data-using-azure-blob-storage-load/1736/3 "2022-04-29T19:22:40Z")

</div>

Thank you, Chris. I could see the file names in a table. But because of how I am receiving my source files. This process is doesn't result in correct final data.

&nbsp;

Is their any way to store the variable values, which in my case store Filename, from the iterator component to a Snowflake table?

&nbsp;

Thanks.

Shivroopa

---

<div class="post-metadata">

**Author:** ![ChrisHastie-InterWorks](https://sea2.discourse-cdn.com/flex002/user_avatar/matillioncommunity.discourse.group/chrishastie-interworks/32/41_2.png) [@ChrisHastie-InterWorks](https://matillioncommunity.discourse.group/u/ChrisHastie-InterWorks)\
**Post date:** [May 3, 2022, 8:55am UTC](https://matillioncommunity.discourse.group/t/load-filename-along-with-data-using-azure-blob-storage-load/1736/4 "2022-05-03T08:55:46Z")

</div>

Absolutely you can, but you will still need to leverage a COPY INTO statement via a SQL table. You can then simply enter any Matillion variables into your SQL and store them as strings. For example, consider the following SQL:

COPY INTO table1(matillion\_variable\_a, matillion\_variable\_b, filename, file\_row\_number, col1, col2)

FROM (

SELECT

'${MATILLION\_VARIABLE\_A}',

'${MATILLION\_VARIABLE\_B}',

METADATA$FILENAME,

METADATA$FILE\_ROW\_NUMBER,

t.$1,

t.$2

FROM @mystage1/data1.csv.gz (file\_format = \> myformat) t

);