DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: loading 200 column table data

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi

    I am trying to load data from a flat file into a 200 column table using sql loader, I am wondering if there is any easier way to write the control file? Or I have to type all 200 columns manually :o

    [Edited by pando on 01-26-2001 at 01:26 PM]

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How about a query from dba_tab_columns:
    system@dev815nt.us> l
    1 select column_name || ' ' || data_type || ','
    2 from dba_tab_columns
    3 where table_name = 'CUST'
    4* order by column_id
    system@dev815nt.us> /

    COLUMN_NAME||''||DATA_TYPE||','
    ----------------------------------------------------------
    ACCT_NO CHAR,
    BANK_NO CHAR,
    FEED VARCHAR2,
    PROFIT_CENTER VARCHAR2,
    ACCT_OFFICER CHAR,
    NAME VARCHAR2,
    ADDR VARCHAR2,
    ICB_FLAG CHAR,
    PRINT_SUPPRESS_FLAG CHAR,
    COMA_STATEMENT_FLAG CHAR,
    STOP_HOLD_FLAG VARCHAR2,
    ACCT_STATUS CHAR,
    ACCT_TYPE CHAR,
    SEND_STMNT_TO_PARENT CHAR,
    LAST_TXN_DATE DATE,
    CREATE_TIME DATE,
    DDA_INSERT_TIME DATE,
    PENDING CHAR,
    PARENT_ACCT_NO CHAR,
    PARENT_BANK_NO CHAR,
    PASSWORD VARCHAR2,

    21 rows selected.
    Jeff Hunter

  3. #3
    Join Date
    Jun 2000
    Posts
    417
    is it deleniated? a quick perl script could be something like this (say if it were comma deleniated). This will create a file turning every one of the rows in the flatfile into an insert statement so you don't have to do any control files.

    Off the top of my head so test it and change it as you need to

    <font face="courier">
    #!/usr/bin/perl

    $batch_count = 0;
    $batch_size = 1000;

    open(FLATFILE, "location/of/flatfile");
    open(INSERTFILE, "insert.sql");
    # read each line
    while ($row = <FLATFILE>) {
    # @columns is an array, each value is a column
    @columns = split /,/, $row;
    # create the front of the insert statement
    $insert = "insert into table_name values (";

    # append each column data to the insert statement
    foreach $column (@columns) {
    $insert .= "$column,";
    }
    # remove the trailing comma
    chop $insert;
    # close the insert statement
    $insert .= ");\n";

    print INSERTFILE $insert;
    if ($batch_count % $batch_size == 0) {
    print INSERTFILE "commit;\n";
    }
    }
    print INSERTFILE "commit;\n";

    close INSERTFILE;
    close FLATFILE;

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    Or do it with Jeff's idea :)

    I can always come up with a quick idea to get it done, but not always the easiest :)

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I also thought of using dynamic sql but how do I specify the position though

    ACCT_NO CHAR, to

    ACCT_NO POSITION(01:04) CHAR EXTERNAL,

    :-?


    and ... sorry but I dunno perl :D

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    where do you have the positions defined? in a file? what does it look like?

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    the control file looks something like


    LOAD DATA
    INFILE 'c:\temp\loader.txt'
    INTO TABLE EMP

    ( EMPNO POSITION(01:04) INTEGER EXTERNAL,
    ENAME POSITION(06:15) CHAR,
    JOB POSITION(17:25) CHAR,
    MGR POSITION(27:30) INTEGER EXTERNAL,
    SAL POSITION(32:39) DECIMAL EXTERNAL,
    COMM POSITION(41:48) DECIMAL EXTERNAL,
    DEPTNO POSITION(50:51) INTEGER EXTERNAL)


    this is an example, the real table has 200 columns....

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    no no, obviously you have the specification of what fields are in what positions. do you have that written on a piece of paper? is it in a spreadsheet? is it in it's own file, or part of the flatfile?

  9. #9
    Join Date
    Dec 2000
    Posts
    11

    if you generate the column list

    don't forget to put the column of type long on the end. =)

    -josh

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I think I will have to write a pl/sql to do it, dba_tab_columns gives field length thankfully :D

Posting Permissions

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


Click Here to Expand Forum to Full Width