Turning Spreadsheets Into Database Tables
While developing database-driven applications or building a database with bogus information for testing purpose, the traditional interfaces for adding values to each table are often utterly complex or require too many mouse-clicks per entry. Wouldn’t it be nice to import a plain spreadsheet created in OpenOffice Calc or Excel straight into your database? This article will introduce a technique making it possible.
The popular .xls format is proprietary and therefore not easy to work with. On the other hand, it contains a lot of unnecessary meta data like font sizes, background colors and things like that. When we are importing data from a spreadsheet into a database table there is no need for such meta data, and the format does not allow us to access the data in an easy manner either. Therefor we will be using a traditional, somewhat deprecated, spreadsheet format ranging back from the ages of mainframe computing. The format is called Comma-separated values and it represents columns and rows in a spreadsheet using nothing but plain-text.
Example CSV:
"Name","Country" "Lasse","Denmark" "Niklas","Sweden"
Importing Through MySQL
There is an already existing functionality in MySQL that helps you import delimited data. It works like this:
LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (field1, filed2, field3);
Importing our example: (the first line containing labels should be deleted first)
LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE People FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (Name,Country);