You can translate the question and the replies:

CSV Filename Column

I am curious if there is any way to include the filename or part of a filename for CSV files in the base view; something simlar to how the Excel wrapper can provide the sheetname as a column in the base view for an Excel data source. My use case scenario involves using querying for data in multiple CSV files that have the same format. One of our ETL processes creates a CSV file everyday with the timestamp string in the filename in a folder. The information contained in the CSV file represents a snapshot of particular pieces of information. However, a date column is not present in this CSV file and for various reasons we cannot introduce a timestamp column in the CSV file. As these filenames follow a certain pattern I have been able to create a CSV data source (via SFTP) with the file name pattern so that it can pick up multiple files in the folder. What I am hoping to do is somehow have filename or the timestamp portion of the filename as a separate column in the base view for the data source. Here is an example of what I am trying to do: **SampleCSV.20170101.csv** Col1,Col2,Col3 r111,r112,r113 r121,r122,r123 **SampleCSV.20170102.csv** Col1,Col2,Col3 r211,r212,r213 r221,r222,r223 **Expected Base View** | FileName | Col1 | Col2 | Col3 | | -------- | -------- | -------- | -------- | | SampleCSV.20170101.csv | r111 | r112 | r113 | | SampleCSV.20170101.csv | r121 | r122 | r123 | | SampleCSV.20170102.csv | r211 | r212 | r213 | | SampleCSV.20170102.csv | r221 | r222 | r223 | **Limitations** I am not an admin on our enterprise instance. So I am limited by what I can do in the VDP. I am looking for something that comes as close as possible to an out-of-the-box solution. That being said if this absolutely requires a custom wrapper I can make that happen with the admins.
CSV
user
06-09-2017 16:18:30 -0400

4 Answers

Hi, To do that, if you are a user with valid support access I recommend you to download and install the "FileSystem Custom Wrapper" from Denodo Connects which helps to retrieve filename information through local and network folders using the protocol, FTP/ FTPS and SFTP. With this, you will able to create a base view <bv_filename> with all CSV file name information using Delimited file data source pointing to your SFTP. <bv_filename> | Parentfolder | relativepath | filename | | -------- | -------- | -------- | | c:\temp | \logs | SampleCSV.20170101.csv | | c:\temp | \logs | SampleCSV.20170102.csv | Then, create another base view <bv_file_data> to retrieve the column information using interpolation variable , For example: url ftp://<host>/temp/logs/@{filename} Finally, create a join view <bv_final> by combining the views <bv_filename> and <bv_file_data> Besides this, if you do not have access to Denodo Support site you can still develop a custom wrapper and import it into Virtual DataPort to retrieve the filename information. Have a look at Denodo FileSystem CustomWrapper - User Manual and the section "Paths and Other Values with Interpolation Variables" in [Path Types in Virtual DataPort](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/path_types_in_virtual_dataport#paths-and-other-values-with-interpolation-variables) from Virtual DataPort Administration Guide. Hope this helps you!
Denodo Team
08-09-2017 06:11:15 -0400
Thank you for that idea. The *FileSystem Custom Wrapper* is installed in our instance. So setting it up the way you described it was a breeze and I was able to get exactly the result I needed.
user
08-09-2017 10:12:02 -0400
Would this custom wrapper work with CSV files saved in Amazon S3?
user
03-06-2020 05:02:09 -0400
Hi, To read the CSV file from the Amazon s3, I would use the **Denodo** **Distributed File System Custom Wrappe**r because this Custom Wrapper capable of reading several file formats stored in HDFS, S3, Azure Data Lake Storage, Azure Blob Storage, Azure Data Lake Storage Gen 2 and Google Cloud Storage. If you are a valid Support user, the Distributed File System Custom Wrapper can be downloaded from the [Denodo Support Site](https://support.denodo.com/) under the “**Downloads > DenodoConnects**” section. To learn more about how to use the wrapper to connect to data sources like Amazon s3, you can refer to the [Denodo Distributed File System Custom Wrapper User Manual](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20Distributed%20File%20System%20Custom%20Wrapper%20-%20User%20Manual). Hope this helps!
Denodo Team
11-06-2020 06:44:58 -0400
You must sign in to add an answer. If you do not have an account, you can register here