All Forums Database
31_resu 38 posts Joined 07/13
08 Feb 2016
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

31_resu 38 posts Joined 07/13
09 Feb 2016

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?
 

31_resu 38 posts Joined 07/13
09 Feb 2016

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
 ;

 
 

31_resu 38 posts Joined 07/13
09 Feb 2016

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.
 
 
 
 

dnoeth 4628 posts Joined 11/04
09 Feb 2016

BTEQ REPORT is fixed width out for direct printing.
Switching to TPT would really simplify this, FILE_WRITER plus Format = 'DELIMITED' :-)

Dieter

You must sign in to leave a comment.