CSV

  • 12 Nov

    How to merge and add filenames to csv datafiles

    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:

    • BTW_Invites_20231023_1.csv
    • BTW_Reminders_20231023_1.csv
    • BTW_Results_20231023_1.csv

    We used a Windows folder as Source:

    limagito file mover windows folder as source

    We added the following ‘Include Filename’ Filter because we want to be sure to only process .csv files:

    limagito file mover filename filter setup

    As Destination we add our ‘Pascal Script’ option:

    limagito file mover destination setup

    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

    limagito file mover merge and add filenames script

    RunTime Log Result:

    limagito file mover runtime log result

    #csv #filetransfer #filemanagement

    If you need any help with this ‘merge and add filenames’ option, please let us know.

    Best Regards,

    Limagito Team

  • 26 May

    Get a field value to rename a csv file

    Q: Get a field value to rename a file. I received a file with a specific filename every week which goes through various processes, after I have finished I need to send the original data with some extra data columns generated by our process back to the client using the original filename. Unfortunately, I cannot keep hold of the filename throughout the process but I can have a column in the return file which contains the original filename. Is there a way I can use Filmover to have a look at a specific column of a data file get the contents of it and use it to rename the file to that value then deleted the column that I got the information from? Bit complex but I thought I’d ask before exploring other avenues. The column that contains the original filename to be used and then deleted is called IMPNAME.

    A: Should be possible (we received an example file of the customer).

    • Please set filename filter to *.csv

    limagito file mover filename filter

    • Add ‘Pascal Script’ as Destination:

    limagito file mover pascal script as destination

    • Don’t forget to adjust the ctOutputPath Const (must end with a \ )
    Var
      iEntry, iIndex, iList: Integer;
      tmpFilename, tmpInputFile, tmpOutputFile: String;
      tmpEntryList, tmpFileList: TStringList;
    Const
      ctOutputPath = 'C:\Test\Out_Csv\';  // Must end with a \
      ctIMPNAME = 'IMPNAME';
    Begin
      psExitCode := 0;
      iIndex := -1;
      tmpInputFile := psFilePath + psFilename;
      tmpOutputFile := '';
      // ... add your code here
      tmpEntryList := TStringList.Create;
      tmpEntryList.Delimiter := ',';
      tmpEntryList.StrictDelimiter := True;
      tmpEntryList.QuoteChar := '"';
      tmpFileList := TStringList.Create;
      Try
        Try
          tmpFileList.LoadFromFile(tmpInputFile);
          tmpEntryList.DelimitedText := tmpFileList.Strings[0];
          For iEntry := 0 to (tmpEntryList.Count - 1) Do
          Begin
            If SameText(ctIMPNAME, tmpEntryList.Strings[iEntry]) Then
            Begin
              iIndex := iEntry;
              psLogWrite(1, '', 'Found ' + ctIMPNAME + ' at Column with Index ' + IntToStr(iIndex));
              Break;
            End;
          End;
          // Did we find 'IMPNAME'
          If iIndex <> -1 Then
          Begin
            tmpFileList.Strings[0] := psStringReplace(tmpFileList.Strings[0], ',"' + ctIMPNAME + '"', '');
            For iList := 1 to (tmpFileList.Count - 1) Do
            Begin
              tmpEntryList.DelimitedText := tmpFileList.Strings[iList];
              If tmpEntryList.Count > iIndex Then
              Begin
                If tmpOutputFile = '' Then
                Begin
                  tmpFilename := tmpEntryList.Strings[iIndex];
                  If tmpFilename <> '' Then
                    tmpOutputFile := ctOutputPath + tmpFilename + '.csv';
                End;  
                If tmpFilename <> '' Then
                Begin
                  tmpFileList.Strings[iList] := psStringReplace(tmpFileList.Strings[iList], ',"' + tmpFileName + '"', '');           
                  psLogWrite(1, '', 'Entry: ' + tmpOutputFile + ' : ' + tmpFileList.Strings[iList]);
                End;  
              End;
            End;
          End;
          // Save To File
          If tmpOutputFile <> '' Then
          Begin
            Try
              tmpFileList.saveToFile(tmpOutputFile);
              psExitCode := 1;
            Except
              psLogWrite(1, '', 'Error saving file: ' + tmpOutputFile);
            End;
          End;
        Except
          psLogWrite(1, '', 'Error loading file: ' + tmpInputFile);
        End;
      Finally
        tmpEntryList.Free;
        tmpFileList.Free;
      End;
    End.

    Get a field value

     

    #filetransfer #mft #filemanagement #csv

    If you need any info  about this ‘get a field value to rename a file’, please let us know.

    Best regards,

    Limagito Team

    By Limagito-Team CSV ,
  • 07 May

    Copy only the modified contents of a file

    Q: We are using Limagito for different projects and we have a query to its functionality. We want to see that it can copy only the modified contents of a file  in a new file with same name. Below is an example on what is being appended and how.

    Let us use the following example: Equipment_AE_Alarm_Event0_2023-05-04_00.00.01.csv.

    • The original content of this csv file:

    “Time;””AlarTxt””;””AlmPrio””;””Almusr””;””acksts”””
    “2023-05-03 00:00:02;””sample text1″”;200;KLMN;1″
    “2023-05-03 00:00:03;””sample text2″”;300;KLMN;1″
    “2023-05-03 00:00:04;””sample text3″”;100;KLMN;0″
    “2023-05-03 00:00:05;””sample text4″”;200;KLMN;1″
    “2023-05-03 00:00:06;””sample text5″”;200;KLMN;1″

    • Expected content of the output file would be the same when we scan this file for the first time:

    “Time;””AlarTxt””;””AlmPrio””;””Almusr””;””acksts”””
    “2023-05-03 00:00:02;””sample text1″”;200;KLMN;1″
    “2023-05-03 00:00:03;””sample text2″”;300;KLMN;1″
    “2023-05-03 00:00:04;””sample text3″”;100;KLMN;0″
    “2023-05-03 00:00:05;””sample text4″”;200;KLMN;1″
    “2023-05-03 00:00:06;””sample text5″”;200;KLMN;1″

    • Data is being added to this csv so the content of file during the next scan is showing more entries:

    “Time;””AlarTxt””;””AlmPrio””;””Almusr””;””acksts”””
    “2023-05-03 00:00:02;””sample text1″”;200;KLMN;1″
    “2023-05-03 00:00:03;””sample text2″”;300;KLMN;1″
    “2023-05-03 00:00:04;””sample text3″”;100;KLMN;0″
    “2023-05-03 00:00:05;””sample text4″”;200;KLMN;1″
    “2023-05-03 00:00:06;””sample text5″”;200;KLMN;1″
    “2023-05-03 00:00:06;””sample text6″”;200;KLMN;0″
    “2023-05-03 00:00:06;””sample text7″”;200;KLMN;1″
    “2023-05-03 00:00:08;””sample text8″”;200;KLMN;0″
    “2023-05-03 00:00:10;””sample text9″”;200;KLMN;1″
    “2023-05-03 00:00:12;””sample text10″”;200;KLMN;0″

    • Expected content of the output file after the second scan should be:

    “Time;””AlarTxt””;””AlmPrio””;””Almusr””;””acksts”””
    “2023-05-03 00:00:06;””sample text6″”;200;KLMN;0″
    “2023-05-03 00:00:06;””sample text7″”;200;KLMN;1″
    “2023-05-03 00:00:08;””sample text8″”;200;KLMN;0″
    “2023-05-03 00:00:10;””sample text9″”;200;KLMN;1″
    “2023-05-03 00:00:12;””sample text10″”;200;KLMN;0″

     

    A: This should be possible using our scripting option. We created a script that will keep the data of the csv files it already handled in a separate folder (= memory folder). These memory files will be used during next scans to only create output files where the content has new data.

    For the test, we added 3 folders:

    • Input
    • Memory
    • Output

    Be sure the ‘Memory’ folder is a local folder on the system where our File Mover is running.

    Modified contents of a file example

    Our Source will be the Input folder:

    Limagito file mover using WIN as source

    We added ‘*.csv’ as Filename Include filter because we only want to scan for csv files:

    limagito file mover filename filter

    Optional: we changed the ‘Source File Sort Order’ option to ‘File last write date ascending (Old to New)’. This will handle the oldest csv files first.

    limagito file mover scan older files first

    The function is set to ‘Copy Files’:

    limagito file mover function setup

    As Destination we’ll use our ‘Pascal Script’ option:

    You’ll need to adjust the following 2 constant values ( always end the constant with a \ ):

    • ctMemPath ( = path were we’ll keep our memory data )
    • ctOutPath ( = output path)

    We added a timestamp to the output filename. The format we used is ‘YYYYMMDDHHNNSS’. The reason we did this is to prevent data loss (in case the next system would not pickup the output files).

     

    Var
      tmpEntry, tmpText, tmpFileIn, tmpFileMem, tmpFileOut, tmpFileExtIn, tmpFileExtOut: String;
      tmpList, tmpListMem: TStringList;
    Const
      ctMemPath = 'C:\Test\Astra\Memory\';
      ctOutPath = 'C:\Test\Astra\Output\';
    Begin
      psExitCode:= 0;
      tmpFileIn := psFilePath + psFileName;
      tmpFileMem := ctMemPath + psFileName;
      tmpFileExtIn := ExtractFileExt(psFileName);
      tmpFileExtOut := '.' + FormatDateTime('YYYYMMDDHHNNSS', Now) + tmpFileExtIn;
      tmpFileOut := psStringReplace(psFileName, tmpFileExtIn, tmpFileExtOut);
      tmpFileOut := ctOutPath + tmpFileOut; 
      // Create Var
      tmpList := TStringList.Create; tmpListMem := TStringList.Create;
      Try
        Try 
          tmpList.LoadFromFile(tmpFileIn);
          // Check if file is already available in Memory folder
          If tmpList.Count > 1 Then
          Begin
            tmpEntry := tmpList.Strings[0]; tmpText := tmpList.Text; 
            If FileExists(tmpFileMem) Then
            Begin
              psLogWrite(1, '', psFileName + ' available in memory folder: ' + ctMemPath);                 
              Try
                tmpListMem.LoadFromFile(tmpFileMem);                          
                tmpList.Text := psStringReplace(tmpText, tmpListMem.Text, '');
                // tmpList.Insert(0, tmpEntry);
                psExitCode := 1;         
              Except
                psLogWrite(1, '', 'Error loading data from: ' + tmpFileMem);
              End;          
            End Else
            Begin
              psExitCode := 1;
              psLogWrite(1, '', psFileName + ' not available in memory folder: ' + ctMemPath);               
            End;  
            If tmpList.Count > 1 Then
            Begin  
              // Save to output folder
              If psExitCode = 1 Then 
              Begin
                psExitCode := 0;
                Try
                  psLogWrite(1, '', 'Saving data to: ' + tmpFileOut);       
                  tmpList.SaveToFile(tmpFileOut);
                  psExitCode := 1;
                Except
                  psLogWrite(1, '', 'Error saving data to: ' + tmpFileOut);
                End;  
              End;
              // Save to Memory folder      
              If psExitCode = 1 Then
              Begin
                psExitCode := 0;
                Try
                  psLogWrite(1, '', 'Saving data to: ' + tmpFileMem);
                  tmpList.Text := tmpText; tmpList.Delete(0);       
                  tmpList.SaveToFile(tmpFileMem);
                  psExitCode := 1;
                Except
                  psLogWrite(1, '', 'Error saving data to: ' + tmpFileOut);
                End;          
              End;
            End;        
          End Else
            psLogWrite(1, '', 'No data available in: ' + tmpFileIn);   
        Except
          psLogWrite(1, '', 'Error loading data from: ' + tmpFileIn);
        End;
      Finally
        tmpList.free; tmpListMem.Free;
      End;
    End.

    limagito file mover pascal script as destination

    This blog article is just a basic example of the customers request and can be tweaked so that it meets your requirements. Just contact us if you need help.

    Don’t forget to add an extra Rule which will cleanup the Memory folder on regular basis.

    #FileTransfer #csv #scada #MFT

    If you need any info  about this ‘Copy only the modified contents of a file’ request, please let us know.

    Best regards,Limagito Team

SEARCH