Instantly share code, notes, and snippets.

Embed
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

This comment has been minimized.

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").

@robflaherty

This comment has been minimized.

Owner

robflaherty commented Apr 30, 2012

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

@aendrew

This comment has been minimized.

aendrew commented Jun 1, 2012

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

@dwharris

This comment has been minimized.

dwharris commented Jan 24, 2013

Great script, thanks.

On a MAC add

ini_set("auto_detect_line_endings", true);
@Kostanos

This comment has been minimized.

Kostanos commented May 23, 2013

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

@russom-woldezghi

This comment has been minimized.

russom-woldezghi commented Aug 8, 2013

Thank you.

@ValentinH

This comment has been minimized.

ValentinH commented Nov 10, 2013

Really nice!

@gruckion

This comment has been minimized.

gruckion commented May 30, 2014

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

@francolaiuppa

This comment has been minimized.

francolaiuppa commented Jun 11, 2014

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

@ram-you

This comment has been minimized.

ram-you commented Jun 13, 2014

Thank you. Saved me.

@dev-ext

This comment has been minimized.

dev-ext commented Aug 10, 2014

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

@joshuaiz

This comment has been minimized.

joshuaiz commented Jan 30, 2015

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>
@albertcoder

This comment has been minimized.

albertcoder commented Apr 4, 2015

Thanks a million Rob! :)

@afenix

This comment has been minimized.

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.

@UTCWebDev

This comment has been minimized.

UTCWebDev commented Oct 14, 2015

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

This comment has been minimized.

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!

@hotgrandma69

This comment has been minimized.

hotgrandma69 commented May 23, 2016

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

@g4spow

This comment has been minimized.

g4spow 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?

@JohanDuran

This comment has been minimized.

JohanDuran commented Apr 9, 2017

Really useful. Thanks.

@imsabin

This comment has been minimized.

imsabin commented Jul 2, 2017

hi, my server has disabled fopen. so is there any alternative like using curl to get the same result??

@AaronDunphy

This comment has been minimized.

AaronDunphy commented Aug 15, 2018

Thanks for this.

Tried using it in Laravel for my seeders with a csv file and ended up needing to move the csvToArray function outside and call it via $this->csvToArray() due to a redeclare issue.

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