Is there anyway to write a linux script that reads the csv file and create the sql file?

Question

I want to write a script that reads the csv file and create the sql file? So I have several data in file.csv files like:

Ball,Jamin,1414 Willow Rd.,Cupertino,CA,94024
Church,Joe,2500 Main St.,Los Altos,CA,94023
Foothill,Ann,12345 El Monte Rd.,Los Altos,CA,94022

And I want to write a code in Linux to get the csv file like above and create the file.sql code so I can use that sql file for insert purpose. For example, I want the code will be like this in .sql file

INSERT INTO students (lname,fname,address,city,state,zip) VALUES
('Ball', 'Jamin',  '1414 Willow Rd.',  'Cupertino',  'CA',  '94024');
INSERT INTO students (lname,fname,address,city,state,zip) VALUES
('Church', 'Joe',  '2500 Main St.', 'Los Altos',  'CA',  '94023');
INSERT INTO students (lname,fname,address,city,state,zip) VALUES
('Foothill', 'Ann', '12345 El Monte Rd.',  'Los Altos', 'CA',  '94022');

So for example, I say,

./generateSQL.sh file.csv

and it will generate and (create) and save the .sql file. Is it possible?

Here is what I have done so far:

awk -F',' '{ print "insert into " $1 " VALUES(" $2 ", " $3 ", " $4 ", " $5 ", "  $6 ", " $7 ");" }' input.csv > output.log

My Code is only works for first line and I actually want this happen till all lines generated to SQL and I want this as a Generate.sh file that I can do below in my Linux. Not sure how:

./generateSQL.sh file.csv

Yes, there is a way. Does your CSV really have these empty lines as shown here? Also, are there any cells containing a `,`? If so, how is the `,` quoted? — Socowi, Mar 12 '19 at 22:08
Oh sorry, It does not have the empty lines. I can make sure all csv files are without empty lines and spaces — , Mar 12 '19 at 22:09
A shell script is not a robust solution for this. Have you considered checking whether your database allows importing CSV files by itself? Here's an [example for MySQL](https://stackoverflow.com/questions/6605765/importing-a-csv-into-mysql-via-command-line/6605783) — that other guy, Mar 12 '19 at 22:11
This maybe helps :https://www.unix.com/shell-programming-and-scripting/160360-read-csv-file-generate-sql-output.html — Kristina, Mar 12 '19 at 22:12
@thatotherguy We are doing that too, But I want to modify this insert in future (after I find the solution for this part) — , Mar 12 '19 at 22:14
@Socowi Thanks for fixing the question and none of the cells also have any `,` — , Mar 12 '19 at 22:54
**Beware** — CSV format is much more [complicated](https://tools.ietf.org/html/rfc4180#section-2) than you think it is. If you have a well-defined subset of CSV files you'll be working on, that's one thing; if you have to accept any arbitrary CSV file that's a much bigger task. — Stephen P, Mar 12 '19 at 22:58
@StephenP I Undestand and Thanks for letting me know the problem. I will make sure the file will be always exact same format as above — , Mar 12 '19 at 23:00
To answer your question, yes there is a way. You should state the database. Some databases can parse CSV files. For example, [How to import CSV file to MySQL table](https://stackoverflow.com/q/3635166/608639). — jww, Mar 12 '19 at 23:03
@jww Yes, I am using that too. But the purpose of this is something different. I like to modify the insert and call some SPs after I find the solution, So this is not only a insert statement. — , Mar 12 '19 at 23:07

KamilCuk · Answer 1 · 2019-03-12T23:26:26.440

Bash loop

while IFS=',' read -r lname fname address city state zip; do
    printf "INSERT INTO students (lname,fname,address,city,state,zip) VALUES\n"
    printf "('%s', '%s', '%s', '%s', '%s', %s');\n" "$lname" "$fname" "$address" "$city" "$state" "$zip"
done <<EOF
Ball,Jamin,1414 Willow Rd.,Cupertino,CA,94024
Church,Joe,2500 Main St.,Los Altos,CA,94023
Foothill,Ann,12345 El Monte Rd.,Los Altos,CA,94022
EOF

Slow, but flexible.

Awk script

awk -F',' '{ 
    print "INSERT INTO students (lname,fname,address,city,state,zip) VALUES"
    print "('\''" $1 "'\'', '\''" $2 "'\'', '\''" $3 "'\'', '\''" $4 "'\'', '\''"  $5 "'\'', '\''" $6 "'\'');" }' <<EOF
Ball,Jamin,1414 Willow Rd.,Cupertino,CA,94024
Church,Joe,2500 Main St.,Los Altos,CA,94023
Foothill,Ann,12345 El Monte Rd.,Los Altos,CA,94022
EOF

Faster, but external tool with it's own syntax. Also quoting here is pain, but it can be reduced by having the script as an awk script with awk shebang:

cat <<'EOF' >script.awk
#!/bin/awk -f
BEGIN {
    FS=","
}
{
    print "INSERT INTO students (lname,fname,address,city,state,zip) VALUES"
    print "('" $1 "', '" $2 "', '" $3 "', '" $4 "', '"  $5 "', '" $6 "');"
}
EOF

chmod +x script.awk

./script.awk <<EOF
Ball,Jamin,1414 Willow Rd.,Cupertino,CA,94024
Church,Joe,2500 Main St.,Los Altos,CA,94023
Foothill,Ann,12345 El Monte Rd.,Los Altos,CA,94022
EOF

Is there anyway to write a linux script that reads the csv file and create the sql file?

1 Answers1