Today, I would be talking about exporting a table to csv file. This method is especially useful for advanced data manipulation within the csv file and I would also talk about how to import the csv data back into the database as a table. I will be showing Java code snippet to explain importing the csv data and the tradeoff for using sql developer. My environment is based on oracle database and so that all the utility I would be using here is targeting oracle database.
Export a table to csv file:
Method 1. Use oracle sql developer. Please take a look at the video below:
Note: This is good for small table with not much data. However, if you are dealing with a large table, I would recommend you try method 2.
Method 2. Generate the csv file from command line. Here is a sample sql file I used to generate the csv file.
set pause off set echo off set verify off set heading off set linesize 5000 set feedback off set termout off set term off spool FY11_276.csv set colsep ',' set pagesize 0 select 'BOOK_MONTH_ID','BOOK_MONTH_CODE','BOOK_MONTH_ORDER','QUARTER_NUMBER','BOOK_MONTH_NUMBER','BOOK_MONTH_NAME','QUARTER_NAME', 'DEL_COL' from dual; select BOOK_MONTH_ID,BOOK_MONTH_CODE,BOOK_MONTH_ORDER,QUARTER_NUMBER,BOOK_MONTH_NUMBER,BOOK_MONTH_NAME,QUARTER_NAME, null from claire_sample.book_month; spool off
Note: You could notice that the highlighted select statement has a last column called: DEL_COL.This column is used to fill in the space after the column separator coma.Otherwise the csv file generated from the command line would have a huge last column filled with spaces.You might need to manually delete the column if you would use oracle sql developer to import the csv file back into database later on.
To be continued…