Re: [MonetDB-users] copy into table

21 Jan 2009

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