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
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |