Contact Us Now

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

• We added *.xlsx as 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

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

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

• XLS as Destination conversion to txt files:

• Pascal Script as Destination merged data as text file:

• Content of the merged tab delimited txt files:

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

Best regards,

Limagito Team