Converting Spreadsheets to JSON

06th January 2017

Imagine we have a spreadsheet of data, it could be an Excel XLS file or a Open Office ODS fie. Using our spreadsheet software of choice we should be able to save this file as a comma seperated values (CSV) file. Our exported CSV may look something like:

# data.csv
1,Product A,5.99
2,Product B,2.50
3,Product C,10.00

Given a CSV file we can use the following PHP code to transform the CSV into JSON. We're loading the file into an array and applying the str_getcsv function to each row. This parses each row as a string into an array for each column, this gives us a multidimensional array, the first level being each row, the second level being the columns. Next we loop round the array and use array_combine to change the keys to match our column headers. Finally we remove the header row from our array before converting the array to JSON.

$data = array_map('str_getcsv', file('data.csv'));
array_walk($data, function(&$a) use ($csv) {
    $a = array_combine($data[0], $a);
echo json_encode($data);

Our result output (once reformatted) will look like:

    "Title":"Product A",
    "Title":"Product B",
    "Title":"Product C",