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

Thread: reading text file

  1. #1
    Join Date
    Aug 2002
    Posts
    2
    Contractor Name, Address, and Zip Code
    YORK INTERNATIONAL CORPORATION
    PURCHASING DIVISION
    631 S RICHLAND AVE
    YORK, PA
    174033445

    Contract_v = varchar2 ''contractor,name,and Zipcode);
    Address_v = null;

    Above is text file. want to read it in Oracle (preferably)or SQL Server.After reading Contractor Name,address,and Zip Code, I want to substring /concat all 4 lines (including tabs) and remove coma after YORKand put all 4 lines in as one field called Address_v and add ',' at the end (after zip code).

    Thanks for the help.

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    Hi

    You can use

    UTL_FILE

    to read the text file in ur PL/SQL

    Regards,

    Santosh

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi
    if you are using 9i use external table
    this shuld be easy for ur requirement
    Cheers!
    OraKid.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Steps:
    -------
    1) create a external table with these Contractor Name, Address, and Zip Code columns.

    2) write a select statement:

    DECLARE
    lv_Address_v VARCHAR2(300);
    BEGIN
    SELECT Contractor_Name || REPLACE(Address,',','') || Zip_Code || ',' INTO lv_Address_v FROM external_table_name;
    insert into Address_v values(lv_Address_v);
    commit;
    END;

    try this hope this help U

    "remove coma after YORK" -- in don't understand this paret. i hope u want to remove from address column.

    Cheers!
    Cheers!
    OraKid.

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