All Forums Tools
01 Aug 2014
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); 
 
 
 
 
 
 

dnoeth 4628 posts Joined 11/04
02 Aug 2014

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))

Dieter

02 Aug 2014

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.

02 Aug 2014

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.

02 Aug 2014

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.

dnoeth 4628 posts Joined 11/04
03 Aug 2014

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.
 

Dieter

04 Aug 2014

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.

You must sign in to leave a comment.