BTEQ Export file with a date variable included in the name

Database

BTEQ Export file with a date variable included in the name

Hi folks,

I have a BTEQ script that will be run daily and will write the output to an export file on a different drive. I need to include a date parameter in the export file name, so that the date the job ran, will be included in the export file name.

Below shows an example of what I am trying to achieve with my BTEQ script. The $date is the date variable.

.logon test/xxxxxx,*****;

.EXPORT REPORT FILE = teradata_output_$date.csv

.set recordmode OFF;

.SET TITLEDASHES OFF;

.SET WIDTH 65531;

.set separator ',';

SELECT * FROM Database.Table;

I have searched the manuals and the web on how to do this, but have had no luck so far. Can anyone suggest how to go about including the date in the export file name using SQL?

Thanks

Tags (1)
8 REPLIES
Enthusiast

Re: BTEQ Export file with a date variable included in the name

Before logon , you can do thus in Linux/unix:

process_date=$(date "+%Y%m%d%H%M%S")

then ..... = teradata_output_${process_date}.csv.....

Re: BTEQ Export file with a date variable included in the name

Thanks Raja. That works, but I thought there might be a way of doing it in BTEQ itself

Re: BTEQ Export file with a date variable included in the name

Hi Raja,

I tried the same...But, it did not work for me.Please find the below code.Thanks !

.SET SESSION TRANSACTION BTET

.os FIL_NAM=$(date "+%Y%m%d")

.RUN FILE $bteq/logon_DDG.bteq;

.SET ERROROUT STDOUT

.SET WIDTH 2000

.SET FORMAT OFF

.SET TITLEDASHES OFF

.SET HEADING OFF

.SET SEPARATOR ' '

.EXPORT REPORT FILE=$dbvenv/perl/DDG/data/outbound/DW_ID_Theft_ID_THEFT_FIRE_PIF_MONTHLY_REPORT-$FIL_NAM.txt

Re: BTEQ Export file with a date variable included in the name

Hi,

i tried the script on the windows server, but it did not work,

can you please tell me how I can assign date to the exported file name in windows is using Bteq

 

regards

hilal 

 

 

Highlighted
Teradata Employee

Re: BTEQ Export file with a date variable included in the name

You can do it in a .bat or .cmd file.  For example:

 

@set FILENAME=MyFile_%DATE:~10%%DATE:~4,2%%DATE:~7,2%.txt
@echo .logon tdp/userID,Password > temp.btq
@echo .export report file=%FILENAME% >> temp.btq
@echo select * from test; >> temp.btq
@echo .export reset >> temp.btq
@echo .quit >> temp.btq
bteq < temp.btq
@del temp.btq

 

This creates a file name of the form MyFIle_20170518.txt

Re: BTEQ Export file with a date variable included in the name

Thanks GJcoleman,

 

I tried to replicate the script provided by you on our server using batch .bat file.

It executes without any error, but does not create any output file.

I think there is something missing in the code or I am not able to run it properly.

Need your support .

 

Thanks and best Regards

 

Hillal

Teradata Employee

Re: BTEQ Export file with a date variable included in the name

It worked for me just as shown here.  When you run this you should see the bteq messages on the screen.  Did bteq return any rows?  Also, if you remove the @ symbols fom the beginnngs of each line, you will see each line as it executes, and then perhaps you will see something odd.

Re: BTEQ Export file with a date variable included in the name

Dear GJcoleman,

 

Followed your instructions seems, and found out that I am unable to logon to the database.

fixed the problem, and the exported the data for the small test table.

 

Now I am going to use it for the real job, In case of any problem, I might request your support.

Thanks and really appreciate your support.

 

Regards

Hilal