combine csv

  • 04 Aug

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

    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:

    Limagito File Mover Pascal Script

    • 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.

    Limagito File Mover On Rule Begin Pascal Script

    • 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.

    Limagito File Mover On Rule End Pascal Script

    • Add ‘Pascal Script’ as Destination

    Limagito File Mover Destinations

    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.

    Limagito File Mover combine csv files Pascal Script

    If you need any help with this ‘Combine csv files’ request, please let us know.

    Best Regards,

    Limagito Team

    #filetransfer #filemanagement