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
- check if the files we need are available
- 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
