convert xls`

  • 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

SEARCH