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.

• Truncate the table `code_mstr`;

• Load the file now

LOAD DATA LOCAL INFILE 'C:\\Users\\snehap\\Desktop\\applications\\code_mst_data.txt'

INTO TABLE `code_mstr`

FIELDS TERMINATED BY ' '

ENCLOSED BY '"'

LINES TERMINATED BY '\n';

• select * from `code_mstr`;

• Now the data is successfully loaded.

Note:

• Before loading the data, we need to create the table structure.
• A CSV file with data that matches with the number of columns of the table and the type of data in each column.
• And we need to check the character set while creating.
• We can skip the column header using ignore.
• Can handle duplicate records.
• Use double backslashes [\\] in the folder path to separate folders rather than just one to escape the first backslash. 
  Eg: C:\\new folder\\abc.txt
  
  
• 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.
• We can skip the columns which we are not necessary.

Analysis:

1.Loading data in MySQL workbench using import option	2.Loading data by using Toad for MySQL	3.Loading data by using Command Line	4.Loading data by using Command Prompt	5.Loading data in MySQL workbench using load command
By using Import option we can directly load SQL file. Here we can import only for single table.	Here table structure is automatically created. If any error occurs then also the flow of loading won’t stop.	To import data via command line is efficient. Unless the user know the commands.	By using command prompt data can be fastly loaded. But the user should have knowledge on it.	Performance Optimization. We can select columns which required to load.

Conclusion:

The purpose of this documentation is providing different ways on how to load a CSV/Text file in MySQL in Windows. Based on the availability of tool we can use different methods for loading the data.

Thanks & Regards,

Sneha Panthulu,

MOURI Tech PVT LTD.

http://www.mouritech.com/