[MonetDB-users] copy into table

newer
Re: [MonetDB-users] copy into table

older
[MonetDB-users] SQL server crash

dariuszs

21 Jan 2009 21 Jan '09

3:11 p.m.

Hello, Is there any way to copy flat file into the table not using delimiters? Thanks. Dariusz.

Show replies by date

Stefan Manegold

21 Jan 21 Jan

3:34 p.m.

On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...

Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?) Stefan

...

Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Sam Mason

3:48 p.m.

On Wed, Jan 21, 2009 at 04:34:26PM +0100, Stefan Manegold wrote:

...

On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work.

My guess is that the OP wants something like the following pseudo code: q <- DB.prepare "INSERT INTO tbl (id,txt) VALUES (10,$1);" DB.execute q [OS.readFile filename] i.e. read the contents of a file and bung the whole thing into a single attribute in a single tuple. Not sure though. Dariuszs, any chance of a more verbose explanation? -- Sam http://samason.me.uk/

dariuszs

5:07 p.m.

Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a',''; So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers It would be nice to have such a feature. Thanks. Dariusz. Stefan Manegold wrote:

...

On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

...
Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Wouter Alink

5:21 p.m.

Hello Dariusz, Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar. Cheers, Wouter import sys numFields = 3 delim = '\t' def emitTuple(cells): sys.stdout.println(delim.join(cells) cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = [] 2009/1/21 dariuszs :

...

Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

...
Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

5:31 p.m.

Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz. Wouter Alink wrote:

...

Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

...
Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Stefan Manegold

5:46 p.m.

On Wed, Jan 21, 2009 at 12:31:05PM -0500, dariuszs wrote:

...

Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz.

Hm, I guess, my original question remains: if the flat file contains only record delimiters (in your case '\x0D\x0a', i.e., Windows-style newlines), but no field delimiters, how can either humans or a system identify the individual fields per record? E.g., consider the following dump (single record) of three integer columns: 12345 is this to be read as 1,2,345 or 1,23,45 or 1,234,5 or ... ? ... maybe I'm overlooking a "crucial" point ...? Stefan

...

Wouter Alink wrote:

...
Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

...
Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

5:59 p.m.

Hi, Hm, because first you create a table and then you import data into it. create table t1 (c1 varchar(27), c2 varchar(10)); Based on this table layout you know how to parse input record, yes/no? Just a suggestion. Dariusz. Stefan Manegold wrote:

...

On Wed, Jan 21, 2009 at 12:31:05PM -0500, dariuszs wrote:

...
Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz.

Hm, I guess, my original question remains: if the flat file contains only record delimiters (in your case '\x0D\x0a', i.e., Windows-style newlines), but no field delimiters, how can either humans or a system identify the individual fields per record? E.g., consider the following dump (single record) of three integer columns:

12345

is this to be read as 1,2,345 or 1,23,45 or 1,234,5 or ... ?

... maybe I'm overlooking a "crucial" point ...?

Stefan

...
Wouter Alink wrote:

...
Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

...
Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Stefan Manegold

6:19 p.m.

On Wed, Jan 21, 2009 at 12:59:20PM -0500, dariuszs wrote:

...

Hi, Hm, because first you create a table and then you import data into it. create table t1 (c1 varchar(27), c2 varchar(10)); Based on this table layout you know how to parse input record, yes/no? Just a suggestion. Dariusz.

Ok, as mentioned earlier, in the special case of fixed-width fields --- fixed-width in the sense that the textual representation of all values in a column has the same lenght/width (in any encoding?) ---, such feature might work --- I cannot tell right now, whether the SQL standard does cover such feature/semantics. Being part of the SQL standard would be a requirement to implement such feature in MonetDB/SQL. Stefan ps: To me, fixed-width dumps without field delimiters ("to save space"?) indeed seem to be a mainframe legacy from the 1970s (and earlier)... ;-) If this is about a one-time migration of mainframe data to MonetDB, I second Wouters suggestion of preprocesing the data to add field delimiters --- a trivial excersize in your favorite scripting language ...

...

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 12:31:05PM -0500, dariuszs wrote:

...
Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz.

Hm, I guess, my original question remains: if the flat file contains only record delimiters (in your case '\x0D\x0a', i.e., Windows-style newlines), but no field delimiters, how can either humans or a system identify the individual fields per record? E.g., consider the following dump (single record) of three integer columns:

12345

is this to be read as 1,2,345 or 1,23,45 or 1,234,5 or ... ?

... maybe I'm overlooking a "crucial" point ...?

Stefan

...
Wouter Alink wrote:

...
Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

> Hello, > Is there any way to copy flat file into the table not using delimiters? > > > I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

> Thanks. Dariusz. > > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by: > SourcForge Community > SourceForge wants to tell your story. > http://p.sf.net/sfu/sf-spreadtheword > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > >

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

6:13 p.m.

Hi, And for numeric columns you would create table like this: create table t1 (c1 int, c2 double, c3 int); example input record '1234123456781234' would create 3 columns with values c1 '1234' c2 '12345678' c3 '1234' Again just a suggestion. Thanks. Dariusz. Stefan Manegold wrote:

...

On Wed, Jan 21, 2009 at 12:31:05PM -0500, dariuszs wrote:

...
Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz.

Hm, I guess, my original question remains: if the flat file contains only record delimiters (in your case '\x0D\x0a', i.e., Windows-style newlines), but no field delimiters, how can either humans or a system identify the individual fields per record? E.g., consider the following dump (single record) of three integer columns:

12345

is this to be read as 1,2,345 or 1,23,45 or 1,234,5 or ... ?

... maybe I'm overlooking a "crucial" point ...?

Stefan

...
Wouter Alink wrote:

...
Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

...
Hello, Is there any way to copy flat file into the table not using delimiters?

I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

...
Thanks. Dariusz.

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Stefan Manegold

6:24 p.m.

On Wed, Jan 21, 2009 at 01:13:15PM -0500, dariuszs wrote:

...

Hi, And for numeric columns you would create table like this:

create table t1 (c1 int, c2 double, c3 int); example input record '1234123456781234' would create 3 columns with values c1 '1234' c2 '12345678' c3 '1234'

Who says that *all* int values have *exactly* 4 digits, and *all* double values have *exactly* 8 digits? Stefan

...

Again just a suggestion. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 12:31:05PM -0500, dariuszs wrote:

...
Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz.

Hm, I guess, my original question remains: if the flat file contains only record delimiters (in your case '\x0D\x0a', i.e., Windows-style newlines), but no field delimiters, how can either humans or a system identify the individual fields per record? E.g., consider the following dump (single record) of three integer columns:

12345

is this to be read as 1,2,345 or 1,23,45 or 1,234,5 or ... ?

... maybe I'm overlooking a "crucial" point ...?

Stefan

...
Wouter Alink wrote:

...
Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote:

> Hello, > Is there any way to copy flat file into the table not using delimiters? > > > I'm now aware that SQL standard covers such functionality, let alone how is should work. How are attribute- and record- boundaries to be identified other than by delimiters? Using only fixed-width datatypes AND attribute values? (The latter requiring explicit white-space(?) padding in the flat data file and implicite white-space trimming during load?)

Stefan

> Thanks. Dariusz. > > > ------------------------------------------------------------------------------ > This SF.net email is sponsored by: > SourcForge Community > SourceForge wants to tell your story. > http://p.sf.net/sfu/sf-spreadtheword > _______________________________________________ > MonetDB-users mailing list > MonetDB-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/monetdb-users > > > > >

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

dariuszs

6:33 p.m.

Hi, You're right. These were just suggestions. I'ts just that I'm so used to MySQL that I assumed that every database has that feature and maybe I was missing something in my SQL.... Thanks for your help. Dariusz. Stefan Manegold wrote:

...

On Wed, Jan 21, 2009 at 01:13:15PM -0500, dariuszs wrote:

...
Hi, And for numeric columns you would create table like this:

create table t1 (c1 int, c2 double, c3 int); example input record '1234123456781234' would create 3 columns with values c1 '1234' c2 '12345678' c3 '1234'

Who says that *all* int values have *exactly* 4 digits, and *all* double values have *exactly* 8 digits?

Stefan

...
Again just a suggestion. Thanks. Dariusz.

Stefan Manegold wrote:

...
On Wed, Jan 21, 2009 at 12:31:05PM -0500, dariuszs wrote:

...
Hi, Such feature would be nice for lots of people especially big data warehouses with mainframe computers that store terabytes of data in flat files. Dariusz.

Hm, I guess, my original question remains: if the flat file contains only record delimiters (in your case '\x0D\x0a', i.e., Windows-style newlines), but no field delimiters, how can either humans or a system identify the individual fields per record? E.g., consider the following dump (single record) of three integer columns:

12345

is this to be read as 1,2,345 or 1,23,45 or 1,234,5 or ... ?

... maybe I'm overlooking a "crucial" point ...?

Stefan

...
Wouter Alink wrote:

...
Hello Dariusz,

Such a feature seems to be very tailored to your specific situation, and will probably not make it into MonetDB (my guess). If you would like such a feature, perhaps preprocess your data with a 12-line python script, such as the one below, or anything similar.

Cheers, Wouter

import sys

numFields = 3 delim = '\t'

def emitTuple(cells): sys.stdout.println(delim.join(cells)

cells = [] for line in sys.stdin: cells += line if len(cells) == numFields: emitLines(cells) cells = []

2009/1/21 dariuszs :

...
Hi, Well I can output a table as a flat file like this: copy select col1,col2,col3 from table1 into 'c:\flat_file.txt' using delimiters '','\x0D\x0a','';

So I was wondering if I can do the same thing for the input like this: copy from 'c:\flat_input.txt' into table2 using delimiters '','\x0D\x0a',''; It says: don't use delimiters, use 0D0A for record delimiters, don't use qualifiers

It would be nice to have such a feature. Thanks. Dariusz.

Stefan Manegold wrote:

> On Wed, Jan 21, 2009 at 10:11:40AM -0500, dariuszs wrote: > > > > >> Hello, >> Is there any way to copy flat file into the table not using delimiters? >> >> >> >> > I'm now aware that SQL standard covers such functionality, let alone how is > should work. > How are attribute- and record- boundaries to be identified other than by > delimiters? > Using only fixed-width datatypes AND attribute values? > (The latter requiring explicit white-space(?) padding in the flat data file > and implicite white-space trimming during load?) > > Stefan > > > > > >> Thanks. Dariusz. >> >> >> ------------------------------------------------------------------------------ >> This SF.net email is sponsored by: >> SourcForge Community >> SourceForge wants to tell your story. >> http://p.sf.net/sfu/sf-spreadtheword >> _______________________________________________ >> MonetDB-users mailing list >> MonetDB-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/monetdb-users >> >> >> >> >> >> > > > ------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

------------------------------------------------------------------------------ This SF.net email is sponsored by: SourcForge Community SourceForge wants to tell your story. http://p.sf.net/sfu/sf-spreadtheword _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

5891

Age (days ago)

5891

Last active (days ago)

List overview

Download

11 comments

4 participants

participants (4)

dariuszs
Sam Mason
Stefan Manegold
Wouter Alink