I am trying to substitute a Python/pandas export process and go direct from Oracle to csv. I have seen a couple of posts such as this one here
Suppose I have a table in Oracle with three columns: ColA, ColB, ColC. I want to employ a command line utility that takes as input an SQL command and generates a CSV file that would look like any standard CSV file with a header line and rows of values:
ColA,ColB,ColC
A,B,C
D,E,F
...
In SQL Server, there is a utility called bcp I can use which looks like this:
bcp "SELECT 'ColA', 'ColB', 'ColC' UNION ALL Select ColA, ColB, ColC From
myTable" queryout "C:\Temp\csvfiles\output.csv" -c -CRAW -S localhost -u
my_userid -p my_password -d my_database
Easy-peasy. But in looking for the Oracle equivalent, not so much.
I took a look here but the author wants you to create a UTL_FILE. That doesn't seem like a possibility since this is supposed to be a completely generic process that gets built and run from Python. I took a look at sqlplus but I can't seem to come up with a way to create a typical CSV similar to bcp. The docs here weren't terribly clear on this either. It's such a common format I expected to see tons of examples.
Sites like this here show how to output a fixed-length CSV using sqlplus but don't show you how to add a header row and values are padded, which might be OK, but not desirable. This was about the best I could come up with:
set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 200
set numwidth 50
SET TERMOUT OFF
Spool output.csv
SELECT ColA,
ColB,
ColC
FROM MyTable;
Spool off
exit
So, is there an equivalent tool/command in Oracle to the trusty bcp, or is there a sample somewhere using sqlplus that can generate a csv similar to the one above with a header line and without all the leading spaces? Or is there another option to generate dynamic CSV files from random Oracle tables? Or am I stuck using Python, pyodbc and pandas to get my nicely-formatted CSV files? TIA
