Contact Us Now

Q: Combine csv files to one and add a column to the end of each line.

1. I have some .CSV files that we receive and want to combine them all into 1 file.
2. 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