Import Large CSV file into MySQL PHP [load data infile mysql]

Brijpal Sharma
php sql   Publish on:-December 31st, 2020 , Category:- Web Development


In this blog, we Import a Large CSV file into MySQL by PHP using load data Infile MySQL. some time we required to upload large files such as up to 100MB.

So this is a very simple load data in file method to import a large CSV file data into the database. I use here PHP language but you can do it with any programming language. 

Step: 1 Upload CSV file.

In first step upload CSV file and define full path of csv in $filePath variable.


$filePath = "your csv file path";


\DB::connection()->getPdo()
->exec("LOAD DATA LOCAL INFILE '{$filePath }'
	INTO TABLE importer_bills
	FIELDS TERMINATED BY ',' 
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(bill_of_entry_no, bill_of_entry_date, importer_id,importer_name,importer_address, ... and more columns name as table)
	SET id = NULL, 

	");

In the above code, I ignore the first line of CSV because I don't want to insert the heading of CSV. In my table, the first column ID is the primary key and auto-increment. here I set it as null. 

MySQL load data Infile specify columns

If you want to insert specific columns you must add columns name at the bottom of your query such as.

MySQL load data infile example

LOAD DATA LOCAL INFILE '{$filePath }'
INTO TABLE importer_bills
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(bill_of_entry_no, bill_of_entry_date, importer_id,importer_name,importer_address, ... and more columns name as table)
SET id = NULL, 

 

By using this method I upload a 5 lakh record in one minute and the file CSV was 300MB. So here we completed the tutorial on load data infile MySQL home it helps you


Brijpal Sharma

Hello, My Name is Brijpal Sharma. I am a Web Developer, Professional Blogger and Digital Marketer from India. I am the founder of Codermen. I started this blog to help web developers & bloggers by providing easy and best tutorials, articles and offers for web developers and bloggers...


0 Comments

You must be logged in to post a comment.