All Forums Tools
vniri.teradata 18 posts Joined 01/09
12 Mar 2009
skip the header row in the flat file, when importing using FastLoad script

Hi,Can someone pls tell me how to skip the header row in the flat file, when importing using FastLoad script on Unix.ThanksNick --

smarty 14 posts Joined 03/09
02 Apr 2009

Hi,We can eliminate the first record of the flatfile by using a RECORD statement in the fastload script.RECORD 2;The above statement skips the first record in the flat file.Thank You

r123 5 posts Joined 05/09
07 May 2009

Hello,But how to skip the first row so, that it is not checked for data types. Fast load fails when checking first row against table datatypes. I have specified RECORD 2 to skip the column headers. Fast load complains that column name (first row) is too long and can not be inserted into smallint column.0008 RECORD 3;**** 09:57:43 Starting record number set to : 30009 ERRLIMIT 25;**** 09:57:43 Error limit set to: 25 =================================================================== = = = Insert Phase = = = ===================================================================0010 INSERT INTO "T01000_PARTY_DDD" ( Party_Id , Last_Name , Full_Name , Lifecycle_Code ) values ( :Party_Id , :Last_Name , :Full_Name , :Lifecycle_Code );**** 09:57:43 Field too large in vartext data record: 2, field: LIFECYCLE_CODE**** 09:57:43 File record count**** 09:57:43 Value for Record statement may be too large =================================================================== = = = Logoff/Disconnect = = = ===================================================================**** 09:57:43 Logging off all sessions**** 09:57:43 Total processor time used = '0.53125 Seconds' . Start : Thu May 07 09:57:40 2009 . End : Thu May 07 09:57:43 2009 . Highest return code encountered = '12'.**** 09:57:43 FastLoad PausedRegards,Roberts

teradata_chela 1 post Joined 06/11
26 Jul 2011

same problem with me also....it says

07:48:18 Field too large in vartext data record: 2, field: EMP_ID

when i wrote RECORD 2; in my fload script.

please help.....

27 Jul 2011

Chela,

A good thing about a lot of Teradata's errors is that they tell you exactly what is wrong. In this case, your DEFINE section is defining each field in the text file as a certain length, but in the actual text file you have values for those fields that exceed that length.

Go back through your text file to ensure you are defining the fields in it correctly and then resubmit. Hopefully this will clear your issue.

The other possibility is that your DEFINE is indeed correct but you have the datatypes in your table wrong. So, if SMALLINT is failing, increase this to INTEGER, etc...

I hope this helps! Welcome to the Teradata Forums!

Andrew

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

feinholz 1234 posts Joined 05/08
27 Jul 2011

FastLoad does not have the ability to skip header rows.
FastLoad is always looking for records in an input file to adhere to the layout in the DEFINE statement.

--SteveF

01 Sep 2011

Agreed, but then what is the point of having statements like RECORD 2;?  Why have functionality inbuilt that doesn't behave in line with the function?

Whilst I know it does not mean this, this would suggest that loading a flat text file with column headers simply will not work, because the headers will not match up with the (CHAR(x)) settings of the DEFINE statement.  The workaround of not have the column headers is a reasonable one, but will not work for everyone/every application.

It'd be interesting to see this functionality built into Fastload properly and behave consistently.

Andrew

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

feinholz 1234 posts Joined 05/08
01 Sep 2011

The RECORD command was a part of the FastLoad utility many years prior to the addition of VARTEXT support.

(Believe it or not, VARTEXT was not very popular many years ago, all data was in binary format.)

In fact, the RECORD command was originally put into place so that users could test out a small subset of their data prior to running long jobs (they wanted to make sure their binary data would load correctly).

When VARTEXT format was added, the RECORD statement processing was not modified to take into account header rows for delimited data. The original design was to support files that looked like /etc/passwd (not that file, but files with that type of formatting).

We have had very few (hard to believe sometimes) requests of your nature, and at this point in time there are no plans on changing the behavior (technically, our SA/legacy utilities are "capped". meaning no new functionality goes into them except for supporting new DBS features). All new features and functionality go into TPT.

Incidentally, TPT will support the processing you are looking for.

 

--SteveF

09 Sep 2011

Many thanks for that.  Great background to the history of Fastload and loading.

We've not really started using Teradata Parallel Transporter, but I think I may suggest we do so in future.

Thanks again!

Andrew

Andrew Livingston
EMEA - Customer Education Consultant
Teradata UK
206 Marylebone Road
London NW1 6LY
United Kingdom

M: +44 7785 971 080
E-mail: Andrew.Livingston@Teradata.com

feinholz 1234 posts Joined 05/08
14 Sep 2011

The sooner you begin to use TPT the better.

The legacy utilities will never go away, but there are many more features in TPT and TPT is the loading tool going forward.

 

--SteveF

Td12krish 11 posts Joined 10/10
25 Sep 2011

I too faced this kind of issue with header part. As our flat files were in uniz i used SED comand to remove first line from flat file and then loaded into the tables which worked fine.

 

TDkrish

satya345 8 posts Joined 11/11
24 Nov 2011

hi

 

can you please provide script by using sed command?

 

ULICKERT 3 posts Joined 10/09
24 Nov 2011

sed may not be necessary

tail +2 YourFile

can do the job

Srichakra 15 posts Joined 11/11
25 Nov 2011

Hi,

Not only Tail we can use

SED 1d abc.txt also

It will work.

But my requirement is different  Am Exporting data in to flat file by using Bteq .But when i observerd in flat file header also coming ,but i need to remove automatically at the time of laoding only ,I don't want to use SED,TAIL .

Even i tried

Sel Column (Title '')  from table name --its not working.

Pls help me out..

 

kmehra1 2 posts Joined 10/11
29 Nov 2011

Use RECORD or INDICATOR output modes in your BTEQ export.

Srichakra 15 posts Joined 11/11
29 Nov 2011

Hi,

 

we have skip option at the time of Importing and we have limit option while exporting.

 

In your case it is importing so you can directly use skip option to remove header.

 

syntax(import):

.import [DATA/INDICDATA/RECORD/VARTEXT] file='Address' Skip=1;

 

syntex(Export):

.Export [DATA/INDICDATA/RECORD/] file='Address' Limit=1;

 

 

 

Srichakra 15 posts Joined 11/11
07 Dec 2011

I tried record and indicator mode but i didn't get that .

any one help me out.

 

And one more question: In my query am using union all and the table contains 1 crore records ,so it's taking time to fetch the records.

So i tried to place sample 1000 but its saying that " SET OERATIONS NOT ALLOW SAMPLE OPERATION" even i tried random also ,i places sample in 2 places (before union all query and last of second query ).But its giving same error.

So my question is is there any way to use sample or random while using SET operations in a query?

 

bmclare 7 posts Joined 07/11
03 Apr 2012

Does TPT have ability to skip loading the first row of a flat file, like fastload's "record 2" stmt?

feinholz 1234 posts Joined 05/08
03 Apr 2012

Yes

--SteveF

bmclare 7 posts Joined 07/11
03 Apr 2012

If yes, can somebody provide an example? Adding INTEGER RECORD = 2 in datacon operator has no effect. thanks

feinholz 1234 posts Joined 05/08
03 Apr 2012

Please read the TPT documentation at www.info.teradata.com.

All of the proper attributes and their uses and values are provided.

Do not assume that the same command name from FastLoad is the same in TPT.

Thanks!

 

--SteveF

bmclare 7 posts Joined 07/11
04 Apr 2012

Prior to posting my question, I scoured the TPT User Guide 2445 and TPT Reference 2436, but did not find the solution. 

I did find this, which does seem to indicate the ability, but with no details or examples:

FastLoad Utility Feature

RECORD n THRU m

 

Teradata PT Operator Support

Supported, in a limited form by the DataConnector operator,

allowing you to read in the first "m" rows of a file, effectively

allowing "RECORD 1 THRU m"

feinholz 1234 posts Joined 05/08
04 Apr 2012

Look in the Reference manual, in the chapter on the DataConnector operator (the file reader operator) at the SkipRows attribute.

--SteveF

bmclare 7 posts Joined 07/11
04 Apr 2012

Not there; I was looking in version 12 manual as we are actually still on v2r6 using 8.1 tools, getting ready to upgrade to 13.10 shortly. I did just now look in vers 13.10 ref manual and see what you are referring to. So, this is apparently a feature added sometime between 12 and 13.1.  If I use version 13.10 client against v2r6 system, will this feature work?

feinholz 1234 posts Joined 05/08
04 Apr 2012

Yes, you *should* be able to use 13.10 client against V2R6.2.

--SteveF

teradatauser2 236 posts Joined 04/12
05 Apr 2012

use mode : data and below script. it works.

       sel
trim ( cast(
       trim(cast(ACCT_ID as varchar(20))) || '|' ||
       trim (cast(src_ID as varchar(20))) || '|' ||
)

       as varchar(600) ) )  (title '')

   from

      table;

You must sign in to leave a comment.