.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
, COUNT(A.pi_col) "count"
FROM database.table_name A
WHERE A.date = DATE -3
GROUP BY 1,2,3,4
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.
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 '|'
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.
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
GROUP BY 1,2,3,4,5
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.
BTEQ REPORT is fixed width out for direct printing.
Switching to TPT would really simplify this, FILE_WRITER plus Format = 'DELIMITED' :-)