I am new in shell scripting. I want to read data from csv file and insert into mysql table. And print the number of record inserted into table

Question

I have csv file as attached here.

I have created one table using query

CREATE TABLE testdb.employee (
  emp_id INT(11) auto_increment NOT NULL,
  full_name varchar(100) NOT NULL,
  phone_no varchar(100) NOT NULL,
  email varchar(200) NOT NULL,
  hire_date DATE NOT NULL,
  salary DOUBLE NOT NULL,
  designation varchar(100) NOT NULL,
  PRIMARY KEY (emp_id)
);

I want to insert data of csv to that mysql table. Can someone help me to write Unix shell script?

Answer 1

Step 1: Read the header

You will need to read the very first row and convert it into comma-separated values. If your csv is comma-separated already, then you just need to read the first line:

header=$(head -n 1 filename)

If it's separated with a different character, then you can explode it like

IFS=';' read -ra FIELDS <<< "$header"
for i in "${FIELDS[@]}"; do
  # process "$i"
done

where the separator is ;. If you have another separator, change accordingly. Now, if you had to explode it, then you can implode it like

IFS=, ; echo "${FIELDS[*]}"

and you can use the above as a function.

Step 2: Read each row

You will need to read all lines starting from the second in a loop until you reach the end of file (see Step 1 and https://www.shell-tips.com/bash/how-to-parse-csv-file/#gsc.tab=0)

Step 3: Parse your rows

You will need to make sure that you properly evaluate all cells, so if they are empty, you replace their value either with null or '', depending on which fits your need better. You also need to escape all apostrophe to avoid code breaks and SQL injections and wrap apostrophes around all textual data. This is not as simple as it sounds.

Step 4: Build your query

Your query will need to look like

insert into yourtable(column1, column2, column3) values
('a', 'b', 'c'),
('d', 'e', 'f');

Your header should already look good, but you will need to prepend insert into yourtable and wrap () around your header. Also, you will need to separate your rows with , and wrap () around all your rows, ending the query with ;.

Step 5: Execute

yourpassword | mysql -h yourhost -p -e"${yourquery}"

However, having a password in your script makes your script a very delicate file and you need to protect it by all means necessary.