All Forums Teradata Studio
pyite 9 posts Joined 10/14
15 Oct 2014
Teradata Load Errors

Hello,
I am attempting to load a tab delimited file into a table, and I am receiving the following errors:
 
Row number 6,812 had an error.
Cause = Unexpected number of parameters

Row number 26,237 had an error.
Cause = Unexpected number of parameters

Row number 31,402 had an error.
Cause = Unexpected number of parameters
 
I've looked at each of these rows in my raw data source, and the commonality amongst all of them is that they contain parentheses.
Example: (888)8998493
 
The column is formatted as varchar(20)
 
Any ideas on why I am receiving this error?
 
 

fnewbrough 17 posts Joined 05/11
15 Oct 2014

A few questions:
1. What utility are you using to load the table?
2. Can you provide the script that you are using?
Thanks
-Fred

fgrimmer 553 posts Joined 04/09
15 Oct 2014

Andrew, Studio's Teradata Load feature allows for tab delimited columns and parenthesis within the column data. The 'Unexpected number of parameters' error means that the row contains more columns than the table has. You want to make sure that there are not embedded tabs in any of the column data for the rows that failed. Also, what is your Character String delimiter? Make sure the rows that failed have the correct string delimiter.

pyite 9 posts Joined 10/14
15 Oct 2014

A few questions:
1. What utility are you using to load the table?
2. Can you provide the script that you are using?
Thanks
-Fred

I'm using the "Teradata Load Wizard" that's a part of Teradata Studio. There really isn't a script, I am merely creating an empty table, and then loading the flat file.
 

Andrew, Studio's Teradata Load feature allows for tab delimited columns and parenthesis within the column data. The 'Unexpected number of parameters' error means that the row contains more columns than the table has. You want to make sure that there are not embedded tabs in any of the column data for the rows that failed. Also, what is your Character String delimiter? Make sure the rows that failed have the correct string delimiter.

The number of columns exceeding the amount of columns in the table was my first guess as well. I went back and checked, and the number of columns was correct.
A few more details about this. If I do a massive find/replace on the file, and remove the ( and ) parentheses, and do nothing else... the load works without error. Additionally, if I save the file in xlsx format instead of tab delimited, the load completes without error (although quite a bit slower).
My string delimiter is set to None. I also tried purposely inserting " or ' for the columns in question, and Teradata throws an unexpected token exception.

fgrimmer 553 posts Joined 04/09
15 Oct 2014

Andrew, What version of Studio are you running?

pyite 9 posts Joined 10/14
15 Oct 2014

Studio version is 14.10

fgrimmer 553 posts Joined 04/09
15 Oct 2014

Andrew, We have tested with our 14.10 version and do not see a problem with what you described. Can you attach a sample of the rows that fail?

pyite 9 posts Joined 10/14
15 Oct 2014

Sure.
 
I have a create table statement that looks like this:

 create table tab_test (paren_field varchar(20), norm_field varchar(20));

 
Then I try to load the file using the following screem (Right click on table > Teradata Load)
 

 
I don't think that I can attach a file here, but it is a simple file with two lines:
paren_field    norm_field
(888)8943456UK    random_text
Where the first row is column labels, and the second begins the data rows.
Here is the error I receive:

Starting Teradata Load...

Teradata Load Error Occurred: Error Code =  0
SQL State = null
Message = Teradata Load Error: Batch is empty.
2 Rows Processed
0 Rows Loaded
Teradata Load Failed

Row number 2 had an error.
Cause = Unexpected number of parameters

Again, if I remove the () from the data row, the table loads without issue.

 
Thanks for your help

fgrimmer 553 posts Joined 04/09
15 Oct 2014

Andrew, Thanks. We will take a look at this.

fgrimmer 553 posts Joined 04/09
16 Oct 2014

Andrew, We found a bug regarding the handling of parenthesis that was fixed in Studio 15.00 version. Is it possible for you to upgrade to Studio 15.00 and try your Teradata Load. Upgrading Studio will not delete your workspace. Let me know if the problem still exists after upgrading.

pyite 9 posts Joined 10/14
16 Oct 2014

Thanks for your help. I'm sure that there is a path to upgrading to 15.00, just not sure when that will happen. Perhaps this will be the impetus to make that upgrade happen.

fgrimmer 553 posts Joined 04/09
16 Oct 2014

Andrew, As a workaround, have you tried the Data>Load option that is available on the 14.10 table Data menu. It uses the Eclipse data load which should work with parenthesis.

pyite 9 posts Joined 10/14
17 Oct 2014

Yes- I have tried to use that facility as well, and it does work... but it is significantly slower. Not a big deal when loading a few thousand rows, but with 1mm+ it becomes difficult.

krusello 4 posts Joined 08/15
24 Aug 2015

Hi,
I have a similar problem. Using Teradata Studio version 15.10.00.04. I need to load (using Data Transfer Wizard) a csv file into a table. The file size is 508 Mb and it contains > 6m rows.
When loading the file I get the "Unexpected number of parameters" error, and the file is not loaded. The text column to be imported does for some rows contain parenteses. (In the file there is a total of 2727 occureces of the left parentheses '(' in the file - and 2662 occurences of the right parentheses ')'.)
I have tried replacing the parentheses in the file with the characters '[' and ']' respectively. Then the load runs with no errors. But this is not an acceptable solution.
I gather the 15.00 version then did not fully solve this problem. Is there any hope for a new fix soon?
Regards,
Leif

hl186018 8 posts Joined 08/14
25 Aug 2015

Hi Leif,
I have tired to load a csv file containing different parentheses {, }, [, ], (, ), < and > to Studio 15.00 and 15.10.00.04 and all get loaded to Teradata table.
"Unexpected number of parameters" error means something wrong for the column separation. The number of columns in your data is more or less than the number of columns of the table that the data is going in. Make sure the column delimiter option matches with your data file and the number of columns matches with the numbers of columns for the error rows as well.
Let us know if you still experience the same issue and tell us the details of your data loading options so we can assist further. Thank you.
 
 

krusello 4 posts Joined 08/15
27 Aug 2015

Hi Janelle,
Thank you for replying!
I understand what the error message is meant to signify. But I cannot se that the issue I am experiancing can be caused by the number of columns in by data being more or less than the number of columns in the destination table. The fact that the very same data/file loads with no errors after having done a search/replace of all parentheses from ( and ) to [ and ] should signify that this is not a case of columns in data file not matching columns in table.
Francine wrote the following in an answer to the original question above: "We found a bug regarding the handling of parentheses that was fixed in Studio 15.00 version. [...] Let me know if the problem still exist after upgrading." The thread-starter never confirmed that the problem was solved. So I gather there is a possibility that the problem is not actually fully solved. Which my problem seem to indicate.
In the row causing the load job to fail, a left parentheses is the first character of the column containing the parentheses characters. This may possibly be a clue to solving the issue.
I would very much appreciate if you could take a second look at the issue. Thank you.

krusello 4 posts Joined 08/15
27 Aug 2015

My loading options are:
 
File type: Delimited Text
Column Labels in firts row
Replace Existing Data
Column Delimiter: Semicolon
Character String Delimiter: "
Line Separator: Widdows OS (CR and LF)
File Encoding: ISO-8859-1
Start Loading At Row Number: 1
 
 

hl186018 8 posts Joined 08/14
27 Aug 2015

I have tried 8M rows of data containing left and right parentheses into existing table using Fastload process and with the same loading options above. All get loaded to the table with Studio 15.10.00.04.
I can assist you by sending me :
1. your data file which experiences loading problem,
2. schema of the existing table that you try to load into,
3. loading options
to my email homan.law@teradata.com
I will take a look, thanks.
 

krusello 4 posts Joined 08/15
28 Aug 2015

Hi Janelle,
I very much appreciate you taking a look at the prolem. I will come back to you by mail over the weekend.
Thank you!

hl186018 8 posts Joined 08/14
11 Sep 2015

It is a bug and will be fixed in the upcoming release aroound Nov 2015. Thanks.

You must sign in to leave a comment.