Last character of the file is not laoding into a table while using bteq script

Tools & Utilities
Enthusiast

Last character of the file is not laoding into a table while using bteq script

Hi,

I am using a BTEQ script to load a table. While loading, last character of the text file is missing. For example, in the text file, last column value of a last row is "Deadline 3", only Deadline is loading. If change the value to "Deadline 33", Deadline 3 is loading. Even if I give space after Deadline 3, it is loading completely. Why is BTEQ missing last character of the file in Loading the table? Please advise...This is pretty urgent.

Thanks,

Chand

6 REPLIES
Teradata Employee

Re: Last character of the file is not laoding into a table while using bteq script

Please supply more details ... What BTEQ version are you using ?

What data format are you using for the IMPORT command ?

What does your USING clause look like ?

Enthusiast

Re: Last character of the file is not laoding into a table while using bteq script

I am using Version is 13.10.01.08.

Bteq Script:

DATABASE ABC;

.IF ERRORCODE <> 0 THEN .GOTO LABEL_ERROR ;

 

.IMPORT VARTEXT ',' FILE=/xfer/BMD/extracts/Forecast.csv, SKIP=1;

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

 

.SET ERROROUT STDOUT

.SET REPEATSTOP ON

.QUIET ON

 

.REPEAT *

.IF ERRORCODE <> 0 THEN .QUIT ERRORCODE;

 

USING        (         A VARCHAR(100)

                                                ,B VARCHAR(100)

                                                ,C VARCHAR(100)

                                                ,D VARCHAR(100)

                                                ,E VARCHAR(100)

                                                ,F VARCHAR(100)

                                                ,G VARCHAR(100)

                      

                     )

INSERT INTO FRS_T.FutsFcst

                                (              A

                                                ,C

                                                ,D

                                                ,F

                                                ,G

                                                ,E

                                                ,CrtdBy

                                                ,CrtTmstmp

                                                ,UpdtdBy

                                                ,UpdtTmstmp

                               

                                )

VALUES                                                                                                               

              (

                                                :A

                                                ,:C

                                                ,SUBSTR(trim((:D)),1,2)

                                                ,:F

                                                ,SUBSTR((:G),10,1)

                                                ,:E

                                                ,USER

                                                ,CURRENT_TIMESTAMP(0)

                                                ,USER

                                                ,CURRENT_TIMESTAMP(0)

                                ) ;

.QUIT

.IF ERRORCODE <> 0 THEN .GOTO LABEL_ERROR ;

.LOGOFF ;

.EXIT ;

 

Sample Data:

A             ,B            ,C            ,D            ,E            ,F            ,G

3,1001,2000,10   ,52915745.00,2012SP,Due 3

3,1001,2000,20   ,329579468.00,2012SP,Due 3

3,1001,2000,30   ,1525491.00,2012SP,Due 3

In the target, last row last field last char is not loading. In this case it is loading as Due (Should load as Due 3)

Thanks,

Chand

Teradata Employee

Re: Last character of the file is not laoding into a table while using bteq script

I'm understanding you to be saying that its just for the last import record that the value for column G is one less character than it should be.

Please try SELECTing the data rather than INSERTing it and see whether omitting the SUBSTR function has any effect. This will help determine whether its a BTEQ issue or a DBS issue.

Enthusiast

Re: Last character of the file is not laoding into a table while using bteq script

I tried loading the table omitting SUBSTR function, but still I have the issue. If I just hit a enter at the end of last record and save the file, its loading the complete value.

And also, I noticed,  if I am saving the file from excel to csv, I can see an extra line after the last record.

How does BTEQ identify End Of File?

Thanks,

Chand

Teradata Employee

Re: Last character of the file is not laoding into a table while using bteq script

What you've figured out is that to prevent BTEQ from thinking it has reached the end of the file, an EOL sequence must be used on each record. You inserted this sequence when you "hit a enter". This is actually what is expected for the version of BTEQ you are using. Glad to hear you can now get the complete value.

Enthusiast

Re: Last character of the file is not laoding into a table while using bteq script

Thanks Suzzanne