CSV format translation

SANbuddies
SANbuddies used Ask the Experts™
on
Hei folks,

I have had developed SQL scripts for reporting data from different tables of the database but

--I used SQL tools to run the scripts and  report the data.I want this data to be presented into the CSV format .

---I want that this data reported in SQL tools be presented in CSV format as well as reported in textpad automatically instead of SQL tools as it is currently.I need this data to be presented in CSV format because this data has to be transported to a repository by a utlity which understands SCV format.


Thanks

Looking forward.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Please refer to the following experts exchange question for information on creating csv files from Oracle:

Create CSV File

Author

Commented:
Mill..,

I have fields from different tables of the database.
First I created a view and then generated headers as told by you in this mentioned post with the necessary script for CSV format.

But it gives errors

SP2-0734: unknown command beginning  "......." -rest of line ignored.

Pls advice
It sounds like a problem with the syntax of your code.  Can you please list the code unit that is causing the problem.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Author

Commented:
Mill..

Please find the attachment.

Now it gives error

ORA-00904: "WEIGHT_NET": invalid identifier.

thanks
code.txt

Author

Commented:
Mill,,

I have replaced the dual table with the view I created so the data is now reported in sql plus in the csv format.


But there is a problem.

There is a lot of space between values reported.

Can you tell me if there is a possibility that the CSV file is generated in the correct format
instead of using the "save as " feature.

THanks
The padding is caused by Oracle space filling the fields based on the size of the columns.

Depending on what application is opening the csv file this may or may not cause a problem.  For example, if loading the file into Excel it doesn't matter.

If you definitely need to remove the padding space then you could write you sql as follows:

select project||','||itemname||','||system||','||status||','||calibrated_range_from||','|| calibrated_range_to||','||calibrated_range_unit||','||scaled_range_from||','||scaled_range_to||','||
   scaled_range_unit||','||setpoint_ll||','||setpoint_l||','||setpoint_h||','||setpoint_hh||','||setpoint_unit||','||location_code||','||is_nonis_code||','||remarks||','||po_no||','|| description||','||serial_no||','||
   tag_type||','||ex_code||','||gas_group||','||temp_class||','|| ip_grade||','||contr_code||','||engineering_code||','||discipline_code||','||sub_system||','|| main_system||','||contractor_installation||','||po_call_off||','||
   supply_code||','||fire_area||','||pzv_category||','||plant_no||','||weight_flag||','|| signal_level||','||instance_no||','||back_pressure||','||cold_set_pressure||','||req_heat_trace_temp||','||safety_integrity_level||','||
   date_installed||','|| pzv_date||','||Ex_certificate||','||weight_net
from my_view;

What this does is concatenates all the columns together to produce a comma separated string of values.  By formatting the SQL statement like this, you no longer need to set "colsep ',' "  as you now only have 1 column being selected, not multiple columns.

Author

Commented:
Mill...


The output is not what i desire . I mean the CSV format.
Between the header fields in the output CSV  lies ||','|| and in a origianl CSV its just a comma between the header fields and its values.

The header values show  correctly.

Regards
You should only be getting a comma between fields, not ||','|| so there is something about the way you are running the script.  The "||" tells Oracle to concatenate a string, so all this is doing is concatenating a comma to the selected values.

Try this:


set linesize 4000 pagesize 0
set heading off feedback off verify off trimspool on trimout on

spool mycsvfile.csv

select 'col1'||','||'col2'||','||'col3'||','||'col4'
from dual;

spool off

After running, if you view the mycsvfile.csv it should contain the following:

col1,col2,col3,col4

If it is possible that some of the column values may contain commas, you can include double quotes to ensure these aren't treated as delimiters e.g.

select '"'||'col1'||'","'||'col2'||'","'||'col3'||'","'||'col4'||'"'
from dual;

This will result in each value (and usually this would only be a character value as these are the only types that are likely to include a comma) being surrounded by double quotes:

"col1","col2","col3","col4"

Author

Commented:
It does report the headers correctly but no values this time.
I want to report header fields first and than after it the header values.

Thanks .
When you say it doesn't report header values, do you mean your "main" select statement isn't returning any rows?  Can you provide a sample of your script.  I can check the syntax but I will be unable to determine why data isn't being returned by your queries.

Author

Commented:
Actually before putting the single commas i.e. col1||','||col2||,||col3||','||col4 the header fields were not reported but only the values for the different header fields.

After that you suggested to use single quotes i.e. 'col1'||','||'col2'||','||'col3'||','||'col4'
the header fields were reported but this time no values for the different header fields.

There is a view created by me firstly where i have defined aliases for different fields in the databases and I use just theses aliases when I try to generate the CSV file.

Example of the view

create or replace view AS

select
table2.field1 as system,
table1.field1 as date_installed,
from table1,
table2
where table2.field_id=table1.field_id;

so when i try to generate the CSV I use the format like

set linesize 4000 pagesize 0
set heading off feedback off verify off trimspool on trimout on

spool mycsvfile.csv

select 'system'||','||'date_installed'
from view;

spool off
exit;

P:S I have tested the views and there values and header fields are reported correctly.
THe problem arises when I try to generate CSV file as suggested by you.

Thanks
The example I provided that selected from "dual" was using literal values to simulate the different columns.  As they were literals they had to have single quotes around them.  Dual is a table that contains only one column, with one row so it isn't possible to select multiple columns, hence the reason for using literals to simulate multiple values.

When you select from your view, you don't need to put your column names in single quotes as this will simply change them to literal values.

If you change your statement to read:

select system||','||date_installed
from view;

That should work.  I would expect a single record to appear in the file for each row retrieved from the table.  In this record you should see the database values for the system and date_installed columns, with a comma between them.
If you want to include both headers and values, you will need to include 2 sql statements in your script; one to generate the headers and then a second statement to generate the values.

The statement generating the headers would likely select from dual as this will ensure only one row is retrieved.  The header text would be a bunch of literal values.  e.g.

-- Write the header record
select 'system,date_installed'
from dual;

-- Write a record for each row
select system||','||date_installed
from view;

Author

Commented:
Mill,

Thanks.

some of the last things i wanted to ask :)

currently there are trailing commas at the end of each line when i open the CSV file with notepad i.e.
header_field1,header_field2,,,,,,,,,,,,,,,,
header_value1,header_value2,,,,,,,,,,,,

I want to get rid of them.

And secondly

How can I just get the output results and not the results with the queries in the CSV file as currently i am getting both results and queries.

Thanks a lot
If I understand you correctly you are saying the sql statement (query) is appearing in the spool file output along with the data.

If this is the case then you can turn this off by adding an additional set command at the beginning of the script e.g.

set echo off

Regarding the trailing commas, this is due to columns in your view having null values.  If you don't want these columns to have any impact on your output then don't select them in the query.  If you are asking if it is possible to conditionally exclude commas, then yes you can, but this is unusually in a csv file as you normally want every record to have the same number of fields.

You can use the nvl2 function to check if the column has a value.  If it does, then you concatenate a comma, if it doesn't then you concatenate a null i.e. nothing.  However, doing this will cause the fields in different records to appear in different columns as the commas control the number of fields.

e.g.
select system||','||date_installed||nvl2(col3,',',null)||col3||nvl2(col4,',',null)||col4
from view
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>-- Write the header record

If you are using sqlplus, no need to select from dual, just use the sqlplus prompt command:
prompt header1,header2,header3

>>currently there are trailing commas at

I would not remove the trailing commas but it is your system...

How will you know what value goes with what column?

Say you have the table: col1,col2,col2
and data:
'a',null,'c'
'1','2',null


your CSV should be:
a,,c
1,2,,

If you remove the extra commas, you can lose position.

Author

Commented:
Mill

All seems fine now.

The formatting has to be done manually in the textpad.I want to avoid it too.

Like

xxx,xxxx,xxxx,xxxx,xxxx,
xxxx,xxx,xxx,xxx,xxx,xx
,xxx,xxx,xxx,xxx
  ,xxx,xxx,xxx,xxx,xx

yy,yyy,yy,yy,yy,,yy,yy,yyy,yyyy,yy

where x is the header fields and y are header field values.Both header fields and header fields values are of the same number so no records are missing now.

But how to remove the space between header fields and header fields values so that everything is looks together without manual  work.

Thanks
I notice in your example, you are showing the header line over multiple lines.  Is this because the header line has wrapped around over the multiple lines i.e. is the text editor wrapping the header line in order to show it all?

If so, is it actually a blank line between the header and the data or is it possibly that the header line has trailing spaces that have wrapped onto a line making it appear like it is blank.

You could try setting linesize to a smaller value.  The examples above show it being set to 4000, although the trimspool setting should remove trailing blanks.

Based on the examples above, I can't see anything that would cause a blank line between the heading and the data, so it may be something you have introduced into your script.  Is it possible to list your script so I can have a look?

Author

Commented:
Hey Mill,

I have worked on it and all seems perfect now.
Thanks for all the help.


Mill:- I just wanted to run the script through a custom buttom.
Is this easy to implement and in what way?

Regards..
That's good news.

I'm not sure what you mean by a custom button.  I see in your question you talk about using SQL Tools so I assume custom buttons are something you setup in SQL Tools, which is a tool I'm not familiar with (I use TOAD myself - brilliant product).

Author

Commented:
Mill.

I mean to say that instead of running the script through sqlcmd or sqlplus, we can have a button for the user to get the data output?


Regards
Sorry, I'm not sure where you would define the button as I don't know the software you are running the script from.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial