Import CSV into MySQL

Database need tons of data to become a ‘Database’. However, manually input is a time consuming and costly method. We need some skills.

How to find Data

How to find data is always a critical issue. There are some tips:

1. google key words, such as “weather data”
2. There are some open data website supported by government or organisations. Thus, it is a reliable source for data.
3. Collect by self, by using Web Spider
4. Buy it

General Data file

The most common data file I know are in three types:

1. CSV: a file, data splited by “,”, can be open by excel.
2. JSON: a kind of key-value format; it is very popular now. It is a semi-structured data.
3. XML: organised data by tags. Currently, it is replacing by JSON, which is more easy to be mulnipulated.

In this articles, I will focus on CSV.

Import CSV into MySQL

Build Table

Firstly, you have to make a table first. Generally, if it downloaded from some organised website, it provides information about each column. You can create it by help of it.

CREATE TABLE tableName 
(
col1 type
);

Import Data

The following code used to import data

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE tableName
CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

Mistkes I encountered

I have met several mistakes

No permission on access data.csv

Firstly, I put my file into my personal folder. Thus, mysql do not have the access right.

This is the reason I recommond to put it under /tmp/

You need also change the permission of the file.

Error Code: 1265. Data truncated for column ‘xxx’ at row 1

I do not quite understand this problem, but I found a code to solve this problem.

set sql_mode='no_auto_create_user,no_engine_substitution';

Data cleaning

Be careful of the “, “, I meet some data has it inside, which split data into two seperateed parts. I have to clean it manully.

Data Cleaning is an important approach in the database management. The data quality is critically related to the final result.