CODEWITHSUNDEEP
sqloracle-database
Eoin2211
Eoin2211

Reputation: 911

Export .txt from Sql issue

I want to export a .txt file from a sql script. I don't want any headings. I also don't want any commas (,) dividing my fields and I want them to keep the " which surrounds each value in my table. My code runs but includes headings, removes " from each field and inserts a ,. My code 

set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 1000
set serveroutput off



spool New_Members.txt

SELECT   'Unique_ID, Name, Alt_Name, Entity_Type, Party_Type,   Reference_#, Addr1, Addr2, Addr3, Town, County, Postcode, 
      Country,ALT_ADDRESS1,ALT_ADDRESS2,ALT_ADDRESS3,ALT_TOWN,ALT_COUNTY,ALT_POST_CODE,ALT_COUNTRY,NATIONALITY,
      DOB,INDIVIDUAL_ID,INDIVIDUAL_ID_TYPE,COUNTRY_OF_REGISTRATION,COMPANY_ID,COMPANY_ID_TYPE,SOURCE_COUNTRY,SOURCE_SYSTEM,TRANSACTION_TYPE' 
from     dual;
/

select   Unique_ID||','||Name||','||Alt_Name||','|| Entity_Type||','||Party_Type||','||Reference_#||','||Addr1||','||Addr2||','||
     Addr3||','||Town||','||County||','||Postcode||','||Country||','||ALT_ADDRESS1||','||ALT_ADDRESS2||','||
     ALT_ADDRESS3||','||ALT_TOWN||','||ALT_COUNTY||','||ALT_POST_CODE||','||ALT_COUNTRY||','||NATIONALITY||','||
     DOB||','||INDIVIDUAL_ID||','||INDIVIDUAL_ID_TYPE||','||COUNTRY_OF_REGISTRATION||','||COMPANY_ID||','||COMPANY_ID_TYPE||','||
     SOURCE_COUNTRY||','||SOURCE_SYSTEM||','||TRANSACTION_TYPE
from     dbo.Temp_Weekly_Export_File;
/


spool off;

exit

Upvotes: 0

Views: 62

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You already have set header off, so you won't see any Oracle-generated column headings. Setting pagesize to zero would also suppress the headers. You may find it helpful to also set trimout on and set trimspool on.

What you seem to be seeing is the CSV header that you are explicitly generating with your first query. If you don't want to see that header line, just remove it from your script.

If you keep that header, remove the extra whitespace and particularly the line breaks, as they will cause the header to be treated as multiple rows when import to Excel etc. If you were trying to stop that line being too long in your script, you could concatenate multiple shorter strings instead:

select 'Unique_ID,Name,Alt_Name,Entity_Type,Party_Type,Reference_#,'
  || 'Addr1,Addr2,Addr3,Town,County,Postcode,Country,ALT_ADDRESS1,'
  || 'ALT_ADDRESS2,ALT_ADDRESS3,ALT_TOWN,ALT_COUNTY,ALT_POST_CODE,'
  || 'ALT_COUNTRY,NATIONALITY,DOB,INDIVIDUAL_ID,INDIVIDUAL_ID_TYPE,'
  || 'COUNTRY_OF_REGISTRATION,COMPANY_ID,COMPANY_ID_TYPE,'
  || 'SOURCE_COUNTRY,SOURCE_SYSTEM,TRANSACTION_TYPE' 
from dual;

You are running both of your queries twice, because each of them is terminated with a semicolon (;) which submits that statement; and is then followed by a slash (/), which re-executes the current command buffer. You only need one or the other, so I'd remove both the slashes - but whichever you remove, be consistent, and check if there are coding guidelines in your organisation which prefer one over the other.

As noted in comments, your question is a bit confused, as you're explicitly adding commas and there are no double-quotes to remove. You may actually want to add double-quotes though, if any of the column values can contain the comma delimiter - which would confuse Excel (or whatever will use this file). You can concatenate double-quotes around specific fields as required, e.g.:

select Unique_ID||','||Name||','||Alt_Name||','|| Entity_Type||','||
  Party_Type||','||Reference_#||',"'||Addr1||'","'||Addr2||'",'||
...

which would enclose the first two address column values in double-quotes in the output.

Upvotes: 1

Related Questions