How to remove the first row while inserting a csv into mysql using shell script

Question

I'm using the below query to insert data from csv into mysql in shell script.I neeed to remove the first line ie,the header names while inserting without mentioning their names

for _csv_file in ${_csv_files[@]}
do
export IFS=","
cat  $_csv_file | while read a b c d e f g h i j k l m n o p q r s t u v; do  mysql -u $_db_user -p$_db_password $_db << eof 
INSERT INTO \`csv_temp_table\`
 (\`ProductType\`,\`ProductTypea\`,\`ProductTypeb\`,
\`ProductTypec\`,\`ProductTyped\`,\`ProductTypee\`,
\`ProductTypef\`,\`ProductTypge\`,\`ProductTypeh\`,
\`ProductTypeh\`,\`ProductTypej\`,\`ProductTypke\`,
\`ProductTypel\`,\`ProductTypem\`,\`ProductTypen\`,
\`ProductTypeo\`,\`ProductTyppe\`,\`ProductTypeq\`,
\`ProductTyper\`,\`ProductTypes\`,\`ProductTypet\`,
\`ProductTypeu\`) VALUES   ('$a','$b','$c','$d','$e','$f','$g','$h','$i',
'$j','$k','$l','$m','$n','$o','$p','$q','$r','$s','$t','$u','$v',); 
eof
done 
done
exit;

Please help me accomplish that.Thank you.

On another note, quote your variables: $_csv_file -> "$_csv_file", ${_csv_files[@]} -> "${_csv_files[@]}" etc. — anishsane
– anishsane, Commented Jun 10, 2015 at 10:23

Olli · Accepted Answer · 2015-06-10 10:48:51Z

2

You can also use the LOAD DATA command which gives you quite interesting options to read different CSV styles into the database. So you need no special script for that purpose. It is definitely worth a look!

part of the man page for your request:

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

edited Jun 10, 2015 at 10:48

answered Jun 10, 2015 at 10:43

Olli

1,76610 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

anishsane Over a year ago

I would +1 this. A custom bash script may break in certain corner cases. These dedicated tools are usually tested for these corner cases.

Adrian Veidt · Accepted Answer · 2015-06-10 10:15:45Z

Here's what I had to do

for _csv_file in ${_csv_files[@]}
do
export IFS=","
tail -n +2 $_csv_file| while read a b c d e f g h i j k l m n o p q r s t u v; do  mysql -u $_db_user -p$_db_password $_db << eof 
INSERT INTO \`csv_temp_table\`
(\`ProductType\`,\`ProductTypea\`,\`ProductTypeb\`,
\`ProductTypec\`,\`ProductTyped\`,\`ProductTypee\`,
\`ProductTypef\`,\`ProductTypge\`,\`ProductTypeh\`,
\`ProductTypeh\`,\`ProductTypej\`,\`ProductTypke\`,
\`ProductTypel\`,\`ProductTypem\`,\`ProductTypen\`,
\`ProductTypeo\`,\`ProductTyppe\`,\`ProductTypeq\`,
\`ProductTyper\`,\`ProductTypes\`,\`ProductTypet\`,
\`ProductTypeu\`)
VALUES('$a','$b','$c','$d','$e','$f','$g','$h','$i','$j','$k','$l','$m','$n','$o','$p','$q','$r','$s','$t','$u','$v',);
eof
done 
done
exit;

Collectives™ on Stack Overflow

How to remove the first row while inserting a csv into mysql using shell script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related