BTEQ Export - Odd File Output

Database
Enthusiast

BTEQ Export - Odd File Output

.run FILE c:\some_dir\logon.txt

.SET WIDTH 2000
.SET Titledashes OFF
.SET Separator '|'
.SET RECORDMODE OFF

.EXPORT REPORT FILE=\share_drive\file_name.dat

SEL
  A.date
, A.time_space
, a.cust_ids
, A.local
, COUNT(A.pi_col) "count"
FROM    database.table_name A
WHERE A.date = DATE -3
GROUP BY 1,2,3,4
;

.EXPORT RESET

.Logoff

Hello, I am extracting a flat file from a Teradata table with BTEQ. The table is fairly wide, the query is pulling back 5 columns. The output file, a pipe delimited looks ok after I bring into Excel, but in doing so, I can the flat file layout is odd, and a huge space bwtween column 4 and the 5th column. If I dont give it a huge width like 2000, then the 5th column won't even make it into the flat file.

I've tried a varirty of Width values, added Pagebreaks, and such, and nothing seems to align the raw file output of the file to layout and be spaced properly as expected.

What are some SET Formats that I can help cleanup the raw file output? My concern is other systems that process this into a table will mishandled the wide spaces. 

I'm running this from a Win 7 PC, with TTU14. Please let me know what additional details I can share.

Thanks

4 REPLIES
Enthusiast

Re: BTEQ Export - Odd File Output

Ive tried quite a nubmer of format variations, including writing to  local drive opposed to a share drive, and even created the query as a volitile table, and casted the columns to eliminate varchar in a select to write to the file, and I still get the result of what seems to be doubleing. If I pull from a column that is char(6), then in the flat file it creates 12 spaces.

I'm doing an EXPORT REPORT c:\filepath\filename

and the formattings are:

.set width 600

.set titlesdashes off

.set sidetitles off

.set format off

.set recordmode off

.set seperator '|'

Any ideas?

Enthusiast

Re: BTEQ Export - Odd File Output

I've also tried to clear out extra space from the VT with the query used to write to the flat file, with the same double spaced results in the flat file.

SEL

  CAST(date_col AS  DATE FORMAT 'yyyy-mm-dd') AS date_col

, SUBSTR(TRIM(time_space),1,CHAR_LENGTH(TRIM(duration))) AS time_space

, SUBSTR(TRIM(cust_id),1,CHAR_LENGTH(TRIM(agentid))) AS cust_id

, SUBSTR(TRIM(local),1,CHAR_LENGTH(TRIM(Location))) AS local

, SUBSTR(TRIM(counts),1,CHAR_LENGTH(TRIM(chatcount))) AS counts

FROM vt_table

GROUP BY 1,2,3,4,5

 ;

Enthusiast

Re: BTEQ Export - Odd File Output

And finally, I cat'd as a single varchar all the columns, and gave it a name, and it writes the file without all the extra unwanted spaces. What a chore. After a lot of code and re-code, to arrive at soemthing seemingly so simple.

Senior Apprentice

Re: BTEQ Export - Odd File Output

BTEQ REPORT is fixed width out for direct printing.

Switching to TPT would really simplify this, FILE_WRITER plus Format = 'DELIMITED' :-)