Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    5

    Unanswered: Load Excel Spreadsheet into a new DB2 table

    Hello guys!

    Please help. I need to create a new Db2 table wherein the data is from an Excel Sheet. I ftp'd the file to mvs but i don't know how can I convert it to a new table.

    If there is already a thread related to this.. please redirect me.

    Thanks!

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by wachutink
    I need to create a new Db2 table wherein the data is from an Excel Sheet. I ftp'd the file to mvs but i don't know how can I convert it to a new table.
    1. Make sure you saved the file (in Excel) as "comma-separated fields", with double quotes as the text delimiter (only for text, not for numbers).
    2. I'm assuming you are using DB2 v8 for z/OS. In that case, use the "FORMAT" option of the LOAD utility:
    Code:
    LOAD RESUME YES FORMAT DELIMITED
    INTO TABLE mytable
         (col1 CHAR,
          col2 INTEGER,
          col3 SMALLINT,
          col4 DECIMAL,
          col5 DATE)
    (My 5 columns are just examples; I suppose you can work out the names and datatypes in your case.)
    3. If still at version 7, you'll have to convince Excel to save the data in fixed-width columns. The column list will need, in that case, a POSITION.

    Sample JCL file:
    Code:
    //user JOB ,,
    //STEP3 EXEC DSNUPROC
    //SYSIN DD *
     LOAD RESUME YES FORMAT DELIMITED
     INTO TABLE mytable (col1 CHAR, col2 INTEGER, col3 SMALLINT,
                         col4 DECIMAL, col5 DATE)
    /*
    //SYSREC DD *
    "Field one",364,-543,3.45,"2008-06-07"
    "Field two",0,0,0,"2008-06-07"
    /*
    //
    (In practice, your SYSREC line will be "//SYSREC DD DISP=SHR,DSN=datasetname".)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Nov 2007
    Posts
    5
    I will try this. Thanks so much Peter!!! Much appreciated.

Posting Permissions

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