1

I need to export data from a table containing around 3 million data. The table has 9 columns and is in the following format:

Source | Num | User | Offer | Simul | Start_Date | End_Date | Label  | Value
coms p | 0012| plin | synth | null  | 04-JAN-15  | 31-JAN-15| page v | 8

However when I am opening the csv file using notepad++ only around 600 000 lines are displayed and the display is as follows:

coms p                                     ,12,plin                                          ,synth                                                                                                ,                                                                                                    ,04/01/2015 00:00:00,04/01/2015 00:00:00,page v                                                                                                                                   
8

As you can see there are lots of spaces in some fields despite having none in the field in the table, the 0012 value of Num field is displayed as 12 and the last field is on another line.

What's more is that there is an empty line in the csv between 2 rows of the table.

Any idea of how to make those useless spaces disappear, and how to display the whole row data in a single row in the csv, how to make the 00 appear for the Num field and why is it that only 600 000 is being displayed in Notepad++? I read that there is no row limit for csv files.

The sql I am using is below:

SET SQLFORMAT csv
SET HEAD OFF

spool /d:/applis/test/file.csv

select * from TEST;

spool off;
refresh
  • 1,319
  • 2
  • 20
  • 71
  • 1
    What are you running this in? SQL Developer, TOAD, Sql*Plus? And what version? It makes a difference - `SET SQLFORMAT csv` is not a standard option. – kfinity Sep 25 '17 at 14:25

2 Answers2

3

First, there are much easier ways to export a CSV if you're using SQL Developer or TOAD.

But for sql*plus, you can use set linesize 32000 to get all the columns to display on a single line, and set pagesize 0 will get rid of the initial CRLF. But it's displaying the columns in fixed-width format because that's how spool output works.

If you want to have variable-width columns, the most portable standardized way is to manually concatenate the columns yourself and not use select *. 

set linesize 32000 -- print as much as possible on each line
set trimspool on   -- don't pad lines with blank spaces
set pagesize 0     -- don't print blank lines between some rows
set termout off    -- just print to spool file, not console (faster)
set echo off       -- don't echo commands to output

set feedback on -- just for troubleshooting; will print the rowcount at the end of the file

spool /d:/applis/test/file.csv
select col1 || ',' || col2 || ',' || col3 from TEST;
spool off
kfinity
  • 8,581
  • 1
  • 13
  • 20
0

It might be because of the column length in the database. Suppose Source column length in database is 50, then it with take a length of 50 character in the file.

Try trimout and trimspool in query as:

SET TRIMOUT ON

SET TRIMSPOOL ON
Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
prashant sugara
  • 321
  • 1
  • 7
  • Yes I think you are right. The first column is indeed a 50 bytes. However, do you have an idea why only 600 000 lines are shown when I open the csv in notepad++? The table contains around 3 million lines. – refresh Sep 25 '17 at 12:55
  • Not sure about it, will let you if i am able to find out the reason behind it. – prashant sugara Sep 25 '17 at 13:13
  • you can use SET AUTOTRACE ON to check if it is processing all the records from table or processing only 600000 records – prashant sugara Sep 25 '17 at 13:28