SFTP->Snowflake: get source (file) name

Discussions
1

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!

2

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

3

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

4

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?

image
image2144Ɨ324 23.8 KB

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

Thanks!

5

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

6

Oh ok, thanks for making the requests

7

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

8

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
9

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