Skip to Main Content
  • Questions

  • SQL solution to read file with one line content and format them

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Samy.

Asked: January 10, 2019 - 10:58 pm UTC

Last updated: January 14, 2019 - 10:04 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

Hello,

Is it possible to write a control file to read a file in the below format (only one line containing multiple records) and split into multiple rows and simply dump into a table (containing only one column TEXT) and load/store these lines ? I dont need to split them and store into seperate column values.

Or what is the solution i got to read such file and dump them into a table with seperated lines?

Input file
ABC*2345*NewYork*NY*99999~CDE*1234*Pennsylvania*PA*99999~EFG*3456*Florida*FL*99999~FGH*4567*RhodeIsland*RI*99999~

Table_name = File_Dump
TEXT
--------------------------
ABC*2345*NewYork*NY*99999~
CDE*1234*Pennsylvania*PA*99999~
EFG*3456*Florida*FL*99999~
FGH*4567*RhodeIsland*RI*99999~

and Chris said...

I'm assuming that when you say "control file", you mean SQL*Loader.

If so, and you know how many records are on a line, you can split them out by loading into the same table N times.

For example:

SQL> ho type sqlldr.ctl
LOAD DATA
infile *
TRUNCATE
INTO TABLE t
(
text char terminated by '~'
)
INTO TABLE t
(
text char terminated by '~'
)
INTO TABLE t
(
text char terminated by '~'
)
INTO TABLE t
(
text char terminated by '~'
)
BEGINDATA
ABC*2345*NewYork*NY*99999~CDE*1234*Pennsylvania*PA*99999~EFG*3456*Florida*FL*99999~FGH*4567*RhodeIsland*RI*99999~

SQL> ho sqlldr userid=chris/chris@db control=sqlldr.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Jan 11 16:33:42 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table T:
  1 Row successfully loaded.

Table T:
  1 Row successfully loaded.

Table T:
  1 Row successfully loaded.

Table T:
  1 Row successfully loaded.

Check the log file:
  sqlldr.log
for more information about the load.

SQL> select * from t;

TEXT
----------------------------------------------------------------------------------------------------
ABC*2345*NewYork*NY*99999
CDE*1234*Pennsylvania*PA*99999
EFG*3456*Florida*FL*99999
FGH*4567*RhodeIsland*RI*99999


But this is much easier if you're using an external table. If so you can use the technique described in:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9535877800346627962

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

If external table...

Stew Ashton, January 12, 2019 - 7:13 am UTC

What would be wrong with just saying
RECORDS DELIMITED BY '~'
?



Best regards,
Stew
Chris Saxon
January 14, 2019 - 10:04 am UTC

You can. Though if the file contains several lines, you need to watch for carriage returns & trailing nulls in your data:

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen ('TMP', 'test.dat', 'w');
  utl_file.put_line(f, 'ABC*2345*NewYork*NY*99999~CDE*1234*Pennsylvania*PA*99999~EFG*3456*Florida*FL*99999~FGH*4567*RhodeIsland*RI*99999~');
  utl_file.put_line(f, 'IJK*2345*NewYork*NY*99999~');
  utl_file.fclose(f);
end;
/
create table t (
  c1 varchar2(100)
) organization external (
  default directory tmp
  access parameters (
    records delimited by '~'
    fields (
      c1 char(100)
    )
  )
  location ( 'test.dat' )
);
select rownum, c1 from t;

ROWNUM   C1                               
       1 ABC*2345*NewYork*NY*99999        
       2 CDE*1234*Pennsylvania*PA*99999   
       3 EFG*3456*Florida*FL*99999        
       4 FGH*4567*RhodeIsland*RI*99999    
       5 
IJK*2345*NewYork*NY*99999       
       6 <null>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.