Hi
I am trying to write a script to extract information from a database and save that info to a csv file.
I am using sql, an oracle database
I have no idea how to even begin this.
Can somebody please help.
try again.
write a simple sql script to get what you want, I added "-- notes" at the end of the line, remove them:
set colsep ' ' -- that is: a tic a tab a tic; you cannot see a tab; this makes csv format
set head off -- don't print the header crud
set pages 0
set lines 999 -- do no wrap on col 80, this can be set larger
set trimspool on
set feedback off
spool myoutputfile.txt
select fld1, fld2, fld3 from mytable -- obviously this is made up
where fld3='something';
spool off
save that script as: one.sql then clean up the dashes
You can run the script from sqlplus:
sqlplus myusername/mypassword@mydbname
@one
exit
If you need to automate running your script , create a shell script, one.sh
- create one.sh:
#!/bin/ksh # or #!/bin/bash
sqlplus myusername/mypassword@mydbname <<EOF
@one
exit
EOF
NOTE: the last EOF has to be in column #1.
Search the forums for a 'here document', EOF could be PDQBACH or anything you like.
chmod +x one.sh
./one.sh
Can this not be done in one script?
I was looking at something like this
def make_csv()
sqlstr = <<-'SQL'
select SITEID, CELLID, "Cell Identity" from
(
select ce.siteid, ct.plancell as "CELLID", ct.allocatedci as "Cell Identity", ct.status
from mtnis.CI_translation ct
left outer join cell ce
on ct.plancell = ce.cellid
)
where status in ('Operational','Ready for service')
SQL
f_out = File.new("/tmp/#{@module}.sql", "w+"); f_out.puts sqlstr; f_out.close
result = `cat /tmp/#{@module}.sql | sqlcsv #{@verbose} \
--headers \
--user="#{TNUSER}" \
--password="#{TNPASS}" \
--dsn="#{TNDSN}" \
--stdin \
2>/dev/null`
#> #{@dsxdir}csv/#{@module}.csv.tmp1`
FileUtils.rm( "/tmp/#{@module}.sql", :force => true )
puts result if @verbose
@arri = result.split("\n")
end
This is something I came across but I don't understand it completely.
My sql statement is very basic
select * from importcell
That results needs to go into a file called results.csv
one shell script:
sqlplus -s username/passwd@dbname <<EOF
set colsep ' '
set feedback off
set lines 9999
spool results.csv
set trimspool on
set pages 999
select * from importcell;
exit
EOF
This makes a tab-delimited csv file.
set colsep ',' makes a comma-delimited csv file.
Thank you
How do I make the output more presentable
---------- Post updated at 07:44 AM ---------- Previous update was at 07:28 AM ----------
The csv file does not get created. I added a path for it
#!/bin/bash
sqlplus -s datasafe/datasafe@DS61MTN <<EOF
set colsep ' '
set feedback off
set lines 9999
spool /var/local/dsx/import/results.csv
set trimspool on
set pages 999
select * from AFFECTLEVEL;
exit
EOF
may b u can give following a try -
main changes being setting column seperator as "," and then some post formatting
#!/bin/ksh
touch tmpFile
sqlplus -s usr/passwd@sid << HERE | egrep -v "altered" > tmpFile
set feedback off
set linesize 9999
set colsep ","
select * from importcell;
HERE
sed -e "s/ //g" tmpFile | grep -v '^$' | grep -v "\-\-" > outfile.csv
rm tmpFile
I get this:
$ ./dbconnect.sh
sed: -e expression #1, char 11: unknown option to `s'
strange...!!!
the usage of sed posted above, is well tested one....
---------- Post updated at 07:45 PM ---------- Previous update was at 07:41 PM ----------
try sed with single quotes
sed -e 's/ //g' tmpFile | grep -v '^$' | grep -v "\-\-" > outfile.csv
Oracle's sqlplus has a rich set of commands that can format your results in a large number of ways, thereby rendering "sed" or "grep" or "egrep" useless.
If you use these sqlplus commands, you don't have to do extraneous processing using Unix utilities. And you don't have to create and remove temporary files either.
Here's a short script that illustrates their usage:
test@ORA11G>
test@ORA11G> -- The data in my Oracle table looks like this
test@ORA11G> select * from t;
X Y Z W
---------- - --------- ----------------------------------------------------------------------------------------------------
1 a 19-SEP-08 the quick brown fox
2 b 19-OCT-08 @#$ jumps over the ::
3 c 19-NOV-08 lazy dog...
4 d 19-DEC-08 abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789~!@#$%^&*()_+{}|:"<>?`-=[]\;',./
4 rows selected.
test@ORA11G>
test@ORA11G>
And here's the Bash shell script and its execution:
$
$ # display the content of the shell script
$ cat tst.sh
#!/usr/bin/bash
sqlplus -s test/test <<EOF >outfile.log
SET NEWPAGE NONE
SET LINESIZE 9999
SET TRIMSPOOL ON
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE OFF
SET COLSEP ","
select * from t;
exit
EOF
$
$ # source the shell script
$ . tst.sh
$
$ # check the generated flat file
$ cat outfile.log
X,Y,Z ,W
1,a,19-SEP-08,the quick brown fox
2,b,19-OCT-08,@#$ jumps over the ::
3,c,19-NOV-08,lazy dog...
4,d,19-DEC-08,abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789~!@#$%^&*()_+{}|:"<>?`-=[]\;',./
$
$
HTH,
tyler_durden