Re: copy into failed to import table

On 2013-11-29 18:18, Colin Foss wrote:

So what was the reason for the failure? The escape character?

You specified the row delimiter as '\\r\\n'. This means that the actual expected row delimiter is the 4 character sequence \r\n. But you want the 2 character sequence carriage return, newline, which in SQL is specified as the string '\r\n'.

Some examples on the web site have the double escape.

Where? That would probably have to be fixed, unless it is inside a C program in which case the C compiler "eats" one level of escaping, and the SQL compiler "eats" the other.

Is it possible to have an error file detailing the attempted import per line?

I'm not entirely sure what you're proposing. But remember, everything has a cost.

Sent from Yahoo Mail on Android http://overview.mail.yahoo.com/mobile/?.src=Android

------------------------------------------------------------------------ *From: * Stefan Manegold ; *To: * Communication channel for MonetDB users ; *Subject: * Re: copy into failed to import table *Sent: * Wed, Nov 27, 2013 4:26:54 PM

Please use correct(ly quoted) delimiters:

sql>CREATE TABLE staging_simple_results( more>lookup varchar(20) NULL, more>extra_symbol varchar(20) NULL, more>measure1 tinyint NULL, more>base_temp float NULL more>) more>; operation successful (2.293ms) sql> sql>copy into staging_simple_results from ('/tmp/simple_import.csv') delimiters ',', '\\r\\n'; failed to import table sql>copy into staging_simple_results from '/tmp/simple_import.csv' delimiters ',', '\\r\\n'; failed to import table sql>copy into staging_simple_results from ('/tmp/simple_import.csv') delimiters ',', '\r\n'; 2 affected rows (72.238ms) sql>copy into staging_simple_results from '/tmp/simple_import.csv' delimiters ',', '\r\n'; 2 affected rows (71.465ms) sql>

Best, Stefan

----- Original Message -----

...

I am using the latest version. MonetDB 5 server v11.15.17 "Feb2013-SP5". The server and client are running under my account. The files and folders were also created with my account.

Here is a simpler version which also encounters the "failed to import

table"

...

message.

CREATE TABLE staging_simple_results( lookup varchar(20) NULL, extra_symbol varchar(20) NULL, measure1 tinyint NULL, base_temp float NULL ) ;

Here are the contents of the same input, in a file called D:\junk\simple_import.csv. The file is also attached. one,two,3,4.023456787 green,brown,2,8.3456837

This is the statement I issued for the bulk load. copy into staging_simple_results from ('D:\junk\simple_import.csv') delimiters ',', '\\r\\n' ;

On 2013-11-27 09:03, Colin Foss wrote:

...

I created a table with 43 columns. The table contains char, varchar, and tinyint datatypes. I am running on Windows Server 2008 R2 64bit. The file contains two rows and is comma separated. The file is BCP output from MSSQL. When I tried to bulk load into the table from a file with a COPY INTO command I got an unhelpful message.

"failed to import table"

How can I find out why the import failed?

Which version of MonetDB are you using? I seem to remember fixing a problem where the actual failure message wasn't propagated to the level where the error was reported.

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Sjoerd Mullender