skip the header row in the flat file, when importing using FastLoad script

Tools
Enthusiast

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.

Thanks
Nick

--
25 REPLIES
Enthusiast

Re: skip the header row in the flat file, when importing using FastLoad script

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
Enthusiast

Re: skip the header row in the flat file, when importing using FastLoad script

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 : 3

0009 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 Paused

Regards,
Roberts

Re: skip the header row in the flat file, when importing using FastLoad script

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

Teradata Employee

Re: skip the header row in the flat file, when importing using FastLoad script

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
Teradata Employee

Re: skip the header row in the flat file, when importing using FastLoad script

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
Teradata Employee

Re: skip the header row in the flat file, when importing using FastLoad script

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

Teradata Employee

Re: skip the header row in the flat file, when importing using FastLoad script

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
Teradata Employee

Re: skip the header row in the flat file, when importing using FastLoad script

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

Teradata Employee

Re: skip the header row in the flat file, when importing using FastLoad script

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