CSV (Comma Separated Values) is one of the most popular methods for transferring tabular data between applications. Imagine you have an eCommerce website with hundreds of products and thousands of order history. And then, for safety purposes, you want to take a backup or you have to migrate to another domain – which justifies your business. Or you have started a dropshipping business where you use to take orders every day and then send all the orders to the franchisee.
In all the above cases, you would be able to manage until when the data is in a couple of tens. As soon as the data starts increasing to hundreds (not even thousands), you will feel the need for some data transfer mechanism that can create as well as transfer data accurately. This can be easily done using CSV files.
Topics at a glance
Create a CSV file from static data
Creating a CSV file from static data is fairly straightforward. Refer to the sample code below.
// open the file "my-csv.csv" for writing
$my_csv = fopen('my-csv.csv', 'w');
// save the column headers
fputcsv($my_csv, array('Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'));
// Static data.
$data = array(
array('A', 'B', 'C', 'D', 'E'),
array('F', 'G', 'H', 'I', 'J'),
array('K', 'L', 'M', 'N', 'O'),
array('P', 'Q', 'R', 'S', 'T'),
array('U', 'V', 'W', 'X', 'Y')
);
// save each row of the data
foreach ($data as $row) {
fputcsv($my_csv, $row);
}
// Close the file
fclose($my_csv);
Code Breakdown
$my_csv = fopen(‘my-csv.csv’, ‘w’);
We start here by creating a new CSV file, using PHP fopen function. We create and open the file in write mode, indicated by second argument ‘w’.
fputcsv($my_csv, array(‘Column 1’, ‘Column 2’…));
Next we start writing the headers in the CSV file, using fputcsv function.
fputcsv($ my_csv, $row);
Following the header format, we start writing data to the CSV file by looping over our static data.
fclose($my_csv);
Next, once we are done with editing the file, we will close the file using fclose.
This is all. Your CSV file is now ready for data transfer.
Overwrite a CSV file
In the example case of dropshipping that we discussed, we might have to update the CSV files as the order gets placed. This can be done by using the below code.
// open the file "my-csv.csv" for writing
$my_csv = fopen('my-csv.csv', 'a');
// Static data.
$data = array(
array('A1', 'B1', 'C1', 'D1', 'E1')
);
// save each row of the data
foreach ($data as $row) {
fputcsv($my_csv, $row);
}
// Close the file
fclose($my_csv);
As you can see, the above code is identical to the previous example except that we open the file with ‘a’ tag instead of ‘w’. For the list of possible modes, check PHP fopen.
Create a CSV file from remote data
Creating a CSV file from static data is good for learning purpose but most often you will be generating a CSV file from dynamic data. Dynamic data can be many places like data from a remote server or data from your database. Let’s see how we can create a CSV file from a remote server.
We will use jsonplaceholder.typeicode.com‘s user API for getting remote data. Refer to the code below.
$json_encoded_users=file_get_contents("https://jsonplaceholder.typicode.com/users");
$users = json_decode($json_encoded_users);
// open the file "my-csv.csv" for writing
$my_csv = fopen('my-csv.csv', 'w');
// save the column headers
fputcsv($my_csv, array('ID', 'Name', 'UserName', 'EMail', 'Phone', 'Website', 'Company'));
// save each row of the data
foreach ($users as $key => $user) {
$data = [
$user->id,
$user->name,
$user->username,
$user->email,
$user->phone,
$user->website,
$user->company->name
];
fputcsv($my_csv, $data);
}
// Close the file
fclose($my_csv);
Code Breakdown
$json_encoded_users=file_get_contents(“https://jsonplaceholder.typicode.com/users”);
We start here by fetching the data from remote endpoint.
$users = json_decode($json_encoded_users);
The data we get is json encoded. In order to use it, we are decoding it using PHP json_decode function.
Next we create a CSV file, loop over the users and add them to the CSV file. We believe the code is self-explanatory.
Create a CSV file from the database(MySQL)
Creating a CSV file from the database is similar to what we did in our previous examples. Find the sample code below.
// open the file "my-csv.csv" for writing
$my_csv = fopen('my-csv.csv', 'w');
// save the column headers
fputcsv($my_csv, array('ID', 'Name', 'UserName', 'EMail', 'Phone', 'Website', 'Company'));
// Connection to database
$connect = mysqli_connect('localhost', 'my_user', 'my_password', 'my_db');
// query the database
$query = 'SELECT fieldA, fieldB, fieldC, fieldD, fieldE FROM users_table';
if ($rows = mysqli_query($connect, $query)) {
// loop over the rows, outputting them
while ($row = mysqli_fetch_assoc($rows)) {
fputcsv($file, $row);
}
// free result set
mysqli_free_result($result);
}
// close the connection
mysqli_close($connect);
// Close the file
fclose($my_csv);
Create and Download a CSV file
Instead of saving on the server, you can force download the file and hence save the space on the server. Refer sample code below.
// output headers so that the file is downloaded rather than displayed
header('Content-type: text/csv');
header('Content-Disposition: attachment; filename="my-csv.csv"');
// do not cache the file
header('Pragma: no-cache');
header('Expires: 0');
// create a file pointer connected to the output stream
$my_csv = fopen('php://output', 'w');
// save the column headers
fputcsv($my_csv, array('Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'));
// Static data.
$data = array(
array('A', 'B', 'C', 'D', 'E'),
array('F', 'G', 'H', 'I', 'J'),
array('K', 'L', 'M', 'N', 'O'),
array('P', 'Q', 'R', 'S', 'T'),
array('U', 'V', 'W', 'X', 'Y')
);
// save each row of the data
foreach ($data as $row) {
fputcsv($my_csv, $row);
}
exit();
Code Breakdown
Here we start by setting proper headers and then create the CSV file.
header(‘Content-type: text/csv’);
This tells the browser that we are going to send the CSV file
header(‘Content-Disposition: attachment; filename=”my-csv.csv”‘);
This will tell the browser to download the file with the name as “my-csv.csv” instead of displaying it.
header(‘Pragma: no-cache’);
This tells the browser to not create any cache.
$my_csv = fopen(‘php://output’, ‘w’);
Next, instead of creating the file, we open the file pointer and start sending the data.
The rest of the code is the same as explained before.
Download an existing CSV file
If you already have a CSV file and want to just force download it, replace lines 9 to 27 with the below code, where $filename is the name of the file that you want to output.
readfile($filename);
That is all. We hope you found this article useful. We will be back with some more interesting articles. Until then, HAPPY CODING!!!