Loading a CSV File in MySQL

       Loading a CSV/Text File in MySQL
Hi,
In this blog, I want to say about how to load CSV/Text file in MySQL.
Purpose:
  • The purpose of Load Data Infile is loading CSV/Text file to MySQL or any delimited data files to MySQL database.
  • For large data which we cannot insert data by using normal insert command can be done by using Load Statement.
  • The external files (csv/flat/text files) cannot be queried and we cannot perform any operations that we are performing on the tables in database.
How to Load:
  • Generally, after creating a table we need to populate data. This can be done by using INSERT statement or LOAD statement.
  • We need to convert the data of flat/csv/text files into database tables which can be done by using import or by using load command.
  • The data can be loaded by different methods and in this blog, I will explain about how data can be loaded directly from csv to MySQL database using single command.
Types of loading the data:
1. We can load a csv/text file by import option in MySQL workbench.
2.In toad for MySQL we load a csv file by import option.
3.Using Command line
4.Using Command Prompt
5. We can load it by using load statement.
  1. We can load a csv/text file by import option in MySQL workbench.
Give the path where the file is saved and click on next.
The preview of the table will be shown as below.
Execute the following command.
select * from load1;
Data loaded successfully.
2. In toad for MySQL we load a csv file by import option.
Open the toad for MySQL and click on tools->import->import wizard.
Click on next-> click on add file->file preview will be appeared
Click on next-> columns are displayed
(In that we can add or remove the columns) and click on next.
select the database and target table
and click on finish. The data is successfully loaded.
3. Using Command line Interface:
Open the MySQL command line and enter the password
select the database in which you have created the table.
Type load command and click on enter. The data is loaded successfully.
Data loaded successfully.
4.Using Command prompt:
We can load the file using command prompt.
Open the command prompt and set the path for MySQL
Next execute the import command as follows:
mysqlimport -u username -p databasename path
Eg: mysqlimport -u root -p sneha "E:\load1.csv" --fields-terminated-by \, --ignore-lines-1
At a time, we can import more than one csv file but before importing we need to create the table structure.
Eg: mysqlimport -u root -p sneha “E:\load1.csv” “E:\load111.csv” –fields-terminated-by \^ --ignore-lines=1
5. We can load it by using load statement in MySQL Workbench.
Load statement:
load data low_priority/concurrent (local) in file ‘path’
replace/ignore
into table tablename
partition (partition_name,partition_name,.)
character set charset_name
fields/columns terminated by ‘string’
(optional) enclosed by ‘char’
escaped by ‘char’
lines starting by 'string ' terminated by 'string'
Ignore number (lines/rows)
(col1,col2,col3) set col1=value
Load statement in detail:
  • Load data: Reads rows from a text file into a table
  • Low_priority: Makes the execution wait until no other client is reading from the table.
  • Concurrent: If we want concurrent transactions to be able to read from the table during the import, then we can use this.
  • Local: The local option causes MySQL import to read data files from the client (LOAD DATA LOCAL INFILE reads a file that is on the machine where you are running the MySQL local server)
  • Path: Specify backslashes in path names as forward slashes or doubled backslashes. You may give path as: C:\\path\\file.csv or C:/path/file.csv
  • Replace: If we want to replace existing data by data being loaded from file, we will add REPLACE keyword before into table. This will eliminate the duplicates in new data.
  • Ignore: If we want input rows that duplicate an existing row on a unique key value to be skipped, we will use IGNORE keyword before into table. (ignoring rows with duplicate unique keys in the import file)
  • Tablename: The table name which we create.
  • Partition: Dividing the data
  • Character_set: used to specify character set of the file.
  • Fields terminated by: specifies the character used to separate the fields, string which is separated for every column.
Eg: file
100,a,5000
200,b,6000
fields terminated by ‘,’ : denotes a comma delimited file
fields terminated by '\t': denotes tab delimited
terminated by may contain more than one character
  • Enclosed: Character which the values are enclosed with.
Eg: “100”,” a”
Enclosed by ‘”’
  • Escaped: character which is not required in the column can be escaped.
Eg: escaped by ‘ \\’
For example with FIELDS ESCAPED BY '^' a line consisting of First,Second^,Third,Fourth would be parsed as three fields: "First", "Second, Third", and "Fourth".
FIELDS ESCAPED BY value is a backslash : FIELDS ESCAPED BY '\\'.
  • lines starting by: Used to skip over the prefix if all the lines to be read in have a common prefix.
Eg: lines starting by '/*/'
  • Set: Using SET you can assign values to those columns which were not available in csv and are not-null.
  • Ignore: This is used to skip the lines or columns when not required.
Eg: Sample data
col1,col2,col3
"101,5","a","k1"
"102","b^s","L3"
"103,1","c","M5"
"104","d","S7"

load data low_priority local infile ' '
ignore into table 'load1'
fields terminated by ','
enclosed by '"'
escaped by '^'
lines started by '\t' terminated by '\r\n'
ignore lines 1
(col1,col2,col3,col4) set col4='null'


Below are the steps for loading file in MySQL Workbench

Step 1:
  • Click on local instance MySQL
Step 2:
  • Homepage of MySQL Workbench
Step 3:
  • Create the table structure
CREATE TABLE `load1` (

`col1` varchar(100) DEFAULT NULL,

`col2` varchar(100) DEFAULT NULL,
`col3` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Step 4:
  • Execute the following load statement in MySQL Workbench:
load data low_priority local infile 'E:\\load1.csv'

into table `load1`

fields ENCLOSED BY '"'
terminated by ','
escaped by '^'
lines terminated by '\r\n'
ignore 1 lines 
(col1,col2,col3);

Step 5:
  • Resultset of the table:
Select * from load1;
Data loaded successfully.
Some of the common issues while loading csv file:
  • No of columns in the csv file should match with no of columns in table structure created.
  • Out of range value error: Out of range value for column ‘column name’ at row 1 occurs when size of column data in csv file is greater than size of column in table created.
  • When there is an illegal character in the CSV file that you are trying to import. To rectify this, the most important thing to remember is that your CSV file must be UTF-8 encoded.
  • Data truncated error: This occurs when source column datatype in the file differs from target column.
  • Incorrect integer value error: This occurs when column data contains null values.
Example:
  • Create a table structure as :
CREATE TABLE `code_mstr` (
`code_fldname` varchar(100) DEFAULT NULL,
`code_value` varchar(100) DEFAULT NULL,
`code_cmmt` varchar(100) DEFAULT NULL,
`code_user1` varchar(100) DEFAULT NULL,
`code_user2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • Execute the statement:
LOAD DATA LOCAL INFILE 'C:\\Users\\snehap\\Desktop\\applications\\code_mst_data.csv'
INTO TABLE `code_mstr`
FIELDS TERMINATED BY ' '
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
  • I have got the following issues.
  • To resolve this I have saved the file as text with .txt extension and changed the character set as UTF-8 and loaded the file again.