Q: Combine csv files to one and add a column to the end of each line.
- I have some .CSV files that we receive and want to combine them all into 1 file.
- We should also add a column to the end of each line, a new piece of data for each record. The extra data to add will depend on the filename of the cvs file.
- If the csv filename contains FILEID1 then we should add FILEID1-DATA.
- If the csv filename contains FILEID2 then we should add FILEID2-DATA.
Is this possible?
A: Yes, this is possible using some custom Pascal Script we created for you.
- Please open our Pascal Script option:
- Add and enable the following ‘On Rule Begin’ Pascal Script. This script will clear string var ( psVSA and psVSE ) when the rule begins.
Begin psExitCode:= 1; // ... add your code here psVSA := ''; psVSE := ''; End.
- Add and enable the following ‘On Rule End’ Pascal Script. Do not forget to adjust the combined output filename.
Var tmpList: TStringList; Const ctOutputFile = 'C:\Test\Out_Csv\MergeCsvFile.csv'; Begin // Init psExitCode:= 1; // ... add your code here If psVSE = '' Then Begin // No Error Occured If Trim(psVSA) <> '' Then Begin tmpList := TStringList.Create; Try tmpList.CommaText := psVSA; Try tmpList.SaveToFile(ctOutputFile); Except Begin psExitCode := 0; psLogWrite(1, '', 'Save To File Exception: ' + ctOutputFile); End; End; Finally tmpList.Free; End; End Else psLogWrite(1, '', 'No csv data to save'); End Else Begin // Error Occured psLogWrite(1, '', 'Error handling csv file(s): ' + psVSE); End; End.
- Add ‘Pascal Script’ as Destination
Var iList: Integer; tmpCount: Integer; tmpList, tmpListMem: TStringList; tmpAddData, tmpEntry: String; Const ctAddHeader = ',"D"'; Begin If psVSE <> '' Then Begin psExitCode:= 0; psLogWrite(1, '', 'Skip File due to previous error: ' + psVSE); End Else psExitCode:= 1; // Set AddData If pos('FILEID1', UpperCase(psFileName)) > 0 then tmpAddData := 'FILEID1-DATA' Else if pos('FILEID2', UpperCase(psFileName)) > 0 then tmpAddData := 'FILEID2-DATA' Else tmpAddData := ''; // Load From File tmpList := TStringList.Create; tmpListMem := TStringList.Create; Try If Trim(psVSA) <> '' Then tmpListMem.CommaText := psVSA; Try psLogWrite(1, '', 'Load From File: ' + psFilePath + psFileName); tmpList.LoadFromFile(psFilePath + psFileName); tmpCount := tmpList.Count; If tmpCount > 0 Then Begin For iList := 0 to (tmpCount - 1) Do Begin tmpEntry := tmpList.Strings[iList]; If iList = 0 Then Begin // Header If Trim(tmpEntry) <> '' Then tmpListMem.Add(tmpEntry + ctAddHeader); End Else Begin // Data If Trim(tmpEntry) <> '' Then tmpListMem.Add(tmpEntry + tmpAddData); End; End; // Update psVSA If Trim(tmpListMem.CommaText) <> '' Then psVSA := tmpListMem.CommaText; End; Except Begin psExitCode := 0; psVSE := 'LoadFromFile ' + psFilePath + psFileName + ' Exception'; End; End; Finally tmpList.Free; tmpListMem.Free; End; End.
If you need any help with this ‘Combine csv files’ request, please let us know.
Best Regards,
Limagito Team
#filetransfer #filemanagement
