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 cgfoss@yahoo.com To: Communication channel for MonetDB users users-list@monetdb.org 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 Stefan.Manegold@cwi.nl To: Communication channel for MonetDB users users-list@monetdb.org 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