Read a CSV file and generate SQL output

Shell Programming and Scripting
1

Friends,

This is what I need:

I will pass a CSV file as an input, and I want my shell to be reading that CSV file, and based on the parameters it should generate SQLs and write those SQL in a different file in the same location.

I'm new to Shell scripting. I'm currently working on a different technology. If somebody could help me, i am grateful.

Thanks,
Math

2

What do you mean by sql? insert statements? please post sample data file (csv) and required sql statements from them.

3

Hi Ram
You can try this

if ur csv file is like this

$ cat input.csv
razor,cts
indu,adobe
akash,daksh

u can use this code

 awk -F',' '{ print "Select * from " $1 " where user=" $2 ";" }' input.csv > output.log

$ cat output.log
Select * from razor where user=cts;
Select * from indu where user=adobe;
Select * from akash where user=daks;

hope it will help

1 Like
4

Thanks a lot, Atul. I think your code should help me. I will give out a try and let you know shortly.

Here is how the CSV should look like.

1000,Y,$2 ,10000,Students Plan
1001,M,$2 ,10001,Professional Plan
1002,M,$3 ,10002,Talk 199
1003,Y,$5 ,10003,Simple 30
1004,M,$3 ,10004,Flat 300

And the sqls should be inserts into tables. I think I will leverage you for many more things :slight_smile:

Regards,
R..

---------- Post updated at 09:22 AM ---------- Previous update was at 09:21 AM ----------

Hi Anchal, I have replied to Atul's comment on the csv and the sqls. they are insert statements...

any of your advice will do a lot to me.

5

Another way:

while IFS="," read c1 c2 c3 c4 c5
do
 echo "insert into table1 values ($c1,$c2,$c3,$c4,'$c5');"
done < csv_file

If you want to insert into specific columns, change echo statement accordingly. also take care of datatypes (quotes for varchars) accordingly.

O/P of above:

insert into table1 values (1000,Y,$2 ,10000,'Students Plan');
insert into table1 values (1001,M,$2 ,10001,'Professional Plan');
insert into table1 values (1002,M,$3 ,10002,'Talk 199');
insert into table1 values (1003,Y,$5 ,10003,'Simple 30');
insert into table1 values (1004,M,$3 ,10004,'Flat 300');
1 Like
6

Hi Anchal, Thanks it worked for me. But I just have a question, how to let my code go directly to the II row I column right after it does with I row I column?

for example: right after 1000, i want it to go to 1001. but i might come back again to I row V column in the later SQLs... is it possible?

please advice...

7

SQL loader is efficient one to load data from file.

8

hello!, but there is a requirement to do it with shell scripts, so that we have our own environment set up to execute it in appworx... it will be scheduled everymonth and this script will be executed automatically.

Also, what Anchal has almost solved the purpose. my only question is instead of going it by column alone, i would like to make it go by row also...

9

Would you please explain with an example and expected output (queries).

10

For this input:

1000,Y,$2 ,10000,Students Plan
1001,M,$2 ,10001,Professional Plan
1002,M,$3 ,10002,Talk 199
1003,Y,$5 ,10003,Simple 30
1004,M,$3 ,10004,Flat 300

The insert is:

insert into elements values (1000, 'Y',10000);
insert into descriptions values (10000,'Students Plan');  

insert into elements values (1001, 'M',10001);
insert into descriptions values (10001,'Professional Plan');
.......

but i would like it coming this way:

insert into elements values (1000, 'Y',10000);
insert into elements values (1001, 'M',10001);
.....
insert into descriptions values (10000,'Students Plan');  
insert into descriptions values (10001,'Professional Plan');
......

i mean, all inserts of 1 table should be accumulated together, instead of having it in mixture.

Thanks in advance....

11

Piping output to sort should work for you..

.... | sort

i.e.

while IFS="," read c1 c2 c3 c4 c5
do
 echo "insert into table1 values ($c1,$c2,$c3,$c4,'$c5');"
done < csv_file | sort
12

Friends, please help me with this: i have the following csv file:

Cat, 4
Cat, 4
Cat, 3
Rat, 4
Rat, 5
Cow,6

This csv file is parsed and found that it contains Cat, Rat, Cow in it. now i have to check if it contains 3 different values, if so i have to perform an action.

if it contains, just Cat and Rat, i have to perform a different action;

or if it contains just Rat and Cow, i have to perform a different action.

but please note, i should check only the distinct values of them and store in a variable.

13 
 
awk -F"," -v v1="Cat" -v v2="Rat" -v v3="Cow" '{ a[$1]++ }
   END {
   if ( a[v1] >= 1 && a[v2] >= 1 && a[v3] >= 1 ) print "all";
   if ( a[v1] >= 1 && a[v2] >= 1 && a[v3] < 1 ) print "1 and 2";
   if ( a[v1] < 1  && a[v2] >= 1 && a[v3] >= 1 ) print "2 and 3";
   }' rem
14

Thank you, but please to explain...

15

Best place as for as I know to learn about awk

www.grymoire.com/Unix/Awk.html

16

Sorry this is the error i find continuously:

awk -F, -v v1 = "RC"  '{ a[$1]++ } END { for (b in a) { print b } }' import_data.csv
awk: syntax error near line 1
awk: bailing out near line 1
17

As always.... if on Solaris, use /usr/bin/nawk or /usr/xpg4/bin/awk

18

it fails again :frowning:

19

Can you copy & paste here as it is

 
head -10 import_data.csv
 
and your awk statement!
 
Also your OS version using uname -a
20

the above statement doesn't really help.....
make sure you execute exactly what was given to you - no spaces surrounding the '=':

awk -F, -v v1="RC"  '{ a[$1]++ } END { for (b in a) { print b } }' import_data.csv