We recieve a .csv file direct from a client. It has column that is the file name, but lacking the .pdf extension. My workflow works fine if I edit that column to add the extension, so I'm looking for the most straightforward way to automate adding that.
Appreciate any help,
In Excel, one could create another column and use the formula =A1&".pdf". This assume that the filename data is in column 1 and starts in row A. See below. The formula needs to be "dragged" the length of the new column.
This can be done with a script on the hot folder. If you have csv that looks like this:
the attached Python script will add the .pdf extension like this:
If you want to give this a try then first install Python https://www.python.org/. You then need to install pandas with Python. From cmd as admin you can use "pip install pandas".
Upload both scripts (bat + py) to the FFCore scripts folder. In the bat file you likely need to change the path to where Python is installed as well as the path to the FFCore scripts folder.
Set the FFCore hot folder to "Execute Script Prior to Workflow" and select the bat file. Use the default parameters "$FFin$" "$FFout$"
You likely need to do the changes to the py file depending on your csv. This line controls how the file is read:
df = pd.read_csv(sys.argv, sep = ';', header=None)
This uses ; as delimiter and assumes there is no header. If you have some other delimiter, for instance | and a header, change to this:
df = pd.read_csv(sys.argv, sep = '|')
The column to be added with the .pdf extension is controlled by the line:
df.iloc[:,1] = df.iloc[:,1]+'.pdf'
This is the second column. If you need to add this to the fifth column for instance, then change to:
df.iloc[:,4] = df.iloc[:,4]+'.pdf'
The writing of the new csv is controlled by the line:
df.to_csv(outfile, sep = ';', index=False, header=None)
You may need to change delimiter and add a header. Example:
df.to_csv(outfile, sep = '|', index=False)
The script also uses a temp folder which is "C:/Temp/". You can use some other folder for this if you like. Use forward slashes for this. Example: D:/mytempfolder/