I’ve been asked to compute some data and statistics from an Excel spreadsheet containing an huge phonebook<\/p>\n
The operations I need wasn’t very complicated, like to find and remove duplicate rows and so on, but I didn’t find any quick way to achieve those simple tasks using just Excel (or OpenOffice and LibreOffice).<\/p>\n
Since I’m good in SQL, I decided to move this data in a MySQL database, then I wondered what’s the simplest way to obtain this result.<\/p>\n
My excel data is on three simple columns:<\/p>\n
I need to export my 20,000 rows in a .csv file.<\/p>\n
From Excel, go in “Save As” then pick the option “Other Formats”, and from the combo box, choose Comma Delimited \u00a0CSV.<\/p>\n
Microsoft Excel \u00a0by default creates values separated by a Comma, and the single column is not enclosed by any special char.<\/p>\n
In OpenOffice, choose Save As and then CSV, using the default options the .csv file will have values separated by semicolon and enclosed by double quotes.<\/p>\n
It’s time to create the basic data structure in MySQL that will be able to host the data we exported from Excel. So the simple task is to generate a table with the same number of columns (and type) that will be associated to the Excel columns.<\/p>\n
create table phonebook (first_name varchar(100), last_name varchar(100), phone_number varchar(100))<\/pre>\nAnd now, the last step, importing the CSV in MySQL<\/p>\n
Import the CSV (generated from an XLS) into MySQL table<\/h2>\n
Mysql offers a useful command for the operation of importing the CSV in a table, the command is LOAD DATA LOCAL INFILE.<\/p>\n
And now the code in the case you exported the CSV from OpenOffice and the rows have the following structure:<\/p>\n
“Mario”,”Rossi”,”+390123456789″<\/p>\n
The code to load the data is:<\/p>\n
load data local infile ‘phonebook.csv’ into table phonebook fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘\\n’ (first_name, last_name, phone_number);<\/span><\/p>\n
If you exported using Microsoft Office the rows have the following structure:<\/p>\n
Mario;Rossi;+390123456789<\/p>\n
The code to load the data is:<\/p>\n
load data local infile 'phonebook.csv' into table phonebook fields terminated by ';' enclosed by '' lines terminated by '\\n' (first_name, last_name, phone_number);<\/pre>\n","protected":false},"excerpt":{"rendered":"Microsoft Excel doesn’t offer a simple way to achieve some results that are very simple to obtain using SQL in MySQL, in this example it’s explained how to migrate some data from Excel to MySQL Continue reading