How to add file_creation_date (from file properties) as new column in file

[Sergi1]

I am trying to append a bunch of excel files from a folder to one table in SQL Server.

I have managed to get that working.

But, I want to add the file's creation date (tFileProperties calls it "mtime") as a new column in SQL Server's target table.

So, for example, say we have 10 excel files (excel_1, excel_2, etc.), each with 3 cols and 1 row.

excel_1 will be appended to SQL with 3 columns, but I want a 4th column with the file's timestamp.

Example of target result: (assume each row is an excel file with 1 row)

col_1 col_2 col_3 file_timestamp

1 Sara Collins 9800€ 2021060715000

2 Mark Bean 7600€ 2021060718300

...

I uploaded the working flow without extra column and the flow that gets the time column.

I somehow need to merge those two. I have tried many things, including tMap.

Please help if you have any ideas!!!

[Anonymous]

Hi

You need to use tFileProperties component to read the file properties and store the mtime value to global variable for used later, eg:

tFileList--iterate--tFileProperties--main--tJavaRow--oncomponentok--tFileInputExcel---tMap---tLogRow--tDBOutput

on tJavaRow:

globalMap.put("mtime",input_row.mtime);

on tMap, add a new column and set its value as:

(Long)globalMap.get("mtime")

Regards

Shong