SFTP->Snowflake: get source (file) name

Hey, all,
does anybody know a possibility to get a file name in a variable? I load the data from 100 zipped csv-files into a table in Snowflake DB. I want to have a name of file in one column of the table.
Any experiences with it? Thanks!

Hi Oleksandra,

This is supported for Excel file formats in SFTP connections. Once you map the columns in the ‘fields mapping’ section, you will see meta data field option in the ‘Source Type’ column where you can pull in the file names. It’s currently not supported for CSVs.

For Snowflake specifically, there could be a workaround leveraging their built-in metadata fields described here: Querying Metadata for Staged Files | Snowflake Documentation. However, this would require that you script this into a logic river step and wouldn’t be supported through the native SFTP connector. More info on Snowflake data loading options here: Summary of Data Loading Features | Snowflake Documentation

Wow! Didn’t expected so much possibilities! I’ll try it and report how it’ll be solved. Thank you very much, @taylor.mcgrath !

Hi @taylor.mcgrath,
I am using the SFTP connection with excel file format but don’t see how to pull in the file names.
In the “Source Type” dropdown menu I have “Source” and “Static”. How can I put the file name as the value in a column?

Also, are you planning to add this option for CSV files? It would be very useful

Thanks!

Hi @monilondo, unfortunately I misspoke here, we only currently support these metadata fields for Excel documents in our Email connector.

I have made the request for SFTP to our product team. We don’t currently have an ETA for CSV metadata, but I can add your request for this as well.

Best,
Taylor

Oh ok, thanks for making the requests

HI Taylor, is there an estimated timeline for having this solution available on metadata fields available for excel via SFTP source? Thanks!

Hey @shirleyhuang , the metadata fields are on our short-term roadmap and will be delivered over the next few months. We are planning to support adding metadata info to the target table from all our storage connectors. such as: file name, folder name, row number, file date etc.

2 Likes

Hey @limor.fledel, is there any information about when the feature will be released?