MYSQL -Import Data from Delimited Data

Loading CSV or any delimited data files to MySQL Database. This is a very common task for the developer and the only thing that I think of is LOAD DATA INFILE.

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

Here, I will explain how to get the data loaded directly from CSV to MySQL database using commands.

For more information, feel free to refer https://dev.mysql.com/doc/refman/5.7/en/load-data.html

a) Copy the complete CSV file data including header

LOAD DATA LOCAL INFILE 'path/of/csv/file'
INTO TABLE <TABLE NAME>
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

Path: '/home/user/file_name.csv' or in windows 'C:/path/file_name.csv'
FIELDS TERMINATED BY ',': Divides the line data into Fields

Example: A table with three columns say A,B,C and CSV file is as follows
a,b,c
aa,bb,cc
aaa,bbb,ccc

By terminating by delimited ',' fields table would look like

A B C
a b c
aa bb cc
aaa bbb ccc

LINES TERMINATED BY '\n': Line Breaker

b) Copy the complete CSV file data excluding header


LOAD DATA LOCAL INFILE 'path/of/csv/file'
INTO TABLE <TABLE NAME>
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

IGNORE 1 LINES: Ignores the first line of the CSV File

c) If data sequence in the file and table are different.


 LOAD DATA LOCAL INFILE 'path/of/csv/file'
INTO TABLE <TABLE NAME> 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Column1,Column3,Column2);

 Assume data sequence and tables fields are not matching we can include rearranging the data by using(Column1, Column3, Column2)

d) Create a new field with computed values

LOAD DATA LOCAL INFILE 'path/of/csv/file'
INTO TABLE <TABLE NAME> 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Column1,@Column3,@Column2,@NewColumn)
SET NewColumn = (@Column2 / @Column3);

In the above example we have NewColumn value by computing the column value of Column2 and Column3

SET NewColumn = (@Column2 / @Column3);


e) Create a new field with Auto-increament Id


LOAD DATA LOCAL INFILE 'path/of/csv/file'
INTO TABLE <TABLE NAME> 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Column1,@Column3,@Column2,@NewColumn)
SET id = NULL;

To create a new autoincrement field on the table just  SET id = NULL; or SET id = '\N';


 Other issues.

how to 'load data infile' on Amazon RDS?

You need to use LOAD DATA LOCAL INFILE as the file is not on the MySQL server, but is on the machine you are running the command from.
--local-infile=1

Comments

Post a Comment

Popular posts from this blog

Using Redis in PHP for handling background Jobs

Using Redis in PHP for handling background Jobs Introduction On Redis Redis is an open source, BSD licensed advanced key-value store. It is often referred to as a data structure server since the keys can contain strings, hashes, lists, sets and sorted sets. Redis is written in C. Refer to the below link which would help us to understand how to install Redis. Also, it gives the basic understanding of how to use Redis. how to install and use Redis Before you start using Redis in your PHP programs, you need to make sure that you have Redis PHP driver and PHP set up on the machine. You can check PHP tutorial for PHP installation on your machine. Installation Now, let us check how to set up Redis PHP driver. You need to download the phpredis from github repository  phpredis . Once you’ve downloaded it, extract the files to phpredis directory. On Ubuntu, install the following extension. cd phpredis sudo phpize sudo ./configure sudo make sudo make install Now,...
Read more

DrillDown using Flot Charts

Image
Flot Charts is a pure JavaScript plotting library for jQuery, with a focus on simple usage, attractive looks and interactive features. For more information on Flot Charts please refer to http://www.flotcharts.org/ Flot Charts supports lines, points, filled areas, bars and any combinations of these, in the same plot and even on the same data series its also provide few additional plugins like pie charts and so on. In this tutorials, we will be creating Bar and Pie Chart DrillDown So, what exactly drilldown means, its opens up a new data set to explore with more granularity A drilldown with allows users to focus on the “data within the data” within a single chart area. Let's get started with real time scenario, Assume any college management software where we need to see individual student performance i.e Average score of the student in all 10 semester along with individual subject scores in each semester. Based on this usecase i have drilled down the charts for 2 level....
Read more

Securely setting Files and Folder permissions on Laravel 5

Image
Securely setting Files and Folder permissions on Laravel 5 Introduction: Handling file and folder permission in Laravel 5 and above. Why was this tutorial made? I had a similar issue while running Laravel Application and Cron Jobs on the same server. So what is the problem here? The issue here was while creating a log file. When the log files are created by the Web Application the file permission would look like. -rw-r--r-- 1 www-data www-data 1646 May 11 2017 laravel.log //Permissions are set to Apache User Similarly, when the log files are created by the Cron Jobs then the file would have its owner and group owner as your Apache User. -rw-r--r-- 1 ubuntu ubuntu 1646 May 11 2017 laravel.log //Permissions are set to User So, if the log file was created by Web Application same file can't be used bt Apache User and vice versa Error would look something like this The stream or file "/var/www/html/project-folder/storage/logs/lara...
Read more