Skip to content

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.

Copy link

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.

Copy link
Owner Author

robflaherty commented Apr 30, 2012

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

@aendrew

This comment has been minimized.

Copy link

aendrew commented Jun 1, 2012

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

@dwharris

This comment has been minimized.

Copy link

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.

Copy link

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.

Copy link

russom-woldezghi commented Aug 8, 2013

Thank you.

@ValentinH

This comment has been minimized.

Copy link

ValentinH commented Nov 10, 2013

Really nice!

@gruckion

This comment has been minimized.

Copy link

gruckion commented May 30, 2014

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

@francolaiuppa

This comment has been minimized.

Copy link

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.

Copy link

ram-you commented Jun 13, 2014

Thank you. Saved me.

@dev-ext

This comment has been minimized.

Copy link

dev-ext commented Aug 10, 2014

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

@joshuaiz

This comment has been minimized.

Copy link

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.

Copy link

albertcoder commented Apr 4, 2015

Thanks a million Rob! :)

@afenix

This comment has been minimized.

Copy link

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.

Copy link

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.

Copy link

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.

Copy link

hotgrandma69 commented May 23, 2016

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

@g4spow

This comment has been minimized.

Copy link

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.

Copy link

JohanDuran commented Apr 9, 2017

Really useful. Thanks.

@imsabin

This comment has been minimized.

Copy link

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.

Copy link

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
You can’t perform that action at this time.