• 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

  • 01 Aug

    How to scan a folder to look for a number of files, then start the transfer?

    Q: How to scan a folder to look for a number of files, then start the transfer? We are a licensed customer of Limagito. Is it possible to scan a folder to look for 6 files, then start the transfer?  If there are less than 6 files, none of the files are transferred.

    A: This is possible with a Windows folder/Share [WIN] as Source. We added some screenshots to get you started.

    • Add Windows folder/share as Source:

    limagito file mover source setup

    •  Open our Pascal Script option:

    limagito file mover pascal script option

    • Please enable and add the following ‘On Rule Begin’ Pascal Script (script attached in txt file). You can modify the amount (ctMinFiles) or search filter (*.*) if needed.
    Var
      tmpCounter: Integer;
    Const
      ctMinFiles = 6;
    Begin
      psExitCode := 0;
      // ... add your code here
      // If you want to scan subirectories
      // tmpCounter := psCountFiles(psSourcePath, '*.*', True);
      // In this example we will not scan the subdirectories of the source folder
      tmpCounter := psCountFiles(psSourcePath, '*.*', False);
      If tmpCounter >= ctMinFiles Then
      Begin
        psLogWrite(1, '', 'Files count successful: ' + IntToStr(tmpCounter));     
        psExitCode := 1;
      End  
      Else
        psLogWrite(1, '', 'Not enough files, count: ' + IntToStr(tmpCounter));     
    End.

    limagito file mover look for a number of files

    • Click F9 and check the log window in the Pascal Script setup. It should show ‘Compiled Successfully’.

    limagito file mover pascal script compile result

     

    If you need any info about this ‘look for a number of files’ question, please let us know.

    Best regards,

    Limagito Team

1 2
SEARCH