Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Unanswered: Create table from excel data

    I need help to create a table to load the values for the date ranges starting from age 18-25
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    DBA: Data Warehousing & Integration
    Generate External Tables from an Excel Spreadsheet Using Apache Jakarta POI
    by Casimir Saternos

    Generate External Tables from an Excel Spreadsheet Using Jakarta POI

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is an example of saving your data from the spreadsheet(the tab with the age 18-25 data) to a tab delimited flat file(see attached file) and creating a oracle external table to link to this flat file and creating a oracle database table if needed to insert the data into.
    Code:
    -- Create oracle directory if you don't already have one setup
    create directory tmp as '/tmp';
    Grant read, write on directory tmp to user;
    
    -- Create external table linked to flat file
    create table schema.ages_18_25_stage (
      values           number( 10,0 ),
      cfg0_a           number( 10,0 ),
      cfg0_m           number( 10,0 ),
      cfg1_a           number( 10,0 ),
      cfg1_m           number( 10,0 ),
      cfg2_a           number( 10,0 ),
      cfg2_m           number( 10,0 ),
      cfg3_a           number( 10,0 ),
      cfg3_m           number( 10,0 ),
      cfg4_a           number( 10,0 ),
      cfg4_m           number( 10,0 ),
      cfg5_a           number( 10,0 ),
      cfg5_m           number( 10,0 ),
      cfg6_a           number( 10,0 ),
      cfg6_m           number( 10,0 ) )
    organization external (
      type              oracle_loader
      default directory TMP
      access parameters (
        records delimited by newline
        BADFILE      'ages_18_25_stage.bad'
        DISCARDFILE  'ages_18_25_stage.dis'
        LOGFILE      'ages_18_25_stage.log'
        fields  terminated  by X'09'
        missing field values are null
        ( values,
          cfg0_a,
          cfg0_m,
          cfg1_a,
          cfg1_m,
          cfg2_a,
          cfg2_m,
          cfg3_a,
          cfg3_m,
          cfg4_a,
          cfg4_m,
          cfg5_a,
          cfg5_m,
          cfg6_a,
          cfg6_m ) )
        location ( 'ages_18_25.txt' ) );
    
    -- Create Table with primary key ( unique )
    create table schema.ages_18_25 ( 
      values           number( 10,0 ),
      cfg0_a           number( 10,0 ),
      cfg0_m           number( 10,0 ),
      cfg1_a           number( 10,0 ),
      cfg1_m           number( 10,0 ),
      cfg2_a           number( 10,0 ),
      cfg2_m           number( 10,0 ),
      cfg3_a           number( 10,0 ),
      cfg3_m           number( 10,0 ),
      cfg4_a           number( 10,0 ),
      cfg4_m           number( 10,0 ),
      cfg5_a           number( 10,0 ),
      cfg5_m           number( 10,0 ),
      cfg6_a           number( 10,0 ),
      cfg6_m           number( 10,0 ),
      primary key( values ) );
      
    -- Now you can view the records in the flat file(i.e.Oracle external table)
    select *
     from  schema.ages_18_25_stage
      
    -- If you have created the database table and you want the data in the database table, You can insert the records from the flat file into the database table.
    insert into schema.ages_18_25
      select *
       from  schema.ages_18_25_stage

    hth
    Attached Files Attached Files

Posting Permissions

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