All Forums Tools
mrodri81 10 posts Joined 08/10
31 Aug 2010
fastload error message: Not enough fields in vartext data record number: 1

Hello everyone,

I am new to teradata and I am trying to upload a file using fast load.
When I run the batch file I get the following error in the log file:

Not enough fields in vartext data record number: 1

In the teradata table, I have varchar (100) for all the colums, so not sure what is going on.

My script is the following:

sessions 1;
errlimit 10;
logon DWSANA/username,password;

set record vartext;
begin loading D_CFAEISDB.FTP_profile_fl01 errorfiles d_cfaeisdb.Err1,d_cfaeisdb.Err2;
RECORD 1;
DEFINE FILE=\\is-m-54lxx-fs12\DBP_PLANNING\Fast Load\FTP profile.txt;
INSERT INTO D_CFAEISDB.FTP_profile_fl01 .*;
END LOADING;
LOGOFF;

Can anyone help? I have been having a look online, but no sucess so far.

My upload file, ftp profile.txt has four columns but no headers for those columns. The first row is the data directly.

I am completely stack at the moment.

Thanks for your help!!

ratchetandclank 49 posts Joined 01/08
31 Aug 2010

VARTEXT format is a delimited type. Like a csv file where comma is the delimiter. The default delimiter here, is the pipe symbol " | ".

The error shows that there is a problem with the data file. How many columns are there in the table ? Is the file having delimited records ? Example of a delimited file with four columns and one row:

RATCHET|CLANK|LEARNING|TPT

mrodri81 10 posts Joined 08/10
01 Sep 2010

Thank for your reply,

My table has four columns and there are four columns in the data file as we.
I have 780 data rows and also the first row with the column headers.

This is part of my data file:

1 Current 2.9563 1
1 Current 2.9622 2
1 Current 2.9681 3
1 Current 2.9740 4
1 Current 2.9799 5

Do I need to put a comma or | between all the fields? Can I use any other format?

Thanks in advanced, m

ratchetandclank 49 posts Joined 01/08
01 Sep 2010

You can specify the space character as delimiter and run the fastload job. Or as I told, the default delimiter is the pipe symbol. You can modify your data file to have that character as delimiter or in your fastload script, change "set record vartext;" to "set record vartext ' ' ; " ( Note the space character after the set record specification).

mrodri81 10 posts Joined 08/10
01 Sep 2010

Hi there,

I have tried both of your suggestions, but I am still getting an error.
Not sure what the problem is anymore.
Any other alternatives? would it help if I send the file i am trying to upload?
thanks, m

ratchetandclank 49 posts Joined 01/08
02 Sep 2010

What is the error you are getting ?

You told you have a header row in the data file. Try removing the header row and try it again. Means you should ignore the header row, if it is present. I see no problem with your script.

mrodri81 10 posts Joined 08/10
02 Sep 2010

Helllo,

I managed to sort out the problem. It was an issue with my txt file. I added ~ between the fields and this has worked.
Thanks for your support.

04 Oct 2010

Hi,

I am having a file of 27 columns which is comma delimeted and some columns may also have "," in its data. But those column values are enclosed by double quotes. Among the 27 columns any column can have a data value having "," in it.
When I use set record vartext ","; those records which is having "," in its column value is loaded into error table. Kindly share your thoughts.

Thanks in advance.

mtlrsk 30 posts Joined 06/08
09 Oct 2010

Hello ayyappan,

Its time you learn Perl :-), Perl can handle this kind of files, where you can change your Delimiter from , to say | ; this will not change the data which is inside " ", so the delimiter "," is kept as it is inside the data, and | is replaced in all the other places.

Any other approaches by experts are welcomes.. and awaited.

Thanks,

islanderman 40 posts Joined 03/14
09 Apr 2014

Is there a way to use Fastload within a perl script?  I setup the odbc drivers and inserts are working fine but over the network it's very slow.  

Bill828 1 post Joined 03/10
10 Apr 2014

perl DBD-ODBC can not support fastload protocol.
I suggest you use JAVA via JDBC, JDBC can support batch-mode insert(ARRAY support) and fastload protocol. JDBC has many benefits, multiplute language support and cross-platform  will be easy.
 

islanderman 40 posts Joined 03/14
15 Apr 2014

So the older teradata DBD interface, 'DBD::Teradata' was able to do Fastload and MLoad, but not 'DBD::ODBC' ?
I am not familiar enough with JAVA, so I would need to run this either with a shell script or perl.  If Fastload is not supported is there any other option I have to insert the data any faster?  I have been reading about BTEQ but there is nowhere I can see to download the TTU for LInux installation.

Santanu84 122 posts Joined 04/13
16 Apr 2014

Hi
Do you have TPT 14 as part of TTU package in Linux system? Then you can use Dataconnector Producer operator with "QuotedData" option and then run Load operator as fast load. This will solve your problem.
Thanking You
Santanu

islanderman 40 posts Joined 03/14
16 Apr 2014

I don't have TPT installed.  I installed the following:
Teradata ICU, TeraGSS and ODBC pkgs.  Then I installed the DBD::ODBC perl modules to work with DataDirect ODBC Driver Manager.  Everything now works fine, command line and via perl script.  I've also installed the CLIV2 package, but haven't used it yet.  But inserts are very slow.  Hoping to get fastload, bteq or multiload working to help speed up the process.  It was suggested to try the JDBC module but I don't know JAVA well enough.  So can you tell me where to get the TPT 14 package?
 

feinholz 1234 posts Joined 05/08
16 Apr 2014

If you are thinking if starting to use FastLoad and MultiLoad, you should consider using TPT instead (on same media as FastLoad and MultiLoad). We are not adding any features to FastLoad and MultiLoad. TPT is the load/unload suite going forward.
 

--SteveF

islanderman 40 posts Joined 03/14
16 Apr 2014

Is there a place to download TPT?  I don't see it on the list of downloadable files.
 

feinholz 1234 posts Joined 05/08
16 Apr 2014

It is on the same media as the rest of TTU. TPT is a part of TTU.

--SteveF

islanderman 40 posts Joined 03/14
17 Apr 2014

Can you tell me how I can get hold of the TTU download for Linux RedHat?  I've seen a few posts for requests but the only answers I've seen is pointing to documentation about the installation.  The only available download is for Windows.

You must sign in to leave a comment.