Skip to main content

Upload Large CSV into mysql in a minute PHP Script

How to upload large CSV file in Mysql Database within a minute [ PHP SCRIPT ]



IF you have large csv file and you want to upload in Mysql then mysql will take too much time to import that csv data . so here is the script that will  take less a minute and all csv data will uploaded in mysql.

This Script contains a html form you have to fill information of your database and also insert file name in "Name of the file" column and also notice that your file and this script must be exist in same folder.

SO here is the PHP script to upload large CSV file in mysql within a minute.

<?php 
if(isset($_POST['username'])&&isset($_POST['mysql'])&&isset($_POST['db'])&&isset($_POST['username']))
{
$sqlname=$_POST['mysql'];
$username=$_POST['username'];
$table=$_POST['table'];
if(isset($_POST['password']))
{
$password=$_POST['password'];
}
else
{
$password= '';
}
$db=$_POST['db'];
$file=$_POST['csv'];
$cons= mysqli_connect("$sqlname", "$username","$password","$db") or die(mysql_error());
$result1=mysqli_query($cons,"select count(*) count from $table");
$r1=mysqli_fetch_array($result1);
$count1=(int)$r1['count'];
//If the fields in CSV are not seperated by comma(,)  replace comma(,) in the below query with that  delimiting character 
//If each tuple in CSV are not seperated by new line.  replace \n in the below query  the delimiting character which seperates two tuples in csv
// for more information about the query http://dev.mysql.com/doc/refman/5.1/en/load-data.html
mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
        INTO TABLE '.$table.'
        FIELDS TERMINATED by \',\'
        LINES TERMINATED BY \'\n\'
')or die(mysql_error());
$result2=mysqli_query($cons,"select count(*) count from $table");
$r2=mysqli_fetch_array($result2);
$count2=(int)$r2['count'];
$count=$count2-$count1;
if($count>0)
echo "Success";
echo "<b> total $count records have been added to the table $table </b> ";
}
else{
echo "Mysql Server address/Host name ,Username , Database name ,Table name , File name are the Mandatory Fields";
}
?>



<html>
<head>
<title> csv2 sql</title>
<link rel="stylesheet" href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css">
</head>
<body>
<br>

</br>
<form class="form-horizontal" action="" method="post">
    <div class="form-group">
        <label for="mysql" class="control-label col-xs-2">Mysql Host name</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="mysql" id="mysql" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label for="username" class="control-label col-xs-2">Username</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="username" id="username" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label for="password" class="control-label col-xs-2">Password</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="password" id="password" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label for="db" class="control-label col-xs-2">Database name</label>
        <div class="col-xs-3">
        <input type="text" class="form-control" name="db" id="db" placeholder="">
        </div>
    </div>
    
    <div class="form-group">
        <label for="table" class="control-label col-xs-2">table name</label>
        <div class="col-xs-3">
        <input type="name" class="form-control" name="table" id="table">
        </div>
    </div>
    <div class="form-group">
        <label for="csvfile" class="control-label col-xs-2">Name of the file</label>
        <div class="col-xs-3">
        <input type="name" class="form-control" name="csv" id="csv">
        </div>
        eg. yourfile.csv  (Your file must be in same folder where this script exist)
    </div>
    <div class="form-group">
    <label for="login" class="control-label col-xs-2"></label>
    <div class="col-xs-3">
    <button type="submit" class="btn btn-primary">Upload</button>
    </div>
    </div>
</form>
</div>

</body>


So this is The PHP script that will import your large csv data file into mysql database within a minute.

 

How to Import large CSV file in Mysql Database within a minute [ PHP SCRIPT ]

 

Labels:

Comments

Post a Comment

Popular posts from this blog

Convert website to android and ios application using react native expo webview

Convert website to android and ios application using react native expo webview If you want to check you can check on github by using below link also dont forget to give star ;) Source Code: https://github.com/shubham715/react-native-expo-webview-convert-website-to-app React native is the best choice to create multi platform mobile application , but sometimes we dont want to write a complete application because we already have a web application or a website and its complicated to manage both . So we have a solution for this problem. React native supports webView that makes easy to run any website url like an app natively. What is webview in react native? In React native, WebView helps to show web content in a native view. For this tutorial we will use EXPO. What is EXPO ? EXPO a set of tools to help you quickly start an app. Expo have many inbuilt components that helps to simplify the development and testing of React Native app. So please follow the below steps to c...
45 comments
Read more

Solution-windows 'expo' is not recognized as an internal or external command

Solution for expo is not recognized as an internal or external command,operable program or batch file in Windows 10 Sometimes expo will not work globally mostly in windows 10, If you are facing same issue then follow the below Steps 1) Click on windows button and search for  " Environment variables"  and click on "Edit the system environment variables" 2) Now you will see a popup like below screen. Then you need to click on Environment Variables. (Please see highlight part in below image)     3)Then click on new button that i have highlighted in below image 4. Then a popup will open and you need to fill details like below mentioned Variable Name :Path Variable Value: %USERPROFILE%\AppData\Roaming\npm Here we are creating a new path variable and passing location of npm.   Now Click on OK and close all the terminal windows and open new CMD or terminal and type Expo . Great now you can access expo from any...
23 comments
Read more

Read files from folder using php

Read files from folder using PHP Today we learn how to read all files from a folder . we will learn to list all files and read all files . So please follow below steps:-   METHOD 1 1) List all files from folder If you want the list of all files in a folder then you can do by using below code //Get a list of file paths using the glob function. $allFilesList= glob('myfolder/*'); //Loop through the array that glob returned. foreach($allFilesList as $filename){ //Simply print them out onto the screen. echo $filename; echo '<br>'; } The above code will print list of all files like file1.jpg file2.png file3.gif file4.pdf   If you want to read only specific extension file like you just want to a list of all png files then you can do it by using below code. //Get a list of all files ending in .txt $fileList = glob('myfolder/*.png);   METHOD 2 Here is the second method. here we are using scandir() function to s...
1 comment
Read more