Home » SQL & PL/SQL » SQL & PL/SQL » Conversion into CSV file while keeping the comma
Conversion into CSV file while keeping the comma [message #185357] Tue, 01 August 2006 05:46 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to convert some select statements into CSV file. But there is comma between some records/values and they too are getting splitted while converting into CSV.
Please advice.

/******************************************************************************
SQL Script

Filename: EndItem_Vio_Item_Material_Violations.sql

Purpose: Crystal conversion script

Usage: While in SQLPlus ...

SQL> @EndItem_Vio_Item_Material_Violations.sql &1

Where &1 = Extract file name used in SPOOL command

******************************************************************************/
WHENEVER OSERROR EXIT SQL.OSCODE ROLLBACK
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SET ECHO OFF
SET ESCAPE ON
SET SERVEROUTPUT ON
SET DOCUMENT OFF
SET VERIFY OFF
/******************************************************************************
Author: Mona Singh
Date:   01-Aug-2006

Edit
History:

  01-Aug-2006 MOna Singh
	o Initial version.
******************************************************************************/
DEFINE g_file = 'EndItem_Vio_Item.csv'

PROMPT
PROMPT Beginning data extraction.

SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500
SET TRIMSPOOL ON
SET TERMOUT OFF

SPOOL &g_file
--
-- Column Headings
--
SELECT 'Planner Id,End Type,End Item,End Description,End Loc,End Qty,Seq Ord,Need Date,Vio Class,Vio Grey,Vio Item,Prnt Type,Prnt Item,Prnt Loc,P-Part Item,'
  FROM DUAL;

--
-- Detail Information
--	

SELECT DESCR1||','||DESCR2||','||DESCR3||','||replace(DESCR5,',','|')||','||DESCR4||','||DESCR6||','||
DESCR15||','||DESCR7||','||DESCR8||','||DESCR9||','||DESCR10||','||DESCR11||','||
      DESCR12||','||DESCR13||','||DESCR14||','
  FROM springs.SI_INFO_RPTING 
 WHERE REPORT = 'PS VIOLATIONS'
   AND DESCR='END ITEM MATERIAL VIOLATIONS INFO'
   ORDER BY DESCR1,DESCR5;

SPOOL OFF
SET TERMOUT ON

--------------------------------------------------
In the above script, I have a column DESCR5 having the value:
"MCSET,WAM300ETOB,STONE,FL/QN". These are getting splitted too. I want them to get into only one column. Please advice.
Re: Conversion into CSV file while keeping the comma [message #185359 is a reply to message #185357] Tue, 01 August 2006 05:56 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

It works if I use replace function to replace comma with any other letter like PIPE(|). But I need to have comma there.
With PIPE, I get it as : "FTDSH|FEDSIGNA|ALABA|QUEEN"
But I need this as "FTDSH,FEDSIGNA,ALABA,QUEEN"

SELECT DESCR1||','||DESCR2||','||DESCR3||','||replace(DESCR5,',',,'|')||','||DESCR4||','||DESCR6||','||
Re: Conversion into CSV file while keeping the comma [message #185373 is a reply to message #185359] Tue, 01 August 2006 07:13 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
if you really want the particular column DESCR5 should only contain "," apart from other character, then why dont you separate the columns with other character instead of "," like given below.

SELECT DESCR1||'~'||DESCR2||'~'||DESCR3||'~'||........


Thanks,
Thangam
Re: Conversion into CSV file while keeping the comma [message #185377 is a reply to message #185359] Tue, 01 August 2006 07:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Sorry to say ..

I am not getting your requirement ...

What is the Format of field Now .??

What is your required format ??

If it is already having comma , why do you need the replace function . ?

Rajuvan.
Re: Conversion into CSV file while keeping the comma [message #185437 is a reply to message #185377] Tue, 01 August 2006 14:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
enclose your fields with double quotes:
SELECT '"'||DESCR1
   ||'","'||DESCR2
   ||'","'
etc.


Re: Conversion into CSV file while keeping the comma [message #185479 is a reply to message #185437] Tue, 01 August 2006 23:43 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Actually I have to export the data into CSV file with the select statement above. And it does that. CSV file seperates comma seperated values into seperate columns of excel sheet.

SELECT DESCR1||','||DESCR2||','||DESCR3||','
FROM springs.SI_INFO_RPTING
From the above statement, three columns should be exported to CSV format as
COL1             COL2                            COL3
CSSET    	CSSET,DOLPHINWM,BLUE,KING       1456837 

But I am getting 6 columns which is wrong. This is because DESCR2 has the value as :CSSET,DOLPHINWM,BLUE,KING

So I am getting :
COL1     COL2     COL3           COL4        COL5      COL6
CSSET   CSSET     DOLPHINWM      BLUE         KING     1456837

Please let me know how to solve this as if I use the replace function(DESCR2,',','|')....the problem is solved as it gives CSSET|DOLPHINWM|BLUE|KING together but there is pipe in between whereas I wanted a comma there. Please advice.

[Updated on: Tue, 01 August 2006 23:44]

Report message to a moderator

Re: Conversion into CSV file while keeping the comma [message #185482 is a reply to message #185479] Tue, 01 August 2006 23:54 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Why didn't you try Frank's Suggestion ?

SELECT '"'||DESCR1
   ||'","'||DESCR2
   ||'","'||DESCR3||'"'
FROM springs.SI_INFO_RPTING ;


This will give the result as ..

"CSSET","CSSET,DOLPHINWM,BLUE,KING","1456837"


Which is Ok from csv format point of view.

Thumbs Up
Rajuvan.
Re: Conversion into CSV file while keeping the comma [message #185493 is a reply to message #185482] Wed, 02 August 2006 00:37 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks to you all....this worked. Smile
But I have one more problem that the data in the CSV file is coming not aligned. The numeric data is all aligned to right side. Even in a single column some data which is numeric is coming on right side while the other on left side.
Do you have any suggestion. I have used different combinations of single and double quotes. But it didn't worked.

Thanks,
Mona

Mona

[Updated on: Wed, 02 August 2006 04:59]

Report message to a moderator

Previous Topic: Table Grants...
Next Topic: Effects of dropping a table
Goto Forum:
  


Current Time: Fri Dec 09 15:53:30 CST 2016

Total time taken to generate the page: 0.20659 seconds