XLS/CSV

  • 19 Feb

    How to copy a csv file when a specific row exists

    How to copy a csv file when a specific row exists

    Q: My client is looking for advanced file copy functionality and have set of requirements for this process. Appreciate if you can clarify below requirements applicability with Limagito. The file copying should not copy the currently modified csv files. Basically, the only difference in the files that are completed and in “modify” mode is a last row in the file – If this specific row not exists, file still in “modify” mode.

    A: Yes this is possible. We added two examples (scripts) on how to achieve this.

    1. Example 1: will read the csv file and check all rows for a certain keyword

    2. Example 2: will read the csv file and will only check the last row for a certain keyword

    • We used a windows folder as source:

    limagito file mover windows as source

    • Open our ‘Pascal Script’ option:

    limagito file mover pascal script option

    • Example Script 1 will read the csv file and check all rows for a certain keyword ( RT_Count )
      • Please enable and add the following ‘On Destination’ Pascal Script: link
      • You can adjust the Keyword but always in Uppercase

    limagito file mover pascal script example

    • Example Script 2 will read the csv file and will only check the last row for a certain keyword ( RT_Count )
      • Please enable and add the following ‘On Destination’ Pascal Script: link
      • You can adjust the Keyword but always in Uppercase

    limagito file mover pascal script example

    • As Destination we also used a windows folder:

    limagito file mover windows folder as destination

    • RunTime Log Result:

    limagito file mover runtime log result

    If you need any help with this ‘copy files in a specific order’ request, please let us know.

    Best Regards,

    Limagito Team

    #csv #managedfiletransfer #filetransfer #filemanagement

  • 08 Sep

    How to use a wildcard in the excel sheet name

    Q: How to use a wildcard in the excel sheet name. I am using the below to split and convert the tabs out of a spreadsheet but every now and again the client gets the sheet name slightly wrong.

    wildcard in the excel sheet name

    Can you use a wildcard in the sheet name option?

    Example:

    2 Sticker*

    A: Added on request and available in v2023.8.12.0

    Example where we used: Page*

    wildcard in the excel sheet name

    If you need any info about this ‘wildcard in the excel sheet name’ request, please let us know.

    #excel #filetransfer #filemanagement

    Best regards,

    Limagito Team

    By Limagito-Team XLS/CSV , , ,
  • 04 Aug

    How to convert xls to tab delimited txt files

    Q: We need to convert xls to tab delimited txt files. The resulting tab delimited txt files should be merged without the header data of the xls file. There will be always three files that need to be merged.

    Example:

    • Accounts_CO-en-us_08042023_009004.xlsx
    • Accounts_NE-en-us_08042023_009007.xlsx
    • Accounts_WY-en-us_08042023_009012.xlsx

    Result should be a txt delimited file:

    • Accounts_WY-en-us_08042023.txt

    A: This is possible using the following setup:

    • Source can be any type. We used a Windows folder as Source

    limagito filemover windows folder as source

    convert xls to tab delimited source files

    • We added *.xlsx as include filename filter:

    limagito file mover include filename filter

    • In this example we used two Destinations
      • First Destination (ID1) is our XLS option which will convert the xlsx file to a tab (#09) delimited txt file
      • Second Destination (ID2) is our Script option and this will:
        • check if the files we need are available
          • we’ll use a filter based on the current date (filter format MMDDYYYY)
          • If we find three files then we are ready to merge, otherwise > error
        • when available will
          • load and merge them, without the header, together
          • save the resulting merged txt file

    limagito file mover destination options

    • XLS Destination Setup:

    Important: The “Destination Directory” in the XLS setup will be used in the second destination (Pascal Script, const ctSearchPath). They both need to be the same., because the script needs to search this “Destination Directory” for converted files.

    limagito file mover xls as destination setup

    limagito file mover xls as destination setup

    • Pascal Script Destination Setup (you’ll need to adjust the ctSearchPath and ctMergedPath):
    Var
      iFilesList, tmpPos: integer;
      tmpFilter, tmpFile, tmpData, tmpOutputName: String;
      tmpFilesList, tmpList: TStringList;  
    Const 
      ctDateMask = 'MMDDYYYY';
      ctSearchPath = 'C:\Test\PVBank\Output\';
      ctMergedPath = 'C:\Test\PVBank\Merged\';  
    Begin
      // Create Var
      tmpList := TStringList.Create;
      Try
        // Init Var
        psExitCode:= -1;
        tmpData := '';
        // Accounts_CO-en-us_08042023_009004
        tmpFilter := FormatDateTime(ctDateMask, Now);
        tmpFilesList := psListFilesEx(ctSearchPath, '*' + tmpFilter + '*', False);
        // Check Qty Files
        If tmpFilesList.Count = 3 Then
        Begin
          psLogWrite(1, '', 'We have found The necessary files using filter: ' + tmpFilter);
          For iFilesList := 0 to (tmpFilesList.Count - 1) Do
          Begin
            Try
              tmpList.Clear;
              tmpFile := tmpFilesList.Strings[iFilesList];
              Try
                tmpList.LoadFromFile(tmpFile);
              Except
                psLogWrite(1, '', 'Exception loading txt file: ' + tmpFile);
              End;
              If tmpList.Count > 0 Then
              Begin
                tmpList.Delete(0);
                tmpData := tmpData + tmpList.Text;
                If iFilesList = (tmpFilesList.Count - 1) Then
                Begin
                  Try
                    tmpList.Text := tmpData;
                    // Save Merged Result
                    tmpFile := ExtractFilename(tmpFile);
                    tmpPos := Pos(tmpFilter, tmpFile);
                    If tmpPos > 1 Then
                      tmpOutputName := Copy(tmpFile, 1, (tmpPos-1) + length('MMDDYYYY')) + '.txt' 
                    Else
                      tmpOutputName := tmpFile;
                    tmpList.SaveToFile(ctMergedPath + tmpOutputName);
                    psExitCode := 1;
                    psLogWrite(1, '', 'Successfully saved merged data to: ' + ctMergedPath + tmpOutputName);
                  Except
                    psExitCode := 0;
                    psLogWrite(1, '', 'Error saving merged data to: ' + ctMergedPath + tmpOutputName);
                    Break;  
                  End;
                End; 
              End
              Else
              Begin
                psExitCode := 0;
                psLogWrite(1, '', 'Error, found empty txt file: ' + tmpFile);
                Break;  
              End; 
            Except
              Break;
            End;
          End;
        End
        Else
        Begin
          psLogWrite(1, '', 'We have not found The necessary files using filter: ' + tmpFilter);  
        End;
      Finally  
        // Free Var
        tmpFilesList.Free;
        tmpList.Free;
      End;  
    End.

    limagito file mover pascal script as destination setup

    • XLS as Destination conversion to txt files:

    limagito convert xls to tab delimited output

    • Pascal Script as Destination merged data as text file:

    limagito file mover convert xls to tab delimited merged

    • Content of the merged tab delimited txt files:

    limagito file mover convert xls to tab delimited content

    If you need any info about this ‘convert xls to tab delimited’ question, please let us know.

    Best regards,

    Limagito Team

1 2 3 4
SEARCH