Visit The New, Modern Unix Linux Community


Extracting data from tables and storing in a file


 
Thread Tools Search this Thread
Special Forums UNIX Desktop Questions & Answers Extracting data from tables and storing in a file
# 1  
Extracting data from tables and storing in a file

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.
# 2  
try again.
write a simple sql script to get what you want, I added "-- notes" at the end of the line, remove them:
Code:
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:
Code:
sqlplus myusername/mypassword@mydbname
@one
exit

If you need to automate running your script , create a shell script, one.sh
1. create one.sh:
Code:
#!/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.

2.
Code:
chmod +x one.sh

3.
Code:
./one.sh


Last edited by jim mcnamara; 06-01-2010 at 07:55 AM..
# 3  
Can this not be done in one script?

I was looking at something like this

Code:
 
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

Code:
 
select * from importcell

That results needs to go into a file called results.csv
# 4  
one shell script:
Code:
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.
# 5  
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

Code:
#!/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

# 6  
may b u can give following a try -
main changes being setting column seperator as "," and then some post formatting

Code:
#!/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

# 7  
I get this:

Code:
 
$ ./dbconnect.sh
sed: -e expression #1, char 11: unknown option to `s'


Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #615
Difficulty: Medium
You can specify multi-line strings using triple quotes in Python.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI I have a dsv file that looks like: <<BOF>> record_number|id_number|first name|last name|msisdn|network|points|card number|gender 312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M 313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M... (4 Replies)
Discussion started by: tera
4 Replies

2. Shell Programming and Scripting

Extracting characters and storing in some variable

eg: ./myProgram.sh filename.cpp I want to remove the :".cpp" extension from the filename.cpp expected output: filename (3 Replies)
Discussion started by: umesh314
3 Replies

3. UNIX for Dummies Questions & Answers

Extracting data from file

I am trying to compare the data in lines 3 & 5 to see if they match up to the '-S570' (see first code set, all proprietary information has been removed from code set) spawn telnet Trying ... Connected to CA-LOS1234-ASE-S570.cl . Escape character is '^]'. CA-LOS1234-ASE-S570 Username: ... (1 Reply)
Discussion started by: slipshft
1 Replies

4. Shell Programming and Scripting

Extracting specific lines of data from a file and related lines of data based on a grep value range?

Hi, I have one file, say file 1, that has data like below where 19900107 is the date, 19900107 12 144 129 0.7380047 19900108 12 168 129 0.3149017 19900109 12 192 129 3.2766666E-02 ... (3 Replies)
Discussion started by: Wynner
3 Replies

5. Shell Programming and Scripting

Extracting columns from a matrix and storing each column in a separate file

Hi All, I have a huge matrix file consisting some some millions rows and 6000 columns. The contents are just floating point numbers in the matrix. I want to extract each column (i.e. 6000 of them) and store each column in a separate file. For example, 1.dat will consist of elements from column... (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

6. Shell Programming and Scripting

Extracting data from tables......

HOw to extracts data from tables in database. Merges them into one output file. This output file is loaded into another tables in database. (1 Reply)
Discussion started by: nari.bommi
1 Replies

7. UNIX for Dummies Questions & Answers

Storing data from a table into a csv file

Hi I need to write a bash script to take the data stored in 3 oracle tables .. and filter them and store the results in a csv file. It is an Oracle database Thank you (1 Reply)
Discussion started by: ladyAnne
1 Replies

8. Shell Programming and Scripting

Extracting particular string in a file and storing matched string in output file

Hi , I have input file and i want to extract below strings <msisdn xmlns="">0492001956</ msisdn> => numaber inside brackets <resCode>3000</resCode> => 3000 needs to be extracted <resMessage>Request time getBalances_PSM.c(37): d out</resMessage></ns2:getBalancesResponse> => the word... (14 Replies)
Discussion started by: sushmab82
14 Replies

9. UNIX for Dummies Questions & Answers

Extracting Data from a File

Hi I need to calculate the number of occurrences of a item in a number of files using Perl. The item appears continually throughout the files but in each case I only want to calculate it in certain blocks of the file. Example - Calculalte the number of occurrences of a 'pass' in a block of... (0 Replies)
Discussion started by: oop
0 Replies

10. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

Featured Tech Videos