BTEQ Export .MDB

General
Enthusiast

BTEQ Export .MDB

Hello ,

 

I'm using Teradata 15.

To feed some reporting, i need to export tables from Teradata. I prefer to export the tables as mdb files.

Because it's simpler and cleaner for me to import on excel mdb format.

So far i used bteq solution in a .bat file.

This is the querry i used :

.logon ********/*******,***********;
.export report ddname=\\PARDMP14SH\FCPTA2\80-72_MO_OUTCTR\OUTILS DE CONTROLE NEW\EXTRA\2 - Projets\MDC - MDD\MDC - MDD TRANSITOIRE\PROTO\DASHBOARD\KPI_maker\MDB\out.txt
.set format on 
.SET WIDTH 20000;
.Export file = \\PARDMP14SH\FCPTA2\80-72_MO_OUTCTR\OUTILS DE CONTROLE NEW\EXTRA\2 - Projets\MDC - MDD\MDC - MDD TRANSITOIRE\PROTO\DASHBOARD\KPI_maker\MDB\SYSUG_AGG.mdb

SEL * from DB_FTG_SRS_DATALAB.mdc_cobalt_agg
where instid ='SYSUG' and top_typ_vision ='A';

.SET WIDTH 20000;
.Export file = \\PARDMP14SH\FCPTA2\80-72_MO_OUTCTR\OUTILS DE CONTROLE NEW\EXTRA\2 - Projets\MDC - MDD\MDC - MDD TRANSITOIRE\PROTO\DASHBOARD\KPI_maker\MDB\SYSUG_DENOM.mdb

SEL * from DB_FTG_SRS_DATALAB.mdc_cobalt_denom
where instid ='SYSUG' and top_typ_vision ='A';

.SET WIDTH 20000;
.Export file = \\PARDMP14SH\FCPTA2\80-72_MO_OUTCTR\OUTILS DE CONTROLE NEW\EXTRA\2 - Projets\MDC - MDD\MDC - MDD TRANSITOIRE\PROTO\DASHBOARD\KPI_maker\MDB\SYSUG_DETAIL.mdb

SEL * from DB_FTG_SRS_DATALAB.mdc_cobalt_detail
where instid ='SYSUG' and top_typ_vision ='A';

.SET WIDTH 20000;
.Export file = \\PARDMP14SH\FCPTA2\80-72_MO_OUTCTR\OUTILS DE CONTROLE NEW\EXTRA\2 - Projets\MDC - MDD\MDC - MDD TRANSITOIRE\PROTO\DASHBOARD\KPI_maker\MDB\SYSUG_STATS.mdb

SEL * from DB_FTG_SRS_DATALAB.mdc_cobalt_reporting
where instid ='SYSUG' and top_typ_vision ='A';

But it's feeding only the first one and i can not open it after.

 

Best regards,

 

AbbeSoury

8 REPLIES 8
Enthusiast

Re: BTEQ Export .MDB

May be try putting .export reset for each one, before you start doing the new file export to see if this helps.

 

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

Enthusiast

Re: BTEQ Export .MDB

Hello,

 

Thanks for your answer but the mdb files are still not usable..

 

Best regards, 

 

AbbeSoury

Enthusiast

Re: BTEQ Export .MDB

I tried with different bteq export dif/data/report/indicdata modes, Not sure why bteq not export to mdb/accdb file format properly, it always turns unrecognized database format error on try to open exported file.
Thus i exported to csv/text and then converted it to mdb file format.

File -> Get External Data -> Import. In the dialog box, under Files of type select Text Files, then select the desired textfile, click Import, and follow the wizard.

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

Ambassador

Re: BTEQ Export .MDB


@sandeep_ganga wrote:

I tried with different bteq export dif/data/report/indicdata modes, Not sure why bteq not export to mdb/accdb file format properly


Well, MDB format is none of those dif/data/report/indicdata modes.

If you simply use .PDF as file extension you're not creating a PDF file.

 

But SQL Assistant offers MDB-export and can be used in command-line mode, see Startup Parameters in help.

 

Enthusiast

Re: BTEQ Export .MDB

Export from SQLA worked sucessful, 

 

-e <name>: Export the results of your query to a file named <name>. The format of this file is determined with Export options currently in effect.
If the export file is a Microsoft Access database, the file name must be followed by a backslash '\' and the tablename.
For example: -e c:\temp\MyDatabase.mdb\MyTable

 

C:\Users\>sqla.exe -c prod -e C:\Users\test\Downloads\ttyy.mdb\dbcinfo -s "select * from dbc.dbcinfo"

But from commandline with sqla start parameters, its giving error "The filename or directory is invalid.". 

Any ideas?

 

Best Regards,

Sandeep.

 

GANGA SANDEEP KUMAR

Highlighted
Ambassador

Re: BTEQ Export .MDB


But from commandline with sqla start parameters, its giving error "The filename or directory is invalid.". 

It's working for me, double check if the directory actually exists.

Enthusiast

Re: BTEQ Export .MDB

Hello,

 

I can not execute sqla.exe because it is not recognize as intern command. How can I do it ?

 

Does TPT script can create and feed mdb files ?

Best regards,

 

AbbeSoury

Teradata Employee

Re: BTEQ Export .MDB

The .mdb file type is Windows-specific. TPT and other (multi-platform) Teradata tools and utilities support delimited text files (e.g. .csv), but not .mdb.

 

Why not make a connection directly from MS Access (VBA) if you really want the data stored in .mdb?