Stefan, Thanks so much for the quick reply, that straightened me out right away. But I find myself at another hurdle. I updated the copy to read COPY 8000000 OFFSET 2 RECORDS INTO tblc ... and I'm faced with the following exception: !SQLException:importTable:failed to import table !ERROR: HEAPextend: failed to extend to 64000000 for 01/46/14603tail !ERROR: TABLETcreate_bats: Failed to create bat of size 8000000 I tried to chunk up the copy 1M rows at a time using COPY 1000000 OFFSET 2 RECORDS ... COPY 1000000 OFFSET 1000002 RECORDS ... The first succeeds but the second fails similarly: !SQLException:importTable:failed to import table !ERROR: HEAPextend: failed to extend to 8000000 for 01/63/16336tail !ERROR: TABLETcreate_bats: Failed to create bat of size 1000000 I imagine I'm having some kind of monetdb.conf setting or the table definition is wrong though I don't find any thing about size in the SQL ref. Thoughts? If I'm overlooking the details in the manuals or the mailing list please just point me in the correct direction. I've gone after a number of searches without much direct reference. Thank you, Paul On 11/18/2009 5:11 PM, Stefan Manegold wrote:
Hi Paul,
here some hints and examples:
Consider
$ cat /tmp/data.csv -1,-2,-3 11,12,13 21,22,23 31,,33 41,42,43 51,52,53
First, if you what to skip the first row, and then load the remaining 5, your SQL COPY syntax should not read "copy 1 offset 5 records" as in
$ cat /tmp/load.sql create table t (a int, b int, c int); copy 1 offset 5 records into t from '/tmp/data.csv' delimiters ',','\n'; select * from t; drop table t;
$ mclient -lsql /tmp/load.sql [ 1 ] % sys.t, sys.t, sys.t # table_name % a, b, c # name % int, int, int # type % 2, 2, 2 # length [ 41, 42, 43 ]
but rather "copy 5 offset 2 records" as in
$ cat /tmp/load.sql create table t (a int, b int, c int); copy 5 offset 2 records into t from '/tmp/data.csv' delimiters ',','\n'; select * from t; drop table t;
(this is not MonetDB specific, but SQL standard; read it as "copy 5 starting from 2").
This still results in your error:
$ mclient -lsql /tmp/load.sql MAPI = monetdb@localhost:50000 QUERY = create table t (a int, b int, c int); copy 5 offset 2 records into t from '/tmp/data.csv' delimiters ',','\n'; select * from t; drop table t; ERROR = !SQLException:sql:value '' while parsing ',33' from line 2 field 1 not inserted, expecting type int !SQLException:importTable:failed to import table % sys.t, sys.t, sys.t # table_name % a, b, c # name % int, int, int # type % 1, 1, 1 # length
To overcome, you need to specify that NULL values are not given as "NULL" or "null" in your data file (as e.g. in
$ cat /tmp/data.csv -1,-2,-3 11,12,13 21,22,23 31,null,33 41,42,43 51,52,53
$ mclient -lsql /tmp/load.sql [ 5 ] % sys.t, sys.t, sys.t # table_name % a, b, c # name % int, int, int # type % 2, 2, 2 # length [ 11, 12, 13 ] [ 21, 22, 23 ] [ 31, NULL, 33 ] [ 41, 42, 43 ] [ 51, 52, 53 ]
), but rather as empty fields (empty strings) by adding "null as ''" to your copy command:
$ cat /tmp/load.sql create table t (a int, b int, c int); copy 5 offset 2 records into t from '/tmp/data.csv' delimiters ',','\n' null as ''; select * from t; drop table t;
$ mclient -lsql /tmp/load.sql [ 5 ] % sys.t, sys.t, sys.t # table_name % a, b, c # name % int, int, int # type % 2, 2, 2 # length [ 11, 12, 13 ] [ 21, 22, 23 ] [ 31, NULL, 33 ] [ 41, 42, 43 ] [ 51, 52, 53 ]
(Again, this is SQL standard.)
How this helps you further.
Stefan
On Wed, Nov 18, 2009 at 04:38:56PM -0600, pphillips99@gmail.com wrote:
COPY 1 OFFSET 100 RECORDS INTO tlbc FROM '/mnt/data/myfile.txt' DELIMITERS '\t', '\r\n'
Trying to use COPY to import tab delimited text files. The text file has a header row and I've verified the delimiters and eols. I have a number of empty columns at the end of each row in the text file and those colums in the table are a combination of integers and decimals. Received the following error at the first of the empty columns:
!SQLException:sql:value '' while parsing from line 0 field 213 not inserted, expecting type lng !SQLException:importTable:failed to import table !ERROR: Decimal cannot be parsed from lng
I realize I could do manipulations but it's second choice given the data size, but the file is 8G.
How can I overcome?
Paul
------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users