fastload error message: Not enough fields in vartext data record number: 1

Tools
Enthusiast

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!!
17 REPLIES
Enthusiast

Re: fastload error message: Not enough fields in vartext data record number: 1

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

Enthusiast

Re: fastload error message: Not enough fields in vartext data record number: 1

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
Enthusiast

Re: fastload error message: Not enough fields in vartext data record number: 1

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

Re: fastload error message: Not enough fields in vartext data record number: 1

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
Enthusiast

Re: fastload error message: Not enough fields in vartext data record number: 1

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

Re: fastload error message: Not enough fields in vartext data record number: 1

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.

Re: fastload error message: Not enough fields in vartext data record number: 1

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

Re: fastload error message: Not enough fields in vartext data record number: 1

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,
Enthusiast

Re: fastload error message: Not enough fields in vartext data record number: 1

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.