Fastload , A record with data and spaces in File.

Tools
Teradata Employee

Fastload , A record with data and spaces in File.

Hi Everyone,
I need help with a Fastload script. The data of file come with data and spaces.
When I want to convert that varchar to Decimal ocurres an errorcode 2679, because the program want to include spaces in decimal.
My question is, Could be exist the posibility that take out away the spaces since script Fastload?
Below, I leave my code. The problem is in the column "id".

This is the File:
1                  | Example          | 1            | Example 1

 

This is the script FastLoad:

SESSIONS 8;
ERRLIMIT 25000;
.`cat $TLOGON_FILE` ;

RECORD 2;

SET RECORD VARTEXT "|";

DROP TABLE Table1;
DROP TABLE ET_Table1_E1;
DROP TABLE ET_Table1_E2;

CREATE TABLE Table1
(
id DECIMAL(6,0),
Desc VARCHAR(30),
id DECIMAL(6,0),
Desc VARCHAR(30)
)
PRIMARY INDEX (id,id)
;

BEGIN LOADING Table1
ERRORFILES ET_Table1_E1,
ET_Table1_E2;

DEFINE
id (VARCHAR(100)),
Desc (VARCHAR(100)),
id (VARCHAR(100)),
Desc (VARCHAR(100))
file = /ruta/$1;

INSERT INTO Table1
(
id
,Desc
,id
,Desc
)
VALUES
(
:id
,:Desc
,:id
,:Desc
);

END LOADING;
LOGOFF
EOF

 

Thanks!!


Accepted Solutions
Teradata Employee

Re: Fastload , A record with data and spaces in File.

Correct. FastLoad itself does not provide functionality or support SQL that would allow you to remove the TAB characters while loading.

 

(An INMOD or AXSMOD could be written to change records "on the fly" and pass them to FastLoad. But that would be more complicated than the options I listed in my prior post.)

 

1 ACCEPTED SOLUTION
10 REPLIES
Junior Contributor

Re: Fastload , A record with data and spaces in File.

Strange, spaces should not result in an error.

But you might try the TRIM option to remove them:

SET RECORD VARTEXT "|" TRIM BOTH ' ';

Teradata Employee

Re: Fastload , A record with data and spaces in File.

I Appreciate your help.
The error still appears in the Error table, with the ErrorCode 2679.

Tags (1)
  • Tags:
Junior Contributor

Re: Fastload , A record with data and spaces in File.

Maybe it's not a space (hex 20), there are some other characters which look like a space.

Teradata Employee

Re: Fastload , A record with data and spaces in File.

Perhaps the values contain TAB characters or other non-display characters that are not actually spaces. (Check the file or look at the DataParcel values for the rejected rows.)

FastLoad will not be able to convert such values to numeric. You would need to fix the file before loading, or load as character and do some transformation afterwards, or use another tool that supports more transformation on the fly.

 

(I presume you have anonymized your example script above, since you can't actually have two columns with the same name in the same table.)

Teradata Employee

Re: Fastload , A record with data and spaces in File.

Thats Rigth Fred, I have anonymized my example script but I not realized very wel the changes of the names.

 

I think Dnoeth and Fred are right about other characters wich look like a space.

 

 

I still working that out. I could not solve this problem.

If I get any news about this, I comment or if anybody have any type of help about this. Please comment this post.

 

Thanks for the help!

Highlighted
Teradata Employee

Re: Fastload , A record with data and spaces in File.

REGEXP_REPLACE(ID,'\W','') is an example of one way to remove whitespace characters using SQL. But you can't do that within a FastLoad.

 

If you can't fix the input file, you could FastLoad the data to a staging table with a character field for the ID and then remove the offending characters in an INSERT/SELECT. Or you could load the data using MultiLoad / TPT UPDATE or BTEQ import / TPT INSERTER or TPump / TPT STREAM - any of which would allow you to include function calls in the INSERT statement.

Teradata Employee

Re: Fastload , A record with data and spaces in File.

Finally, I found '09'XC' to fix this problem, because was the tabulation.

Do you know how I can erase the tab on the FastLoad script?

 

Thanks!!

 

Teradata Employee

Re: Fastload , A record with data and spaces in File.

Thankiu Fred!!


So, the solucion of '09'XC' in the script Fastload not could be? Thats rigth?

Teradata Employee

Re: Fastload , A record with data and spaces in File.

Correct. FastLoad itself does not provide functionality or support SQL that would allow you to remove the TAB characters while loading.

 

(An INMOD or AXSMOD could be written to change records "on the fly" and pass them to FastLoad. But that would be more complicated than the options I listed in my prior post.)