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
# 1  
Old 02-21-2014
Loading text file into table
Hi,

I have text file with comma seprater shown below

Code:
lu8yh,n,Fri,Feb,7,2014,16:5
deer4
deer4,n,Tue,Aug,21,,2012,on
r43ed
r43ed,n,Tue,Nov,12,2013,12:
e43sd
e43sd,n,Tue,Jan,1,,2013,on,

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

But this code is not compling
# 2  
Old 02-21-2014
Which database?
# 3  
Old 02-21-2014
Ingres Databse
# 4  
Old 02-21-2014
Quote:
Originally Posted by stew
Hi,

I have text file with comma seprater shown below

Code:
lu8yh,n,Fri,Feb,7,2014,16:5
deer4
deer4,n,Tue,Aug,21,,2012,on
r43ed
r43ed,n,Tue,Nov,12,2013,12:
e43sd
e43sd,n,Tue,Jan,1,,2013,on,

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

But this code is not compling
There are a number of reasons why this will not work:

First, "INSERT INTO <tablename>" is not a valid statement, because there is nothing specified to insert into the table at all. If you haven't truncated your code before putting it here you have to correct that.

Second, i am not sure what your loop is supposed to do, but it definitely does soething else.

Code:
filename=userreport.txt
while IFS=, read -r filename
do
 ....
done

This loop has no input, because the read-command works differently from what you seem to suspect. If you want to read the file specified by variable "filename", do it like this:

Code:
filename=userreport.txt
while IFS=, read <variablename>
do
 ....
done < "$filename"

Each line in "$filename" will then be assigned to "<variablename>" every turn. You can try this demo code to see how it works:

Code:
filename=userreport.txt
while IFS=, read currentline ; do
     print - "line read: $currentline"
done < "$filename"

Next is: you specifiy "IFS", seemingly to split the input line into (comma-separated) pieces. But because you specify only one variable to "read" the whole line gets put into that variable anyway. The way you use it the IFS-specification is superfluous.

When you want to split the input line upon reading it you have to specify more than one variable to be assigned with the "read"-command. The first "field" (everything before the first IFS character) will be assigned the first variable, the second field (everything between the first and second IFS character) will be assigned the second variable and so on. If you specify less variables than there are fields all the remaining fields are getting crammed into the last variable specified. In your case that was everything.

Try the following demo code to see how it works:

Code:
cat - > /tmp/input.for.demo <<EOF
line1,1-field1,1-field2,1-field-3
line2,2-field1,2-field2,2-field-3,2-field4
line3,3-field1,3-field2,3-field-3,3-field4,3-field5
EOF

Code:
#!/usr/bin/ksh
filename=/tmp/input.for.demo
while IFS=, read -r linenr field1 field2 field3 field4 ; do
     print - "new line read --------------------"
     print - "   linenr is: $linenr"
     print - "   field1 is: $field1"
     print - "   field2 is: $field2"
     print - "   field3 is: $field3"
     print - "   field4 is: $field4"
done < "$filename"
exit 0

I hope this helps.

bakunin
This User Gave Thanks to bakunin For This Post:
stew
# 5  
Old 02-21-2014
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


I wan to insert below text file into table

Code:
r44g,n,Thu,Feb,20,2014,13:
vfdgfg
34ffc,n,Mon,Sep,23,2013,17:
4fyht
4fyht,n,Tue,Feb,18,2014,12:
4rt64
4rt64,n,Mon,Jul,30,,2007,on
45tg4
45tg4,n,Fri,Aug,25,,2006,on

I grep ",n," in awk and then insert into lastlogon table only field 1,3,4,5,6

Code:
34fd3,Fri,Aug,25,2006

But the script is inserting like below

Code:
insert into lastlogon values(34edf,n,Thu,Oct,11,,2007,on,,,,)
insert into lastlogon values(45fbt,n,Tue,Feb,4,2014,13:2,,,,)
insert into lastlogon values(j12fet,n,Sun,Jun,24,,2012,on,,,,)
insert into lastlogon values(34dwr,n,Mon,Feb,17,2014,15:,,,,)
insert into lastlogon values(98jgg,n,Thu,Feb,20,2014,15:,,,,)
insert into lastlogon values(78jgf,n,Thu,Feb,20,2014,13:,,,,)
insert into lastlogon values(45fgh,n,Mon,Sep,23,2013,17:,,,,)
insert into lastlogon values(65jjg,n,Tue,Feb,18,2014,12:,,,,)
insert into lastlogon values(90fdd,n,Mon,Jul,30,,2007,on,,,,)
insert into lastlogon values(67vfd,n,Fri,Aug,25,,2006,on,,,,)
insert into lastlogon values(45fgj,n,Fri,Feb,14,2014,11:,,,,)

the files 2,7 and all inserting I want to insert only column 1,3,4,5,6
Last edited by stew; 02-21-2014 at 08:07 AM..
# 6  
Old 02-21-2014
What is the field separator in the file you're giving awk to process?
What field separator have you told awk to use while processing its input?

Why do the last two entries in your data:
Code:
4rt64
4rt64,n,Mon,Jul,30,,2007,on
45tg4
45tg4,n,Fri,Aug,25,,2006,on

have the year in field 7 instead of field 6?
# 7  
Old 02-21-2014
As you didn't specify the separator "," for awk, the entire line will be $1, and this and the (empty) residual fields are concatenated together with the literal "," in between to be then printed out..
As Don Cragun pointed out, you should make very sure your input file adheres to one single defined structure (which your sample does not). You also could try to make your sample data consistent between posts.
And, you need to decide to use either a shell loop or an awk loop.
You also should consider to add a command running some sql code to connect to the DB unless you want to run your result script manually from within sql.
 
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