Get column values from multi-line text file

Question

How can I populate the columns with data from a text file, where each line is a different column? The text file looks like this:

Amsterdam Aalsmeerweg  
Aalsmeerweg
14
Amsterdam
NL
1059NJ
0204122131

Amsterdam Bos en Lommerweg
Bos en Lommerweg
215
AMSTERDAM
NL
1055DT
0206847676

Amsterdam Ceintuurbaan  
Ceintuurbaan  
314 
AMSTERDAM
NL
1072GL
0204705292

This post didn't help me.

My code looks like this:

set serveroutput on;
 CREATE or replace DIRECTORY USER_DIR AS '/home/renejanssen/'; 
 GRANT READ ON DIRECTORY USER_DIR TO PUBLIC;
 declare
  V1 VARCHAR2(200); 
  F1 UTL_FILE.FILE_TYPE; 
  f_name WINKEL.NAAM%type;
  f_adres varchar2(255);
  f_homenr WINKEL.HUISNR%TYPE;
  f_city varchar2(100);
  f_countrcode varchar(255);
  f_zipcode WINKEL.POSTCODE%TYPE;
  f_phonenr WINKEL.TELNR%TYPE;
  v_counter number DEFAULT 1; --counter for ID
 BEGIN
  F1 := UTL_FILE.FOPEN('USER_DIR','test','R');
   Loop
    BEGIN
     UTL_FILE.GET_LINE(F1,V1);
     dbms_output.put_line(v_counter || ' = ' || V1);
     EXCEPTION WHEN No_Data_Found THEN EXIT;
     dbms_output.put_line('niets'); 
    END;                                
   f_name     := substr(V1,1); 
   f_adres     := substr(V1,1);
   f_homenr    := substr(V1,1);                    
   f_countrcode  := substr(V1,1);
   f_zipcode  := substr(V1,1);        
   insert INTO WINKEL (ID, NAAM, HUISNR, POSTCODE, TELNR ) 
   values(v_counter, f_name, f_homenr, f_zipcode, f_phonenr);           
   v_counter := v_counter +1;
 end loop; 
 IF UTL_FILE.IS_OPEN(F1) THEN
 dbms_output.put_line('File is Open');
 end if;                 
 UTL_FILE.FCLOSE(F1); 
END;

Are there always 7 lines for each entry? If not are you using a blank line as the delimiter, and how would you map lines in the file variables? — Alex Poole, May 13 '16 at 15:55
I dont know, what do you suggest. All i know is that each line needs a variable. — ReneJanssen, May 13 '16 at 15:58
If it's a school project, it is probably safe to assume every "record" is seven lines, in the exact same order every time, separated by blank lines. — , May 13 '16 at 16:41

score 1 · Accepted Answer · answered May 13 '16 at 16:09

If each entry in the file always has seven lines as you've shown, followed by a blank line, you can do seven reads directly into your PL/SQL variables:

declare
  v1 varchar2(200);
  f1 utl_file.file_type;
  f_name winkel.naam%type;
  f_adres varchar2(255);
  f_homenr winkel.huisnr%type;
  f_city varchar2(100);
  f_countrcode varchar(255);
  f_zipcode winkel.postcode%type;
  f_phonenr winkel.telnr%type;
  v_counter number default 1; --counter for ID
begin
  f1 := utl_file.fopen('D42','test','R');
  loop
    begin
      utl_file.get_line(f1, f_name);
      utl_file.get_line(f1, f_adres);
      utl_file.get_line(f1, f_homenr);
      utl_file.get_line(f1, f_city);
      utl_file.get_line(f1, f_countrcode);
      utl_file.get_line(f1, f_zipcode);
      utl_file.get_line(f1, f_phonenr);
    exception
    when no_data_found then
      exit;
      dbms_output.put_line('niets');
    end;
    insert into winkel(id, naam, huisnr, postcode, telnr)
    values (v_counter, f_name, f_homenr, f_zipcode, f_phonenr);
    v_counter := v_counter +1;
    begin
        -- consume possible blank line; don't error as final entry may not have one
    utl_file.get_line(f1, f_adres);
dbms_output.put_line('extra line: ' || f_adres);
    exception
    when no_data_found then
dbms_output.put_line('extra line: ' || sqlerrm);
      null;
    end;  
  end loop;
  if utl_file.is_open(f1) then
    dbms_output.put_line('File is Open');
  end if;
  utl_file.fclose(f1);
end;
/

With your data file that populates a table with:

select * from winkel;

        ID NAAM                                     HUISNR     POSTCODE   TELNR        
---------- ---------------------------------------- ---------- ---------- --------------
         1 Amsterdam Aalsmeerweg                    14         1059NJ     0204122131    
         2 Amsterdam Bos en Lommerweg               215        1055DT     0206847676    
         3 Amsterdam Ceintuurbaan                   314        1072GL     0204705292

Okey Alex, that helps me alot, i can work from there. Thanks! — ReneJanssen, May 13 '16 at 16:34
@ReneJanssen - if the answer is indeed what you needed, it is customary to mark it as "correct answer" - that way the question will not be on the list of unanswered questions. This will help save time for volunteers on the forum. Cheers! — , May 13 '16 at 16:44

Get column values from multi-line text file

1 Answers1