Parse a text file using batch script and remove the first 2 characters from each line

Question

I have been trying to parse the data from a text file that is generated by Teradata fast export utility. The data looks like this:

 Type2LRF|84|249
 Job3|86|327
 StageTOStageBackUp|85|327

When I have checked the character count of the garbage characters that is there is initially, it is 2. I have been trying to parse the text file to remove the first 2 characters and generate a new text file out of it.

The new file should look like this:

Type2LRF|84|249
Job3|86|327
StageTOStageBackUp|85|327

I am trying to add the first 2 characters but they are not appearing correctly in the above block.

The Teradata fast export code that I am using is:

.LOGTABLE Informatica_Test.JobControlExport_log;  
.LOGON server_name/dbc,dbc;  
   DATABASE Informatica_Test;  
   .BEGIN EXPORT SESSIONS 2;  
      .EXPORT OUTFILE "data.txt" 
      MODE RECORD FORMAT TEXT;
     
SELECT ((TRIM((COALESCE(J.JobName,''))))
 ||'|'||
(TRIM((COALESCE(JC.JobControlID,''))))
 ||'|'||
(TRIM((COALESCE(JC.Success_Source_Rows,''))))
)(TITLE '') from
Informatica_Test.JobControl JC
JOIN Informatica_Test.Job J 
ON J.JobID = JC.JobID
JOIN Informatica_Test.BatchControl BC 
ON BC.BatchControlID = JC.BatchCtrlID
where BC.BatchID = 1 -- This will be a parameter   
and BC.EndDatetime = (select max(EndDatetime) from Informatica_Test.BatchControl);

   
   .END EXPORT;
.LOGOFF;

@echo off
setlocal enabledelayedexpansion
break>test.txt
for /F "tokens=*" %%A in (data.txt) do (
 set line=%%A
 echo !line:~2! >>test.txt
)

I have tried the above code for removing the 2 characters.

Your exported data is VARCHAR so the first two bytes are the binary length of the string. Rather than trying to patch it up on the client, use TPT (Teradata Parallel Transporter) to export as delimited text. Or better yet, since it appears the number of rows exported will be very small, use BTEQ export (in report format /field mode). — Fred, Feb 23 '21 at 18:32
@Fred, it would be helpful if you could write that up as an answer. — jwdonahue, Feb 23 '21 at 23:36
Thanks @Fred. You have exactly pointed to the root cause and it helped. — Teja Goud Kandula, Feb 24 '21 at 14:55

score 3 · Accepted Answer · answered Feb 24 '21 at 16:53

Your exported data is VARCHAR so the first two bytes are the binary length of the string. Instead of parsing/fixing the FastExport output file, use a different tool to export the data.

For larger numbers of rows, use Teradata Parallel Transporter (TPT) to export as delimited text (without the need for explicit concatenation or changing the file afterwards.

For small numbers of rows, use BTEQ EXPORT with REPORT format.

Parse a text file using batch script and remove the first 2 characters from each line

1 Answers1