SQLPLUS - gives too many spaces between semicolons

Question

Just a little bit about what I did that made me make this post. I created Sharepoint, uploaded the files that I took from PL/SQL Developer (.csv) and linked those to my Excel file. Then I thought I could automate the process with SQLPLUS.

After refreshing the Excel I saw that Excel is not happy with the formatting of this file created with this tool. It recognizes the file, but doesn't recognize the values when there are so many spaces between semicolons. There is also a strange symbol in the first column header that I cannot get rid off. This also causes the problem.

I could successfully update the Excel when I got rid off those spaces and this symbol.

It wants the file to look like this:

DATUM;PROZESS;MENGE
27.04.2023;1150501002109;332
27.04.2023;1150502002006;1358
27.04.2023;1150502022040;121
27.04.2023;1150503001014;2060
27.04.2023;1150503004014;2784
27.04.2023;1150503007014;13655
27.04.2023;1150503022014;8895
27.04.2023;1150503034014;6

This is how I set up the parameters:

SET echo off
SET heading   on
SET underline off
SET newpage 0
SET linesize 2000
SET colsep ';'
SET trimspool on
SET tab off
SET pagesize 50000 embedded on
SET feedback off
SET wrap off
SET trimout on
alter session set NLS_DATE_FORMAT = 'dd.mm.yyyy';

Is there something that could solve my problem?

I'd be very grateful for the help!

Answer 1

Writing a CSV file from SQL*Plus is significantly easier when you use the command set csv mark on. If you really need semicolons instead of the standard commas, you can run set mark csv on delimiter ';'.

For example, create a file named "script.sql" like this:

-- If you really want semicolons, use: set mark csv on delimiter ';'
set mark csv on
set feedback off
set termout off
spool test.csv
select level a, level b from dual connect by level <= 100;
spool off

Then run @script.sql in a SQL*Plus session.

Unfortunately, there will still be an empty line at the top of the file. This is caused by the currently unfixed bug "32970199 : EMPTY LINE IS ADDED AS FIRST LINE IN SPOOL FILE WITH SET HEADING OPTION". If that extra line is a problem, you'll need to either set the heading off or have a script remove the first line of the file.

Your original script contained the command SET newpage 0. According to the manual, "A value of zero places a formfeed at the beginning of each page (including the first page) and clears the screen on most terminals." But on Windows I get the same female symbol, and on my Red Hat Linux terminal, the command doesn't seem to do anything.