Skip to Main Content
  • Questions
  • exporting csv within the limits of excel

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, javid .

Asked: July 27, 2020 - 10:51 am UTC

Answered by: Connor McDonald - Last updated: August 15, 2020 - 6:43 am UTC

Category: Database Administration - Version: 19c

Viewed 100+ times

You Asked

Hello Tom,

I need help on below question please help me


Single sensor data from different tables should be merged in one excel.
File should not exceed 4mb size and records should not exceed 100000 records.

IF single sensor data from different tables exceeds more than 4mb file or 100000 records limit then the remaining data should go to second excel.

the csv to be export in to structure folder like below Year  Month  Bi-weekly/Weekly

and we said...

You could use some logic within UTL_FILE to keep track of size and row counts and start a new file accordingly

declare

  l_file  utl_file.file_type;
  l_idx   int := 1;
  l_text  varchar2(32767);
  l_size  int := 0;
  l_rows  int := 0;
begin
  l_file := utl_file.fopen(location     => 'temp',
                           filename     => 'file'||l_idx||'.csv',
                           open_mode    => 'w',
                           max_linesize => 32767);
  for c in 
    ( select ... from table1 union all
      select ... from table2 union all
      ...
    )
  loop
   
    if l_rows = 100000 or
       l_size > 4*1024*1024 
    then
      utl_file.fclose(l_file);
      l_idx := l_idx + 1;
      l_rows := 0;
      l_size := 0;
      l_file := utl_file.fopen(location     => 'temp',
                               filename     => 'file'||l_idx||'.csv',
                               open_mode    => 'w',
                               max_linesize => 32767);
    end if;
    
    l_text := c.col1    || ',' ||
              c.col2    || ',' ||
              c.col3    || ',' ||
              c.col4    || ',' ||
              c.col5    || ',' ||
              c.col6    || ',' || 
              ...
              ...;
    l_rows := l_rows + 1;
    l_size := l_size + length(l_text);
    
    utl_file.put_line(l_file,
                      
                      c.deptno);
  end loop;
  utl_file.fclose(l_file);
  
exception
  when others then
    utl_file.fclose(l_file);
    raise;
end;
/


If you want to write out fully XLSX compatible sheets, you'd need a 3rd party solution.

There are also PLSQL versions out there, but thats a fairly big undertaking

and you rated our response

  (2 ratings)

Reviews

it was very helpful

August 13, 2020 - 2:15 pm UTC

Reviewer: javid

Thank you so much appreciate your help your a rock star.
Connor McDonald

Followup  

August 15, 2020 - 6:43 am UTC

Oracle writes to directory objects, so you would need to have a directory set up for each *pre-existing* folder that you want to write to.

Then it is just a case of changing the directory name as required.


Need lil help ( adding to my previous script only)

August 13, 2020 - 2:33 pm UTC

Reviewer: javid

Hello Tom ,

Thank you so much for help. And i need lil more help .

This is related to last question only which i asked . And you sent script also. Which was great help. Now i need lil more help my question is we need data to be export in .csv and structure should be in folder like below Year  Month  Bi-weekly / Weekly ?

Can we add / modify the script you sent me and add above requirement that data should be export into .csv in folder structure YEAR / MONTH / BIWEEKLY / WEEKLY?

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.