BTEQ REPORT EXPORT AND IMPORT

Tools
Enthusiast

BTEQ REPORT EXPORT AND IMPORT

Hello All,

I create a BTEQ Report file. While exporting i converted it to CHAR data type. When importing the exported file. I am getting a BAD CHARACTER ERROR for EmpId. I passing a Fixed Length Data. Each row carries 32 bytes(Counted the characters). Because the data is sent in the char format.

Plz help me understand where i am going wrong.

Below is the complete structure:

Exported:

.run file=logon.txt

DROP TABLE DB.SourceT1;

CREATE MULTISET TABLE DB.SourceT1, NO FALLBACK

(EmpId INT GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 1 CYCLE ),

EmpName CHAR(10),

Salary DECIMAL(7,2),

DeptId BIGINT

)

Primary Index(EmpId);

.IF ERRORCODE=0 THEN .GOTO INS1;

.GOTO Done;

.LABEL INS1

INS INTO DB.SourceT1

SEL * FROM DB.SourceT;

.IF ACTIVITYCOUNT > 0 THEN .GOTO Cont;

.GOTO Done;

.LABEL Cont

.set PAGELENGTH 60;

.set titledashes OFF;

.SET RETLIMIT 1;

.export report file=result;

SEL CAST(EmpId AS CHAR(3)),EmpName,CAST(DeptId AS CHAR(3)) FROM DB.SourceT1;

.export reset;

.LABEL Done

.QUIT;

IMPORT:

.run file=logon.txt;

.import report file=result,skip 1;

.QUIET ON

.REPEAT *

using EmpId (CHAR(7)),

EmpName (CHAR(12)),

Salary (CHAR(10)),

DeptId (CHAR(3))

INS INTO DB.SourceT2 VALUES

(:EmpId,:EmpName,:Salary,:DeptId);

.LOGOFF;

 

Rows inserted

INS INTO DB.SourceT VALUES(,'JugalBhatt',2200,503)

 

 

 

6 REPLIES
Junior Contributor

Re: BTEQ REPORT EXPORT AND IMPORT

When you export in report format there's a .SEPERATOR (by default 2 blanks).

Your number of column and length in EXPORT don't match your IMPORT definition:

CAST(EmpId AS CHAR(3)),EmpName,CAST(DeptId AS CHAR(3)) 

vs.

using EmpId (CHAR(7)),

EmpName (CHAR(12)),

Salary (CHAR(10)),

DeptId (CHAR(3))

Enthusiast

Re: BTEQ REPORT EXPORT AND IMPORT

Hi Dnoeth,

I had oversighted it b4. I later applied the changes to the query.

CAST(EmpId AS CHAR(3)),EmpName,CAST(DeptId AS CHAR(10)),CAST(DeptId AS CHAR(3)).

When i export in the output i could see the result along with the spaces:

EmpId(3 Char+4 Spaces)

EmpName(10Char+2 Spaces)

Salary(8 Char+2 Spaces)

DeptId(3 Char+ 0 Spaces)

So it is a total for 32 bytes. Since i am importing Fixed Length Columns. I had defined the USING Schema accordingly. But the error still persist. Bad Character Error.EmpId.

Thanks,

Jugal.

Enthusiast

Re: BTEQ REPORT EXPORT AND IMPORT

Hi Dnoeth,

I had oversighted it b4. I later applied the changes to the query.

CAST(EmpId AS CHAR(3)),EmpName,CAST(DeptId AS CHAR(10)),CAST(DeptId AS CHAR(3)).

When i export in the output i could see the result along with the spaces:

EmpId(3 Char+4 Spaces)

EmpName(10Char+2 Spaces)

Salary(8 Char+2 Spaces)

DeptId(3 Char+ 0 Spaces)

So it is a total for 32 bytes. Since i am importing Fixed Length Columns. I had defined the USING Schema accordingly. But the error still persist. Bad Character Error.EmpId.

Thanks,

Jugal.

Enthusiast

Re: BTEQ REPORT EXPORT AND IMPORT

Hi Dnoeth,

I had oversighted it b4. I later applied the changes to the query.

CAST(EmpId AS CHAR(3)),EmpName,CAST(DeptId AS CHAR(10)),CAST(DeptId AS CHAR(3)).

When i export in the output i could see the result along with the spaces:

EmpId(3 Char+4 Spaces)

EmpName(10Char+2 Spaces)

Salary(8 Char+2 Spaces)

DeptId(3 Char+ 0 Spaces)

So it is a total for 32 bytes. Since i am importing Fixed Length Columns. I had defined the USING Schema accordingly. But the error still persist. Bad Character Error.EmpId.

Thanks,

Jugal.

Junior Contributor

Re: BTEQ REPORT EXPORT AND IMPORT

Hi Jugal,

your CASTs still don't match, you use DeptId twice:

CAST(EmpId AS CHAR(3)),EmpName,CAST(DeptId AS CHAR(10)),CAST(DeptId AS CHAR(3)).

Regarding the error this looks like you didn't use SKIP 1 and TD tried to cast the string 'EMPID' as an integer.

You might need to show the actual script or the actual BTEQ output.

The 4 spaces for EmpId is due to the column name in the header, if you remove the header using (TITLE '') you will get two blanks, too. And for fixed lenght export you might do .SEPERATOR 0 to remove them.

Enthusiast

Re: BTEQ REPORT EXPORT AND IMPORT

Hi Dieter,

I applied the changes as suggested. Which reduced the spaces. And i had included SKIP 1 in the script.

Thanks for the Help

Jugal.