Export sqlplus query result to CSV with Export-CSV

Question

I am running some queries in SQLPLUS inside Powershell 2.0

I am wanting to output the content of a table to csv using Export-CSV. It looks something like this.

$query = "set hea off`n"

$date1 = "ADD_MONTHS(TRUNC(SYSDATE, 'MM'),-3))"

$query += "DROP TABLE CHRIS_TEMP;CREATE TABLE CHRIS_TEMP AS SELECT FIELDS FROM TABLES;
exit;"

$sqlexec = ($query | sqlplus -s login/pass@db)

$sqlexec

$export= ("set wrap off 
set linesize 30000
select * from CHRIS_TEMP;" | sqlplus -s login/pass@db ) 
$export | Export-Csv   c:\users\*****\test.csv -notypeinformation

But the output is all one single field. Can anyone help with this issue? I would prefer not to spool out CSV in sqlplus.

What does the $export look like when printed on screen?

vonPryz
– vonPryz

2013-04-19 19:44:07 +00:00
Commented Apr 19, 2013 at 19:44 — vonPryz
– vonPryz, Commented Apr 19, 2013 at 19:44

marceljg · Accepted Answer · 2013-04-22 17:15:16Z

1

The problem is without separators Powershell has no idea how your data is structured. As soon as you add the comma, it becomes very easy to handle.

Otherwise, you are back to attempting to parse the data, since a blank column is not going to look much different then trailing spaces.

Using a direct connection to the database will give you more flexibility then using SQL plus. Your data will be in a structured record you can do what you want with.

When I do use SQLPlus inside a powershell script, I write the SQL Script programatically using file fragments to simplify the code, and send that to sqlplus rather then pipe the SQL itself to SQLPLUS. This makes it easy to add such commands as colsep , and other formatting commands.

answered Apr 22, 2013 at 17:15

marceljg

1,0677 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris_PEI Over a year ago

I switched to direct connect and and filled a table worked like a charm, exported using nimizen's metheod above. You both helped me a great deal with this.

nimizen · Accepted Answer · 2013-04-19 22:19:06Z

1

Can't test this right now but try piping your $export to Select-Object:

$export | 
    Select-Object Column1, Column2, etc | 
    Export-Csv   c:\users\*****\test.csv -notypeinformation

answered Apr 19, 2013 at 22:19

nimizen

3,4292 gold badges26 silver badges35 bronze badges

2 Comments

Chris_PEI Over a year ago

Thanks for getting back to me, this outputs a CSV with only the headers. However I combined it with the answer from marceljg and was able to get what I needed by dropping the SQLPLUS portion and direct connecting

nimizen Over a year ago

Thanks for feeding back about your solution, glad this helped!

Collectives™ on Stack Overflow

Export sqlplus query result to CSV with Export-CSV

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related