Forum Stats

  • 3,826,326 Users
  • 2,260,624 Discussions
  • 7,896,896 Comments

Discussions

Using UTL_FILE create dat file

User_IAP38
User_IAP38 Member Posts: 14 Green Ribbon
edited May 17, 2022 11:51AM in SQL & PL/SQL

Hi Team,

I need urgent help for read input csv file to convert Dat file with proper format..

can you please help me

Input file : lower.csv

loc: AD/LP/log

8888,22222,4/2/2022,5/17/2022,5,5,MAC

8888,222228,4/2/2022,5/17/2022,5,5,MACH

8888,2222289,4/2/2022,5/17/2022,5,5,MACHE

OUTPUT: real.dat

loc: AD/LP/load

08888,0000022222,20220402,20220517,5,5,MAC

08888,0000222228,20220402,20220517,5,5,MACH

08888,0002222289,20220402,20220517,5,5,MACHE


Thanks

Tagged:

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓

    If you want to do this in PL/SQL and it's something you need to do regularly (not just a one-off quick fix) then I would consider setting up the source file to be read using EXTERNAL TABLES, and that can give you each of the columns of data separated by the commas.

    Then formatting the data to give a result is simple...

    SQL> with ext_table as (
      2    -- simulate data that has been read using an External Table into separate columns
      3    select '8888' as col1, '22222' as col2, '4/2/2022' as col3, '5/17/2022' as col4, 5 as col5, 5 as col6, 'MAC' as col7 from dual union all
      4    select '8888','222228','4/2/2022','5/17/2022',5,5,'MACH' from dual union all
      5    select '8888','2222289','4/2/2022','5/17/2022',5,5,'MACHE' from dual
      6    )
      7  -- generate the required format
      8  select lpad(col1,5,'0')||','||
      9         lpad(col2,10,'0')||','||
     10         to_char(to_date(col3,'MM/DD/YYYY'),'YYYYMMDD')||','||
     11         to_char(to_date(col4,'MM/DD/YYYY'),'YYYYMMDD')||','||
     12         col5||','||
     13         col6||','||
     14         col7 as result
     15  from   ext_table
     16  /
    
    
    RESULT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    08888,0000022222,20220402,20220517,5,5,MAC
    08888,0000222228,20220402,20220517,5,5,MACH
    08888,0002222289,20220402,20220517,5,5,MACHE
    

    And from that result you can either collect the results into a CLOB and write the whole CLOB out to the output file in one go (using something like DBMS_LOB.CLOB2FILE... https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_LOB.html#GUID-006129FC-1335-4B9F-9D9A-40130F6D6F45)

    ... or take the result and write it out row by row using UTL_FILE.

    Setting up external tables is well documented online.

    Writing out data using UTL_FILE is also well documented online (as is using DBMS_LOB.CLOB2FILE).

    So, I can only imagine your issue was how to convert the data from one format to the other, which isn't that tricky (see above).

    What exactly are you having trouble with?

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    unix:~> cat lower.csv
    8888,22222,4/2/2022,5/17/2022,5,5,MAC
    8888,222228,4/2/2022,5/17/2022,5,5,MACH
    8888,2222289,4/2/2022,5/17/2022,5,5,MACHE
    
    unix:~> awk -F '[,/]' '{printf "%s,%010d,%04d%02d%02d,%04d%02d%02d,%s,%s,%s\n",$1,$2,$5,$3,$4,$8,$6,$7,$9,$10,$11}' lower.csv > real.dat
    
    unix:~> cat real.dat
    8888,0000022222,20220402,20220517,5,5,MAC
    8888,0000222228,20220402,20220517,5,5,MACH
    8888,0002222289,20220402,20220517,5,5,MACHE
    
  • Warren Tolentino
    Warren Tolentino Member Posts: 5,542 Silver Trophy

    maybe this might help.

    declare
      vInFile      utl_file.file_type;
      vOutFile     utl_file.file_type;
      vNewLine     varchar2(4000);
      vPosition    pls_integer;
      vFlag        boolean := TRUE;
    begin
      vInFile := utl_file.fopen('AD_LP_LOG', 'lower.csv','r');
      vOutFile := utl_file.fopen('AD_LP_LOAD', 'real.dat', 'w');
    
    
      if utl_file.is_open(vInFile) then
        loop
          begin
            utl_file.get_line(vInFile, vNewLine);
    
    
            vPosition := utl_file.fgetpos(vInFile);
            dbms_output.put_line(to_char(vPosition));
    
    
            utl_file.put_line(vOutFile, vNewLine, FALSE);
            utl_file.fflush(vOutFile);
    
    
            if vFlag = true then
              utl_file.fseek(vInFile, null, -30);
              vFlag := false;
            end if; 
          exception
            when no_data_found then
              exit;
          end;
        end loop;
        commit;
      end if;
      utl_file.fclose(vInFile);
      utl_file.fclose(vOutFile);
    exception
      when others then
        raise_application_error (-20099, 'Unknown UTL_FILE Error');
    end;
    
    
    
  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond
    Answer ✓

    If you want to do this in PL/SQL and it's something you need to do regularly (not just a one-off quick fix) then I would consider setting up the source file to be read using EXTERNAL TABLES, and that can give you each of the columns of data separated by the commas.

    Then formatting the data to give a result is simple...

    SQL> with ext_table as (
      2    -- simulate data that has been read using an External Table into separate columns
      3    select '8888' as col1, '22222' as col2, '4/2/2022' as col3, '5/17/2022' as col4, 5 as col5, 5 as col6, 'MAC' as col7 from dual union all
      4    select '8888','222228','4/2/2022','5/17/2022',5,5,'MACH' from dual union all
      5    select '8888','2222289','4/2/2022','5/17/2022',5,5,'MACHE' from dual
      6    )
      7  -- generate the required format
      8  select lpad(col1,5,'0')||','||
      9         lpad(col2,10,'0')||','||
     10         to_char(to_date(col3,'MM/DD/YYYY'),'YYYYMMDD')||','||
     11         to_char(to_date(col4,'MM/DD/YYYY'),'YYYYMMDD')||','||
     12         col5||','||
     13         col6||','||
     14         col7 as result
     15  from   ext_table
     16  /
    
    
    RESULT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    08888,0000022222,20220402,20220517,5,5,MAC
    08888,0000222228,20220402,20220517,5,5,MACH
    08888,0002222289,20220402,20220517,5,5,MACHE
    

    And from that result you can either collect the results into a CLOB and write the whole CLOB out to the output file in one go (using something like DBMS_LOB.CLOB2FILE... https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_LOB.html#GUID-006129FC-1335-4B9F-9D9A-40130F6D6F45)

    ... or take the result and write it out row by row using UTL_FILE.

    Setting up external tables is well documented online.

    Writing out data using UTL_FILE is also well documented online (as is using DBMS_LOB.CLOB2FILE).

    So, I can only imagine your issue was how to convert the data from one format to the other, which isn't that tricky (see above).

    What exactly are you having trouble with?

  • User_IAP38
    User_IAP38 Member Posts: 14 Green Ribbon

    hI

    Now that issue resolved thanks