Upload Large CSV File to MySQL Database through File Read Operation

What is File?

A file is a container in a computer system for storing information. Files used in computers are similar in features to that of paper documents used in libraries and files. There are different types of files such as text files, data files, directory files, binary and graphic files, and these different types of files store different types of information. In a computer operating system, files can be stored on optical drives, hard drives, or other types of storage devices. This is very important part in Laravel development

Why File Read Operation is required to upload a large CSV file to MySql Database?

Sometimes we have to upload large size of excel or CSV data to the MySQL database. The size of data may be 200 MB or more size. In a normal process, we can't upload that huge amount of data to the database. There are many techniques used for this. But programmatically file read operation is one and easiest technique that I have used here.

Actual a Comma-separated values (CSV) file stores tabular data (numbers and text) in plain-text form. Often it is one record per line separated by a comma or any other delimiter. In PHP it is often useful to be able to read a CSV file and access its data. That is where the fgetcsv() function comes in used, it will read each line of a CSV file and assign each value into an ARRAY. The given coding shows how to read our CSV file and return an ARRAY holding the data from the CSV.

Use the below code to fulfill your requirement:

<?php	
$url = $request->file('import_file')->getRealPath();  /// DEFINE FILE PATH HERE///
$outputFile = 'stockfile-part-';   /// NAME OF THE FILE IN WHICH NAME YOU WANT TO UPLOAD///
$splitSize = 50000;  // 50k records in a one file
$in = fopen($url, 'r');  /// OPEN THE UPLOADED FILE///
$rows = 0;
$fileCount = 1;
$out = null;
while (( $data = fgetcsv($in)) !== FALSE) ///START READ OPERATION OF THE UPLOADED FILE///
{
    if (($rows % $splitSize) == 0) {
        if ($rows > 0) {
            fclose($out);
        }
        $fileCount++;
        // for filenames like indiacountry-part-0001.csv, indiacountry-part-0002.csv etc
        $fileCounterDisplay = sprintf("%04d", $fileCount);
        $fileName = "$outputFile$fileCounterDisplay.csv";
        $out = fopen($fileName, 'w');
    }				  
//dd($data);	
$final_array=array();
for($i=0;$i<1;$i++)
{      
   ///////////////////BELOW ARE INPUT OPERATION FROM EACH ROW OF CSV FILE////
	$importarrays['site_code'] = $data[0];  ///here $data[0], $data[1]....are the each column of csv file///
	$importarrays['category1'] =$data[1];
	$importarrays['category2']= $data[2];
	$importarrays['category4'] =$data[3];	
	$importarrays['division'] = $data[4];
	$importarrays['section'] =$data[5];
	$importarrays['department']= $data[6];
	$importarrays['barcode'] =$data[7];
	array_push($final_array,$importarrays);  
}												
foreach($final_array as $arr) 
{
	\DB::table('closing_stock')->insert($arr);  //// HERE IS THE DATABASE INSERT OPERATION/////
}				
    $rows++;
}
fclose($in);  /// CLOSING THE FILE AFTER COMPLETE FILE READ OPERATION//// 
?>

Conclusion:

You can apply the code in laravel development. Please share your review in the comment section.