Importing Data from file with sql assistant

Tools & Utilities

Importing Data from file with sql assistant

Hi, I

have created a table:

create table sandbox.xyz
(name varchar(256) not null);

and want to load data from a file with the following command:

insert into sandbox.xyz
(?);

the file I use contains just one value 'test'

after I have executed the query and choosen a file the following errormessage appears:

ERROR [42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like ';' between the word 'xyz' and the 'INSERT' keyword. 

Unexpected Error. Refer to the following file for details:

C:\Users\PSteinnoekel\AppData\Roaming\Teradata\SQL Assistant\SQLAError.Txt

The textfile contains the following:

20.02.2013 12:28:17

SQLA Version: 14.1.0.2

Driver Version: ODBC 14.00.00.04

System.Data.Odbc.OdbcException: ERROR [42000] [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like ';' between the word 'xyz' and the 'INSERT' keyword. 

   bei System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

   bei System.Data.Odbc.OdbcCommand.Prepare()

   bei Teradata.SQLA.RunQry.DoImportBind() in V:\cm.client.ttu1401.efix\tdcli\qman\sqla\RunQry.vb:Zeile 1821.

Can someone help me? Thanks!

10 REPLIES
Junior Contributor

Re: Importing Data from file with sql assistant

I just tried it, seems to be a bug in SQLA while parsing the highlighted text.

Is this your code?

create table sandbox.xyz
(name varchar(256) not null)

insert into sandbox.xyz
(?);

Add a  semicolon after the end of the previous query before the insert.

Dieter

Enthusiast

Re: Importing Data from file with sql assistant

I'm using SQLA 13.11.0.03

August 12, 2011

ODBC 13.10.00.06

Database Version Teradata 13.10.0511 13.10.05.11

I have coma delimited CSV with No headers.

When I try to import it in a Table that has a Structure

Col1 CHAR(2) NOT NULL  default 'NA',
Col2 CHAR(2) NOT NULL default 'US',
Col3 CHAR(2) NOT NULL default 'GHH',
Col4 CHAR(16) NOT NULL default 'Opt - ins (1,CT)',
Col5 CHAR(6)NOT NULL default '(1,CT)',
Col6 CHAR(5) NOT NULL default 'Daily',
Col7 CHAR(6)NOT NULL default 'GHH-US',
MyDate DATE NOT NULL FORMAT 'YYYY-MM-DD',
Q1Count Integer null,
Q2Count Integer null,
Q3Count Integer null,
Q4Count Integer null,
Q5Count Integer null,
Q6Count Integer null,
Q7Count Integer null)
PRIMARY INDEX MyDate (MyDate );

insert into TWM_SANDBOX.T1

Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);

Error:

Query contains 15 parameters but import file contains 21 data values

tools->options->export/import/->import also set to comma delimited

Click on File and Select Import Data,Now writeinsert into table.namevalues(?, ?, ?, ?) no of columns on the table should be equal to ?now press f5, it will ask for the source, jus browse and select the source and you can load the data in to the table using SQL assistant.

Not working??

Any clue??

Enthusiast

Re: Importing Data from file with sql assistant

I've been working with a 3 column file, real simple, with a 5 character numeric in col1, a DBname in col 2 and some text in col3.  The Import/Export field delimiters (Tools, Options) set to pipe (|) and field delimiters set to none.  File fields are delimited by pipe and no delimiters around each field.  When I try to execute the insert query (Insert into <tablename> values (?, ?, ?);  I also get the error:  Error executing query for record 1:  3707:  Syntax error, expected something like ')' or ',' between an integer and '?'.  No matter what I do, the best that I get is this error and no data load.  Based upon my analysis, this is a bug in SQLA so any others of you who encounter this realize that if possible a better way may be to edit your input file in something like Ultra Edit and preface each line with 'Insert into <tablename> values(' columns from file data' );  as this appears to be a bug in SQLA.  

Junior Contributor

Re: Importing Data from file with sql assistant

Can you show some example data?

What's your SQLA version?

Enthusiast

Re: Importing Data from file with sql assistant

Hi Dieter, 

   The SQLA version is 14.10, using ODBC 14.10, applied to TD Ver 15.10

 

Sample data, although I have moved on to converting each row of fields to an "Insert into..." command, here is the data from my memory:

 

00001|XXX_XX_XXX|Replace Procedure XXX_XX_XXX.Proc_Test

00002| ...

This is the best memory that I have of the data.  It has been sanitized for publication.  If you have additional questions, I can probably help.  Many different formats were tried, along with different configurations of delimiters as allowed by Import/Export screen options in Tools=> Options in SQLA.  All to no avail. 

 

Had I expected a reply within such a short time, I would have saved all relevant data to the problem. 

 

Thank for your interest,

 

Rick Sumner

Junior Contributor

Re: Importing Data from file with sql assistant

Your data loads fine for me, SQLA 16.0.0.3.

 

I remember an error when there was any other statement on the same tab and you tried to run the highlighted INSERT, but this has beed fixed years ago.

 

If I got strange errors and suspect a client related problem I check DBQL to see the actual SQL received by the parser .

Enthusiast

Re: Importing Data from file with sql assistant

Dieter,
   There are actually many possible sources of the error but, I didn't believe that the data format was one of them. 
The only problem of checking DBQL is that since the line got an error, it isn't in the DBQL because it got bounced with an error message.  It is in the SQLA history DB but that is just as much a question, which is why I class the issue as a SQLA error.  The data looked perfect...and I spent a good while trying various field separators and all the types of named field delimiters.  My guess is that the problem is an old problem that was fixed in SQLA in a prior version but since I have to use the customer's version, I have to deal with what I have.  My preference would be to have TPT, Fastload or even BTEQ and load that way but the customer won't give me those packages since I'm not in the ETL group.  As a result, I'm going to have to load 237000 + rows with SQLA.  Ultraedit makes it tolerable to create a macro that does the formatting by putting the insert on the front and adding the values clause with the data.  It just takes forever to load. 
 
Thanks for the review of the data....it looked OK to me too.  Been a big fan of yours for years.  (Talked to someone who said that you had left TD?  Are you back or are you doing private consulting? ) 
 
Best wishes,
 
Rick Sumner
Junior Contributor

Re: Importing Data from file with sql assistant

Hi Rick,

of course syntax errorrs will be found in DBQL, unless it's logged only into the Summary table or the housekeeping process doesn't copy syntax errors.

Now there might be 270k rows in DBQL :-)

 

If there's a Java runtime you could try running Teradata Studio, this might work without installing by simply copying an existing installation (I've done this successfully using a different JDBC-client). Or use a portable client like DBeaver Portable.

 

Btw, I will never be able to leave Teradata because I'm not an employee :-)

 

 

Enthusiast

Re: Importing Data from file with sql assistant

Hmm, As I think about it, you are right in that the errors and error codes get into DBQLogTbl.  I generally screen them out when I'm working the log as I only want the successful queries.  I'll have to check what went into the table this next week and get back on the content.  

BTW, I had a pleasant time working with a couple of your peer PS people on a gig at a shipper in Copenhagen.  I'll investigate this further next week.