Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Loading text file into table

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Loading text file into table
# 8  
Old 02-21-2014
Quote:
Originally Posted by stew

I am using below code to load the text file into table

Code:
#!/bin/ksh
filename=userreport.txt
while IFS=, read -r filename
do
INSERT INTO <tablename>
done

Quote:
Originally Posted by stew
But I am using awk to insert into table

Code:
#!/bin/ksh
filename=/export/home/er4rw/script/userreport.txt
#while IFS=, |read -r "$filename"
#do
#echo "$filename"
#done
awk '/,n,/{print "insert into lastlogon values("$1","$3","$4","$5","$6")"}' user
report.txt

You show one part of your code first, then some other, completely different code. Will you - once someone managed to debug your awk script - tell us that in fact you use something entirely different (which won't work either)?

Please show your COMPLETE AND UNALTERED code, until this i am out of here! Life is too short to waste it on correcting unused code.

bakunin
# 9  
Old 02-23-2014
I am using below script to insert text file data into table

Code:
awk '/ n /{print "insert into lastlogon values("$1", "$3", "$4", "$5", "$6")"}'
userreport.txt

when I run the script I am getting below lines.

Code:
+ awk / n /{print "insert into lastlogon values("$1", "$3", "$4", "$5", "$6")"} userreport.txt
insert into lastlogon values(we34w, Fri, Jan, 3, 2014)
insert into lastlogon values(yyy43, Mon, Aug, 19,, 2013)
insert into lastlogon values(34der, Thu, Jul, 12,, 2012)
insert into lastlogon values(45erf, Mon, Apr, 4,, 2011)
insert into lastlogon values(23erf, Fri, Feb, 7, 2014)
insert into lastlogon values(23wqe, Tue, Aug, 21,, 2012)
insert into lastlogon values(34dw3, Tue, Nov, 12, 2013)
insert into lastlogon values(34ewe, Tue, Jan, 1,, 2013)
insert into lastlogon values(23wwq, Thu, Feb, 20, 2014)

but when I view the table the data were NOT inserted, the table is empty
# 10  
Old 02-23-2014
Load data into table by awk
Hi

I am using below awk command to load text file data into table

Code:
awk '/ n /{print "insert into lastlogon values("$1", "$3", "$4", "$5", "$6")"}
' userreport.txt

but when I run its showing below O/P

Code:
insert into lastlogon values(3er45, Mon, Feb, 17, 2014)
insert into lastlogon values(34fe, Thu, Feb, 20, 2014)
insert into lastlogon values(ve3wThu, Feb, 20, 2014)
insert into lastlogon values(v34rfn, Sep, 23, 2013)
insert into lastlogon values(gwed,ue, Feb, 18, 2014)
insert into lastlogon values(swed2n, Jul, 30, 2007)
insert into lastlogon values(wesdc, Aug, 25, 2006)
insert into lastlogon values(wedsa, Feb, 14, 2014)

but the data is not loadin into table due to single quotes. The actual data should load with quotes as shown below.

Code:
insert into lastlogonvalues('ap238d', 'Fri', 'Feb', 14, '2014')

how I could load data with quotes with awk.



Moderator's Comments:
Mod Comment From here on down was merged from a similar thread by the same poster
Last edited by Scrutinizer; 02-24-2014 at 02:24 AM..
# 11  
Old 02-23-2014
Try:
Code:
awk '/ n /{q="\047";print "insert into lastlogon values("q$1q", "q$3q", "q$4q", "q$5q", "q$6q")"} ' userreport.txt

# 12  
Old 02-23-2014
Try:
Code:
awk '/ n /{print "insert into lastlogon values(" q $1 q ", " q $3 q ", "q $4 q", "q $5 q", " q $6 q ")"}' q=\' userreport.txt

or
Code:
awk '/ n /{print "insert into lastlogon values(" q$1q, q$3q, q$4q, q$5q, q$6q ")"}' q=\' OFS=", " userreport.txt

or
Code:
awk '/ n /{print "insert into lastlogon values(" q $1, $3, $4, $5, $6 q ")"}' q=\' OFS="', '" userreport.txt

Last edited by Scrutinizer; 02-23-2014 at 02:56 PM..
This User Gave Thanks to Scrutinizer For This Post:
stew
# 13  
Old 02-23-2014
Hi,

I tried

Code:
awk '/ n /{print "insert into lastlogon values(" q$1q, q$3q, q$4q, $5, q$6q ")"}' q=\' OFS=", " userreport.txt

I am getting below O/P while execute, but data were not inserted.

Code:
insert into lastlogon values('swr34', 'Thu', 'Oct', 25, '2012')
insert into lastlogon values('rerw3', 'Tue', 'Dec', 3, '2013')
insert into lastlogon values('5qsd1', 'Thu', 'Nov', 21, '2013')
insert into lastlogon values('r86r5', 'Thu', 'Oct', 11, '2007')
insert into lastlogon values('7gc51', 'Tue', 'Feb', 4, '2014')
insert into lastlogon values('jp64d', 'Sun', 'Jun', 24, '2012')
insert into lastlogon values('534df', 'Mon', 'Feb', 17, '2014')
insert into lastlogon values('64fd4', 'Thu', 'Feb', 20, '2014')
insert into lastlogon values('64etv', 'Thu', 'Feb', 20, '2014')
insert into lastlogon values('564dd', 'Mon', 'Sep', 23, '2013')
insert into lastlogon values('g64ft7', 'Tue', 'Feb', 18, '2014')
insert into lastlogon values('56gdt', 'Mon', 'Jul', 30, '2007')
insert into lastlogon values(5fyg7', 'Fri', 'Aug', 25, '2006')
insert into lastlogon values('ad496', 'Fri', 'Feb', 14, '2014')

when I copy like
Code:
insert into lastlogon values('ad496', 'Fri', 'Feb', 14, '2014')

individual line and run in sql DB the data is inserted.

so should I run read line one by and insert into table.

---------- Post updated at 09:01 PM ---------- Previous update was at 08:35 PM ----------

Thanks all for your help.

Actully I need end the insert statment with semi coloon like shown below

Code:
insert into lastlogon values(se3aa', 'Wed', 'Jun', 30, '2010');
insert into lastlogon values('34s34', 'Tue', 'Mar', 15, '2011');
insert into lastlogon values('34saw', 'Mon', 'Feb', 3, '2014');

how I can add
Code:
;

in insert statment

---------- Post updated at 09:09 PM ---------- Previous update was at 09:01 PM ----------

Hi
I am using below script to txt file into table

Code:
while read filename
do
awk '/ n /{print "insert into lastlogon values(" q$1q, q$3q, q$4q, $5, q$6q ");"
}' q=\' OFS=", " userreport.txt
done<${filename}

now the data is inserted with semi colon like below

Code:
insert into lastlogon values(se3aa', 'Wed', 'Jun', 30, '2010');
insert into lastlogon values('34s34', 'Tue', 'Mar', 15, '2011');
insert into lastlogon values('34saw', 'Mon', 'Feb', 3, '2014');

but still data were not inserted
# 14  
Old 02-23-2014
Did you try a commit? Are you getting any error?

--ahamed
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Make a table from a text file

Hi, I have a pipe separated text file. Can some someone tell me how to convert it to a table? Text File contents. |Activities|Status1|Status2|Status3| ||NA|$io_running2|$io_running3| |Replication Status|NA|$running2|$running3| ||NA|$master2|$master3|... (1 Reply)
Discussion started by: rocky88
1 Replies

2. Shell Programming and Scripting

Bash shell script that inserts a text data file into an HTML table

hi , i need to create a bash shell script that insert a text data file into an html made table, this table output has to mailed.I am new to shell scripting and have a very minimum idea of shell scripting. please help. (9 Replies)
Discussion started by: intern123
9 Replies

3. Shell Programming and Scripting

Help in script - Getting table name from a text file

hhhhhhhhhh (5 Replies)
Discussion started by: sams
5 Replies

4. Web Development

INSERT data to a Database Table from a text file

If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (0 Replies)
Discussion started by: sitex
0 Replies

5. Shell Programming and Scripting

Is it possible to convert text file to html table using perl

Hi, I have a text file say file1 having data like ABC c:/hm/new1 Dir DEF d:/ner/d sd ...... So i want to make a table from this text file, is it possible to do it using perl. Thanks in advance Sarbjit (1 Reply)
Discussion started by: sarbjit
1 Replies

6. Shell Programming and Scripting

loading the data to the partitioned table using procedure

Hi one help, I need one procedure to load data from flat file to table. Table name as input parameter for the procedure. can anyone help me Thanks, Raj, (1 Reply)
Discussion started by: easterraj
1 Replies

7. Shell Programming and Scripting

how can I bcp out a table into a text file including the header row in the text file

Hi All, I need to BCP out a table into a text file along with the table headers. Normal BCP out command only bulk copies the data, and not the headers. I am using the following command: bcp database1..table1 out file1.dat -c -t\| -b1000 -A8192 -Uuser -Ppassword -efile.dat.err Regards,... (0 Replies)
Discussion started by: shilpa_acc
0 Replies

8. Shell Programming and Scripting

shellscript.query Oracle table..populate in a text file

Hi Guys, I'm new to this forum as well as to UNIX shell scripting. I'm looking for a shellscript to query an Oracle database table and populate the result set of the query in a text file. Could you someone help me out with a sample code? Thanks, Bhagat (7 Replies)
Discussion started by: bhagat.singh-j
7 Replies

9. Shell Programming and Scripting

load a data from text file into a oracle table

Hi all, I have a data like, 0,R001,2,D this wants to be loaded into a oracle database table. Pl let me know how this has to be done. Thanks in advance (2 Replies)
Discussion started by: raji35
2 Replies

10. Shell Programming and Scripting

shell script to read data from text file and to load it into a table in TOAD

Hi....can you guys help me out in this script?? Below is a portion text file and it contains these: GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL005675 000001... (1 Reply)
Discussion started by: pallavishetty
1 Replies
Login or Register to Ask a Question