How to Import/Export data from/to Excel Sheet

Do you want to learn the process to export your database to excel sheet or import data from excel sheet. It will be a piece of cake after you learn what I am going to teach you.

Read carefully the steps.  Don't give any hassle to your brain you will say such a easy method at the end of this post.

In-order to export and import data from and to database you need first to learn connecting and performing queries using PHP. As I am going to write code using PHP.If you don't have database connection knowledge. Please learn that first and re-read this article.

OK Lets proceed

First you need to know about CSV file

A file which contains the data which is separated from one another with comma is known as comma-separated values (CSV) file. Normally this file can be open in Microsoft Excel Sheet and can treated as XLS file. We will use CSV file to import and export data.

Here's the Code needed to Read the CSV File and Import Data using PHP

//Integrate this code in your PHP file for reading csv file and inserting data to table
$dbc=mysqli_connect('hostname','username','password','database_name');
$f = fopen('file_name.csv','r');

while($row=fgetcsv($f))

{

$query="Insert into table_name values('$row[0]','$row[1]')";

mysqli_query($dbc,$query); 
}

Here enter your hostname,username,password and database_name that is required for your database connection. After successfull connection you can proceed reading csv file.

First you need to make connection between your csv file and server using the default function fopen. Enter the location and filename you want to read This function is used to read files.

Since we just need to read file we have passed "r" for reading only.

fopen('file_name.csv','r');

Let's assume that the CSV file contains two field and table also contains two field.If you have more that two field  then you need to make changes according to it.

for e.g. if you got n column in CSV file and have n columns in table

$query="Insert into table_name values('$row[0]','$row[1]',.......,'$row[n-1]','$row[n]')";

The first the foremost thing you need to check is no of fields you have in your table ,As only for that fields you can insert data. If  CSV file contains less or more field  that required it may cause error.

Here's the Code needed to Read the Database and Export it to CSV File using PHP

//Integrate this code in your PHP file for exporting data to csv file

$dbc=mysqli_connect('hostname','username','password','database_name'); 
$f = fopen('file_name.csv','a');
$query="Select * from table_name";
$r=mysql_query($dbc,$query);

while($row=mysqli_fetch_array($r,MYSQL_NUM))

{

fputcsv($f,$row);

}

 Here we read the data from database and write it to the csv file. Query returns the data in the form of array which we write in the CSV file using the function fputcsv.

fputcsv($f,$row);

 Since we are going to write in the file the second parameter of the fopen function should be "a" as it indicates we are writing to the file and appending data to previous data of the file.