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

I figured out the problem.  the row delimiter on windows should be '\n' and not '\r\n'.   I was able to successfully load 51M rows into a much larger table. ________________________________ From: Colin Foss To: Communication channel for MonetDB users Sent: Friday, November 29, 2013 5:31 PM Subject: Re: copy into failed to import table Would it be possible to have a windows path example? No matter what combination I try I still get "failed to import table" or "filename must have absolute path". copy into staging_simple_results from ('D:\junk\simple_import.csv') delimiters ',', '\r\n'; copy into staging_simple_results from ('D:/junk/simple_import.csv') delimiters ',', '\r\n'; copy into staging_simple_results from ('D:\\junk\\simple_import.csv') delimiters ',', '\r\n'; copy into staging_simple_results from ('\junk\simple_import.csv') delimiters ',', '\r\n'; copy into staging_simple_results from ('\\junk\\simple_import.csv') delimiters ',', '\r\n'; copy into staging_simple_results from ('/junk/simple_import.csv') delimiters ',', '\r\n'; I have been away from MonetDB for about six years and wanted to reevaluate it for a new idea I have. I remember doing bulk inserts back then but cannot remember the syntax for windows drive paths. in mssql, the bcp utility has the ability to put errors into a separate file making it a lot easier to troubleshoot problems. http://technet.microsoft.com/en-us/library/ms162802(v=sql.90).aspx ________________________________ From: Stefan Manegold To: Communication channel for MonetDB users Sent: Wednesday, November 27, 2013 8:26 AM Subject: Re: copy into failed to import table 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