SPOOL 'C:\Users\tony\Downloads\test.csv'
SET COLSEP '|'
SELECT /*csv*/ * FROM hr.employees;
SPOOL OFF;
-- -- SPOOL END;
With next error:
--[Error] Execution (77: 1): ORA-00900: invalid SQL statement
Could you help me please
Thank you so much
Hi Antoine,
I don't get an error with this script when I try it. See below (That's version 16.2 - maybe you are using a different version?)
But - Toad doesn't do anything with the /*CSV*/ hint. That is a SQL Developer-specific command. We shouldn't throw an error on it, though (and for me it isn't). If you turn on spool SQL in Toad, (main menu -> Database -> Spool SQL -> Spool to screen), you should be able to look through the output and see what statement Toad is trying to run that is causing a problem.
We could maybe add support for creating CSV output with this syntax for a future version. I didn't realize SQL Developer did that until I googled it after seeing your post.
What is the correct way to output to file with a header row? I can save from the grid, but what I'd rather do is output the file to a folder so it's ready to refresh in a preformatted excel file. However matching the format is proving to be a bit challenging.
Hi @Toad.user
You can right click in a grid and choose "Export Dataset", then save to the "Excel File" format. There is a checkbox in that dialog to include headers.
If that doesn't solve your problem, you'll need to be more specific about what you are trying to achieve. I don't know what you mean by "output the file to a folder so it's ready to refresh in a preformatted excel file".
I sort of understand what that means, I guess, but I don't know what kind of format you want in order to achieve that. Can you provide a small example?
I've been using the export which works fine. In this case I'm batching a series of tables. It seems I can use SPOOL append to create a header row first and then append the rest, however in the editor output there's no space after the header row, but when it writes to file there is. Is there a way to suppress that?
When I run this in the Editor with F5, I see the same thing in the editor as I do in the file.
Are you doing something differently?
Also, would Export Dataset with the "Delimited Text" format do what you want? You could set it up to export multiple tables using Automation Designer, like this:
I'm not using the automation designer, though I may get around to that. At the moment I'm just trying to confirm a reliable format that will produce a header and table data for a pre-existing excel format.
SET PAGESIZE 0
SET TRIMSPOOL ON
SET HEADSEP OFF
SET ECHO OFF
SET EMBEDDED OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 2000
SPOOL 'C:\TEST.TXT';
SELECT
'ROW1'||CHR(124)
,'ROW2'||CHR(124)
,'ROW3'||CHR(124)
FROM DUAL;
SPOOL 'C:\TEST.TXT' APPEND
SELECT
COL1 ||CHR(124)
,COL2 ||CHR(124)
,COL3 ||CHR(124)
FROM MY_TABLE
;
SPOOL OFF;
I see what you mean now, with extra line breaks in the file.
I just started playing with your SET commands and got lucky. Take out SET TRIMSPOOL ON and then the file matches what you see in Toad.
That did the trick, thanks. Now I just have to figure out how to trim off the white space that's padding some of the values, which trim doesn't seem to solve. When Toad is writing to file is there any kind of order of operations that would prevent trim functions from removing white space?
Never mind, I solved this one myself. Seems you have to concatenate all columns with delimiters to control the output. Now I think we have a working file format. Thanks again.
You're welcome.
