Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: How to do data import from a flat file ?

    Hi,

    How to load the data from a flat text file having delimiter ',' between columns ?
    Please give a sample query.

    Thanks,
    Sam

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by SamCute
    Hi,

    How to load the data from a flat text file having delimiter ',' between columns ?
    Please give a sample query.

    Thanks,
    Sam
    modified by coldel;

    See LOAD/IMPORT for dets.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    Thanks for your reply.

    Can you give some examples for the same.

    Sam.

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    There are some in the Command reference...

    import from myfile.ixf of ixf messages msg.txt modified by coldel; insert into staff

    ";" is actually a little tricky since it's a special character to your shell most likely, so stick the command in a file like so:

    import from myfile.ixf of ixf messages msg.txt modified by coldel; insert into staff
    0

    (That's a 0)

    and run the command:
    db2 -td0 -vf yourfile.sql
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    import from myfile.ixf of ixf messages msg.txt modified by coldel; insert into staff

    In the above query please explain the following denotes what ?

    'of ixf'
    'messages'
    'modified by coldel'

    Then why haven insert into staff ? -- > Is the above is a full single query ?

    Thanks,
    Sam

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by SamCute
    Hi,

    import from myfile.ixf of ixf messages msg.txt modified by coldel; insert into staff

    In the above query please explain the following denotes what ?

    'of ixf'
    'messages'
    'modified by coldel'

    Then why haven insert into staff ? -- > Is the above is a full single query ?

    Thanks,
    Sam
    Please look at the IMPORT command in the Command Reference, it explains everything!

    I messed up, it should have been:
    import from myfile.del of del messages msg.txt modified by coldel; insert into staff

    This takes everything from the flat file "myfile.del" which is "of del" (delimited ASCII), writes any messages to msg.txt file, assuming a column delimiter of ";".

    Insert is the mode, not an SQL statement.
    Staff is the target table.

    Command Reference.
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

  8. #8
    Join Date
    Jan 2005
    Posts
    1

    alternative methode

    hi everyone!

    try this sample-statement: instead of using coldel; try coldel0x3B.
    by using the hex-code for ";" the problem with the ";" will disappear...

    IMPORT FROM "O:\test.txt" OF DEL MODIFIED BY CHARDEL"" COLDEL0x3B DECPT, COMMITCOUNT 100 MESSAGES "O:\test.err" INSERT INTO DB2ADMIN.TEST1;

    rgds, sleye.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •