How to merge and add filenames to csv datafiles
Q: I have a new daily job where I will receive 3 CSV files. I need to merge these together and also add a new field to the data that contains the original filename as I need to split them later on in the process and use the original filename to send back to the client. Also, I may not always get all 3 files so the rule needs to work for receiving 1,2, or all 3 files.
We will run the rule at 18:00 for example and process however many files have been uploaded, then run the rule every 1 hour after to pick up any files that the client has uploaded late. The ideal scenario would be for all 3 files to be uploaded by the client by the scan time, but they want us to be able to pick up any files they may upload late.
The data does not contain headers either. I’m sure I have asked this before but I can’t find the solution if I have, I have found similar and have tried to amend it to suit but failed miserably.
A: We asked and received the following example files:
We used a Windows folder as Source:
We added the following ‘Include Filename’ Filter because we want to be sure to only process .csv files:
As Destination we add our ‘Pascal Script’ option:
Destination Pascal Script, do not forget to adjust the ctOutputPath const (Path must end with a \ ).
The script will add the filename of the source csv file to the end of each entry of the merged csv file. The Destination merged csv file is determined by stripping parts of the source filename. In our example it will be: ‘BTW_Merged_20231023_1.csv’.
This script is just an example and it can be adjusted so it fits your needs, just let us know.
You can download the script: here
RunTime Log Result:
#csv #filetransfer #filemanagement
If you need any help with this ‘merge and add filenames’ option, please let us know.