Hello
I need the creation date and time (createdAt) of a CSV file within the MDW table as a column. How can I access this information?
Best Regards
Best answer by rory.smith
View originalIs it possible to extract created date for a CSV file?
Userlevel 4
+5
Dear
Can you elaborate on what you;ve tried so far and what exactly is not working out?
What I would do is create a new column with the datetime data type and try to use either cast or format or convert transformations to get the datetime into that column
Hope this helps
= Daniel
Hi
Yes, that would have been easy, but using those methods, I can only obtain the execution date. The issue is that there is no "createdAt" column in the table. I need to retrieve the creation date of the file from metadata.
Userlevel 4
+5
Dear
Right! I understand now. You need the created date from the metadata of the CSV file.
The datetime that the CSV file was created.
I have not implemented this myself. Maybe you can check the CDATA documentation or maybe you could get this data by using a Query Table?
Interested in the fix!
Userlevel 5
+7
Hi
some connectors allow you to add metadata fields which might contain what you need. Otherwise you may need to connect to the same source using another connector. For example, the SFTP connector allows you to see all sorts of metadata for the files, where you may use the CSV connector to actually load the data.
Userlevel 4
+5
It is worth exploring Rory's idea. I know that there are connectors which just connect to the metadata (the data lake connector for instance). Then you can combine the data later. Which is, unfortunately, more work, but potentially worth it.
It would be great if you have the option to add extra with ‘most’ connectors (where applicable).
For some connectors you do get the filename for instance. That is super usefull and nessicary, but there are more metadata fields that could be very useful. (Like the the name of the Table directly into a column which also changes when a change to the table name has been made)
Userlevel 6
+5
Hi
Hi
Userlevel 6
+5
Hi
I have not heard about the option to extract meta data info about files. The only one I know is the option to add the URI as a field.
You mentioned that the files are stored on an FTP server. You could try to connect to the folder with the FTP provider.
It should have the info about what is stored where and when, but it is an entirely different data source, so merging them into the CSV table will be really difficult.
Userlevel 6
+5
Hi
I have reached out to Cdata regarding this, and apart from the suggestions above (e.g. FTP provider), the only way to get the CSV file info using the CSV provider would be using the ListFiles stored procedure, please see Cdata’s response below:
"While it is possible to fetch catalog, schema, tables, and column information using metadata calls, retrieving file-level information through metadata is currently not feasible. To address this need, we have introduced a stored procedure called Listfiles, which can be executed using the following query:”
EXEC Listfiles @mask='<filename.csv>' Unfortunately TimeXtender does not currently support the above query as a query table for an ODX data source, as the query table transfer task will result in the following error, due to a current requirement of a FROM statement in the query table
Please feel free to submit a product idea, similar to the following “CDATA query table: Support for queries without FROM in the query statement”
Reply
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
