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
>

--
| 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