AndhySukma.com

Load MySQL Database from Spreadsheet using Shell Script

When we have a spreadsheet file with huge content which should be loaded into MySQL database, then what will we do?
For me, i prefer use shell script. So, how?

Well, before we do it, we need to understand the format of the spreadsheet first. The spreadsheet file consists of rows and columns, so what we need is to find how to identify each row and column. I don’t know how to identify rows and columns in shell script, therefore, in order to load the data correctly, I export the spreadsheet into plain text file by copy the entire content of the spreadsheet file and then paste it into plain text file.
If we copy a spreadsheet excel file and paste it into text file, each column in excel will be identified by tab and row will be identified by line in text file. From this, we can use symbol \t for tab and symbol \n for new line.
Before attempting to convert any spreadsheet into text file, it is best to review the data for consistency. For example, make sure that every row has the same number of columns and check that all dates are formatted in the same way.
After that, we can use shell script to load the plain text file into MySQL database using command LOAD DATA LOCAL INFILE.
The term of LOCAL is used because my MySQL server is remote server. That means my MySQL server is located in different server. See my previous post to know my server configuration.

Here is my simple script :

#! /bin/sh
# filename : updatedb.sh
# Load data from file into MySQL
mysql -u USER -pPASSWORD -D mydatabase -h 10.1.1.10 -e
"LOAD DATA LOCAL INFILE 'mydata' INTO TABLE mytable
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';"

By that script my file is successfully loaded into MySQL database.