Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Convert CSV to JSON
<?php
/*
* Converts CSV to JSON
* Example uses Google Spreadsheet CSV feed
* csvToArray function I think I found on php.net
*/
header('Content-type: application/json');
// Set your CSV feed
$feed = 'https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0Akse3y5kCOR8dEh6cWRYWDVlWmN0TEdfRkZ3dkkzdGc&single=true&gid=0&output=csv';
// Arrays we'll use later
$keys = array();
$newArray = array();
// Function to convert CSV into associative array
function csvToArray($file, $delimiter) {
if (($handle = fopen($file, 'r')) !== FALSE) {
$i = 0;
while (($lineArray = fgetcsv($handle, 4000, $delimiter, '"')) !== FALSE) {
for ($j = 0; $j < count($lineArray); $j++) {
$arr[$i][$j] = $lineArray[$j];
}
$i++;
}
fclose($handle);
}
return $arr;
}
// Do it
$data = csvToArray($feed, ',');
// Set number of elements (minus 1 because we shift off the first row)
$count = count($data) - 1;
//Use first row for names
$labels = array_shift($data);
foreach ($labels as $label) {
$keys[] = $label;
}
// Add Ids, just in case we want them later
$keys[] = 'id';
for ($i = 0; $i < $count; $i++) {
$data[$i][] = $i;
}
// Bring it all together
for ($j = 0; $j < $count; $j++) {
$d = array_combine($keys, $data[$j]);
$newArray[$j] = $d;
}
// Print it out as JSON
echo json_encode($newArray);
?>

aendrew commented Apr 27, 2012

Great script, saved me a bunch of time for an interactive I was working on for the Guardian.

Couple quick things --
a. I was getting PHP warnings on ln. 54. Adding an @ in from of array_combine(); suppresses that.
b. If you're wanting the JSON to be consumed on a different domain than the one hosting the PHP (i.e., through jQuery's getJSON(); function), you'll need a way around AJAX's same domain origin policy. I've done so by changing the header to script/javascript and replacing line 59 with echo $_GET['callback']. '(' . json_encode($newArray) . ');';. This allows you to consume the output through JSONP by attaching a callback variable to the path (e.g., "?callback=mycallback").

Owner

robflaherty commented Apr 30, 2012

Thanks! Regarding the PHP warnings, what version of PHP are you using?

aendrew commented Jun 1, 2012

Sorry for the slow response -- I was using 5.2.17.

Great script, thanks.

On a MAC add

ini_set("auto_detect_line_endings", true);

Great script. Thanks, In my fork I made it for command line use.

Thank you.

Really nice!

Licensing? am I free to use this or are there any restrictions?

We executed this for CSV dataset that we got and the results were incredible. Thank you!!!!!!

ram-you commented Jun 13, 2014

Thank you. Saved me.

Thank you. I make my portfolio based on your script.

This is exactly what I needed.
It may be obvious but for anyone who needs to access the PHP created json via javascript, just use this:

<?php $json = json_encode($newArray); ?>
<script>
var $json = <?php echo $json; ?>;
// do your magic here
</script>

Thanks a million Rob! :)

afenix commented Jun 19, 2015

So awesome. Thanks much for the share! I was going to have to refactor a bunch of code, and this worked like a charm. I hope I can repay the favor one day.

I have a nested/setted JSON format I need to produce... Can anyone suggest how to format a spreadsheet to produce this: http://mapplic.com/plugin/mall.json

Or would that require a set of spreadsheets/CSVs?

ur92 commented Feb 25, 2016

Great Gist!

I forked it and shortened the csvToArray function to this:
$data = array_map('str_getcsv', file($feed));
as this is more compact and performance should be better.

Tested => works!

This is exactly what I was looking for! Thanks a million!!!!

jones98 commented Jun 9, 2016

My file path for my images is folder/imagename.jpg.

When I run this great script, this is the only thing throwing an error. The path come out as folder/imagename.jpg

Here is the screenshot of the issue:
http://i.imgur.com/t0GQAEv.jpg

Also, can the output be saved to a .json file rather than within the PHP?

Really useful. Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment