Visit Our UNIX and Linux User Community


How to read from txt file and use that as an array


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers How to read from txt file and use that as an array
# 1  
Old 10-05-2007
How to read from txt file and use that as an array

Hi Guys

How u all doing?

I am having tough time to achieve this I have a unix .ksh script which calls
sql script

Right now I harcoded column id's in sql script but I want to read them from a txt file
1084,1143,1074,1080,1091,1090,1101,1069,1104,1087,1089,1081

I want to read this file as an array in my sql script


My ksh script is



#!/usr/bin/ksh
DIRNAME=`dirname $0`
# set up environment for oracle based SMO database instance
. $DIRNAME/defaults_env.ksh

echo "Enter the region (ie. 001 or 002): "
read sdzone;
export userFile="cvsusers.txt"
LOGFILE=$DIRNAME/cvs_users_defaults.log
TIME=`date`

#checks to see if the log files is present, if not then creates it
if [ -a $DIRNAME/cvs_users_defaults.log ]
then
:
else
touch $DIRNAME/cvs_users_defaults.log
fi

#Checking to see if a data file(cvsusers.txt) exists for a load.
if [ -a $DIRNAME/$userFile ]
then
# The list of userIds is supplied from userId file


INFILE=$userFile;
echo "Using file $INFILE as input for user id";
echo " ";
echo "Started Process on $TIME" >> $LOGFILE
echo "Calling procedure to initialize users"
for userId in `cat $INFILE`
do
echo "Storing User Defaults for User " $userId >> $LOGFILE
userIdLength=`expr length $userId`
if [ $userIdLength -gt 8 ]
then
echo "Length of UserID " $userId " is too long">> $LOGFILE
else
echo "connecting to SQL">> $LOGFILE

if [[ $sdzone = '001' ]]; then
sqlplus -s $ZONE1_USERNAME_525/$ZONE1_PASSWRD_525@$ZONE1_DBNAME_525 \@$DIRNAME/sql/cvs_users_defaults.sql $userId >> LOGFILE
echo "connected to SQL">> $LOGFILE


elif [[ $sdzone = '002' ]]; then
sqlplus -s $ZONE2_USERNAME_525/$ZONE2_PASSWRD_525@$ZONE2_DBNAME_525 \@$DIRNAME/sql/cvs_users_defaults.sql $userId >> LOGFILE
echo "connected to SQL">> $LOGFILE


else
echo "Invalid region code!"
exit;
fi

fi
done


#rename the file after processing
logtime=`date "+%y%m%d%H%M%S"`
newUserFile="cvs_users_processed_"$logtime".txt"
mv $userFile $newUserFile

echo "Ending process of setting user defaults at $TIME " >> $LOGFILE
echo "Check the log file at $LOGFILE"
exit 0
else
#IF "custsoncologyusers.txt" file does not exist in DIRNAME then end gracefully
echo "No user default file ($userFile) is available to load"
echo "No user default file ($userFile) is available to load" >> $LOGFILE
echo "Ending user default process with a return code 0 at $TIME" >> $LOGFILE
exit 0
fi


This is my sql script( cvs_users_defaults.sql)

WHENEVER SQLERROR EXIT SQL.SQLCODE;
set serveroutput on;
set verify off;
set feedback off;

declare
userId s_user_cust_acct.user_id%TYPE := '&1';
TYPE t_col_id_tab IS TABLE OF number;
my_col_id t_col_id_tab;
TYPE t_col_seq_num_tab IS TABLE OF number;
my_col_seq_num t_col_seq_num_tab;
TYPE t_all_col_id_tab IS TABLE OF number;
all_col_id t_all_col_id_tab;
tcount number;
begin
select count(*)
into tcount
from s_column_atrb
where user_id = UPPER(userId)
and screen_typ_cd = 0;
if (tcount > 0) THEN
dbms_output.put_line('updating s_column_atrb' || userId);

-- For Orders(I hard coded the array here but I want to read this from .ksh script by feeding it with a txt file)
all_col_id := t_all_col_id_tab(1069,1071,1072,1073,1074,1075,1076,1077,1078,1080,1081,1082,1083,1084,1085,1086,108 7,1088,1089,1090,1091,1101,1102,1103,1104,1114,1115,1116,1117,1118,1119,1120,1122,1123,1124,1125,112 6,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,114 6,1147,1148,1149,1150,1151,1152,1154,1169,1178,1182,1183,1195);
my_col_id := t_col_id_tab(1084,1143,1074,1080,1091,1090,1101,1069,1104,1087,1089,1081);

my_col_seq_num := t_col_seq_num_tab(4,12,2,6,7,8,10,1,9,3,5,11);

For i in 1..12 LOOP
dbms_output.put_line('my_col_id(' || i || ') is ' || my_col_id(i));
update s_column_atrb
set show_hide_ind = 'Y', column_seq_num = my_col_seq_num(i)
where user_id = UPPER(userId)
and screen_typ_cd = 0
and col_id = my_col_id(i);
END LOOP;
end;
/
exit


can any body help to get this done
Thanks in advance
Pinky
# 2  
Old 10-07-2007
The simplest way would be to build "cvs_users_defaults.sql" on the fly before
you execute it.

For example: the file "head" would contain

Code:
WHENEVER SQLERROR EXIT SQL.SQLCODE;
set serveroutput on;
set verify off;
set feedback off;

declare
userId s_user_cust_acct.user_id%TYPE := '&1';
TYPE t_col_id_tab IS TABLE OF number;
my_col_id t_col_id_tab;
TYPE t_col_seq_num_tab IS TABLE OF number;
my_col_seq_num t_col_seq_num_tab;
TYPE t_all_col_id_tab IS TABLE OF number;
all_col_id t_all_col_id_tab;
tcount number;
begin
select count(*)
into tcount
from s_column_atrb
where user_id = UPPER(userId)
and screen_typ_cd = 0;
if (tcount > 0) THEN
dbms_output.put_line('updating s_column_atrb' || userId);

The file "tail" would contain:

Code:
my_col_seq_num := t_col_seq_num_tab(4,12,2,6,7,8,10,1,9,3,5,11); 

For i in 1..12 LOOP
dbms_output.put_line('my_col_id(' || i || ') is ' || my_col_id(i));
update s_column_atrb
set show_hide_ind = 'Y', column_seq_num = my_col_seq_num(i)
where user_id = UPPER(userId)
and screen_typ_cd = 0
and col_id = my_col_id(i);
END LOOP;
end;
/
exit

and the file "orders" would contain the "modifiable code"

Code:
-- Orders
all_col_id := t_all_col_id_tab(1069,1071,1072,1073,1074,1075,1076,1077,1078,1080,1081,1082,1083,1084,1085,1086,108 7,1088,1089,1090,1091,1101,1102,1103,1104,1114,1115,1116,1117,1118,1119,1120,1122,1123,1124,1125,112 6,1127,1128,1129,1130,1131,1132,1133,1134,1135,1136,1137,1138,1139,1140,1141,1142,1143,1144,1145,114 6,1147,1148,1149,1150,1151,1152,1154,1169,1178,1182,1183,1195);
my_col_id := t_col_id_tab(1084,1143,1074,1080,1091,1090,1101,1069,1104,1087,1089,1081);

You would then modify your shell script to build the sql script as follows:

cat head orders tail > cvs_users_defaults.sql

before invoking cvs_users_defaults.sql
# 3  
Old 10-07-2007
I appreciate your help but I totally got confused and posted a new thread related to this
Thanks for answering
# 4  
Old 10-08-2007
hey Guys

I wanted to ask my question in detail what I wanted to achieve is

I have txt file
which looks like this

x.txt
5
8
10
15
17
19

I have abc.ksh and xyz.sql scripts

I want to read x.txt file into an array in abc.ksh (shell script) and pass that array to xyz.sql (sql script) where I wanted to read the values of that array and use those values in logic in xyz.sql (sql script).


Thanks
Apprciate any help
I am desperate and needed help
Pinky
# 5  
Old 10-08-2007
Hey fpmurphy

if I want to build my .sql file on the fly do I have to save the head and tail and orders as head.sql , tail.sql , and orders.sql
can u plz elaborate

I am just wondering whether there is a way that we can send arrays as parameters from shell script to sql script

Thanks a lot for all ur help
Pinky
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #1002
Difficulty: Medium
During her lifetime, Grace Hopper was awarded over 100 honorary degrees from universities across the world for her work in computer science.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Read in txt file and run a different command for each line

hi, i'm trying to write a tcsh script that reads in a text file (one column) and the runs a different command for each line of text. i've found lots of example commands for bash, but not for tcsh. can anyone give me a hint? thanks, jill (8 Replies)
Discussion started by: giuinha
8 Replies

2. Shell Programming and Scripting

Needed shell script to read txt file and do some modification

Hi ...programmers... I need a shell script to perform some specific task.. my txt file looks like this netcdf new { dimensions: XAX1_11 = 11 ; variables: double XAX1_11(XAX1_11) ; XAX1_11:point_spacing = "even" ; XAX1_11:axis = "X" ; float DEPTH(XAX1_11) ;... (19 Replies)
Discussion started by: Akshay Hegde
19 Replies

3. Shell Programming and Scripting

Want to read data from a file name.txt and search it in another file and then matching...

Hi Frnds... I have an input file name.txt and another file named as source.. name.txt is having only one column and source is having around 25 columns...i need to read from name.txt line by line and search it in source file and then save the result in results file.. I have a rough idea about the... (15 Replies)
Discussion started by: ektubbe
15 Replies

4. Shell Programming and Scripting

How to read userid and password information from txt file

Hi Experts, I am writing a shell script (for displaying disk space details) which is logging to 15 different servers using following command. ssh userid@servername It is prompting me for password for all 15 servers when I manually run it. However , soon I would like to schedule this script... (4 Replies)
Discussion started by: ajaypatil_am
4 Replies

5. Shell Programming and Scripting

Read .txt file and dropping lines starting with #

Hi All, I have a .txt file with some contents as below: Hi How are you? # Fine and you? I want a script file which reads the .txt file and output the lines which does not start with #. Hi How are you? Help is highly appreciated. Please use code tags when posting data and... (5 Replies)
Discussion started by: bghosh
5 Replies

6. Shell Programming and Scripting

How to read from txt file and write it to xls?

Hello All, I just want help in coding a simple shell script since i am a newbie for UNIX and i started learning unix and shell scripting basics recently. I am having a data like this in .txt file. Product Name : XYZ Price : 678.1 Best Buy Price : 600 Product Name : ABC Price : 465... (3 Replies)
Discussion started by: vasanth_123
3 Replies

7. Shell Programming and Scripting

i need to read the last line in a txt file

i'm a beginner in shell and i have a txt file that is updating every second or msec so i need a program to read the last line of this txt file is this possible to do? (5 Replies)
Discussion started by: _-_shadow_-_
5 Replies

8. UNIX for Dummies Questions & Answers

can't read a .txt file

Hello, I have a set of .txt files I cannot read. This is a part of what I see. Is there a way to view these files? _MO<P.6D@K;WU<B$X-;)SIV/ROO!UL+1P=VTT-?,SLC`MI/6QMS#UYGGT\+)C=#\UIO`TL/0]=#/T) it's about 3 pages. Thanks for your help. Joe (3 Replies)
Discussion started by: rcracerjoe
3 Replies

9. Shell Programming and Scripting

sed to read x.txt and grep from y.txt

How would I write a command(s) to read from a file (list) that looks like this: 29847374384 and grep from a second file (list) that looks like this: 29847374384, jkdfkjdf,3833,ddd:confused: (1 Reply)
Discussion started by: smellylizzard
1 Replies

10. UNIX for Dummies Questions & Answers

How to read last line of a txt file?

I need to read the last file for a particular day, such as, "Jun 13" because the CSV file is cumulative for the entire day, so I don't want all the previous files, I just want the last file, for that day. I ran an 'ls -al | grep "June 13" > myLs.txt' (simplified) to list all files from that day.... (2 Replies)
Discussion started by: yongho
2 Replies

Featured Tech Videos