JSON to CSV (and Excel) Conversion Utility

After trying to search around for a quick, free, and easy way to convert JSON data into a CSV I came up empty handed. So I started working on a solution: JSON2CSV. That’s all it does. Takes JSON data either through POST data or file upload. It then spits out a CSV with your data. CSVs can also be opened in Excel which you can then save as a xls or xlsx file. It’s up on GitHub and you’re welcome to do whatever you’d like to do with it: github.com/danmandle/JSON2CSV

Update! (2/2/2013) I’ve modified the script to work from the command line as well. Simply execute the code below in a Mac/*nix terminal:

php json2csv.php --file=/path/to/source/file.json --dest=/path/to/destination/file.csv

Update! (6/23/2013) CSVs now have headers! The key name for the field is now the first row of the CSV.

Update! (7/24/2013) There’s now basic error handling for invalid JSON or JSON with nested elements.

But if you don’t want to download it…

You can use it here. You can either paste the JSON data into form below or you can upload a file with the JSON data.

NOTE: This script does not currently support nested or multidimensional arrays.
Be sure to validate your JSON before using the tool at JSONlint.com.

JSON Data:

Or JSON text file:

Click to download:

Did that work well for you? Send me a beer to say thanks:

Donate Bitcoins




Donate Dogecoin: DJoF2AUA7HyoQEhwmSG5jCSHJorFZWNd9p
Donate Bitcoins

57 responses to “JSON to CSV (and Excel) Conversion Utility

  1. Michael

    Just tried your service and it seems to be spitting out errors when I try converting my JSon file to CSV. Just a heads up.

  2. Anonymous

    Thank you for doing this. Such a common need (and simply to provide) yet there were very few services that offered this online (for free).

    Instead of downloading an HTML, how about displaying i as an HTML table so that I can copy and paste.

    • danmandle

      Not quite sure what you mean by “an HTML”, but the utility converts JSON to a CSV which can be opened by Microsoft Excel, OpenOffice, Google Docs, and many more.

  3. Terry

    Dan, I’ve been looking online for a tool exactly like JSON2CSV. Plus, your PHP script is hot off the press! However, I am unfamiliar with the PHP environment. I now have a IIS server up and running on my laptop, but cannot get the script to run on a very short “test.JSON” sample which is in the same folder as JSON2CSV.php. Could you please post a command line example for your script?

    • danmandle

      Thanks for the suggestion, Terry. I took some time this morning and have updated the script to also accept command line arguments. You can now run this command from the command line:

      For Mac/*nix:
      php json2csv.php --file=/path/to/source/file.json --dest=/path/to/destination/file.csv

      For Windows (I’m guessing):
      php json2csv.php --file=c:\path\to\source\file.json --dest=c:\path\to\destination\file.csv

      Note: I’ve tested this using Apache on my Mac. If you only have IIS set up for this, you might want to consider using something like XAMPP instead of IIS.

  4. ken

    I’ve tried a few times, with valid JSON, but always the same results:

    Warning: fputcsv() expects parameter 2 to be array

  5. ken

    Yes, the JSON was validated on jsonlint.com.

    • Dan Mandle

      Hmmmm That’s bizarre. That error would be thrown if it couldn’t process the JSON, which would normally be caused by invalid JSON. If you want to send me a sample of your JSON data, I can try and take a look some time this week.

  6. Sanctusgee

    Hi Dan,

    thanks for taking time to work on this.

    Using your utility, my Jsonlist.com validated json file returns a csv file containing only the words (see below)
    “Array Array Array Array Array Array Array Array”

    Any ideas?
    Thanks

  7. Jules

    Same here. Each cell just has “Array”. Maybe it has to do with inside recursive JSON doc. E.g. Mine is
    { “_id” : “301bb353-52ed-4f1e-8e4b-fe49ea7c270f”, “records” : [ { “timestamp” : { “$date” : 1360243868967 }, “snapshot” : { “updated” : { “$date” : 1360243840329 }, “nosub_msgu” : 16, “tx_pkts” : 417158522, “rx_pkts” : 160889909, “tx_msgu” : 395265371, “rx_msgu” : 131794372, “nss_pos_leaves” : 0, “nss_pos_join” : 0, “nss_subs_fwd” : 0, “nss_subs_del” : 16, “nss_subs_add” : 38, “nss_subs_mcast” : 0, “nss_subs_ucast” : 22, “nss_subs_wildcard” : 13, “nss_subs_topic” : 9, “nss_sub” : 22 } }, { “timestamp” : { “$date” : 1360243899040 }, “snapshot” : { “updated” : { “$date” : 1360243870339 }, “nosub_msgu” : 16, “tx_pkts” : 417160330, “rx_pkts” : 160891567, “tx_msgu” : 395265687, “rx_msgu” : 131794474, “nss_pos_leaves” : 0, “nss_pos_join” : 0, “nss_subs_fwd” : 0, “nss_subs_del” : 16, “nss_subs_add” : 38, “nss_subs_mcast” : 0, “nss_subs_ucast” : 22, “nss_subs_wildcard” : 13, “nss_subs_topic” : 9, “nss_sub” : 22 } }]}

  8. Hi, i tried to convert my json data (validated by jsonlint) but the csv i received is made only by one row of “array”.
    Can u help me ?
    Vittorio

  9. Rohit Wadhwa

    Found this error in my downloaded csv file :

    Warning: Invalid argument supplied for foreach() in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 30

  10. Matias

    This is very nice. Looking forward to support for nested arrays. Thanks a lot.

  11. K

    All I get in the result is “Array”,”Array”,”Array”….

  12. Teddy

    Is there any chance that you could update this so that it captures the field names and uses those as column headers?

  13. Stuart

    Great tool, I use it quite a bit! Just wondering if you plan to retain the header fields at all? By this I mean, in an array like this:

    “city”:”New York”,
    “state”:”New York”,
    “country”:”USA”

    the CSV would output

    city,state,country
    New York,New York,USA

    rather than just

    New York,New York,USA

    Thanks again Dan.

    • Dan Mandle

      That would be a good addition. It’ll probably be a little while before I get back to this project though. If you’re able, feel free to fork the project on GitHub and do a pull request with the modification.

    • Dan Mandle

      Good news! I finally got around to modifying the script and now it will also provide header data!

  14. Kavya

    Hi can we do it in a reverse way? I have a cvs file and I want to convert it to JSON data.

  15. rajeev

    This code does not work on my file . I validated it too. I wonder why so many codes on web do not work :-( . It ‘successfully’ generated a CSV, but when I opened the CSV, it was full of errors and warnings – “Warning: array_keys() [function.array-keys]: The first argument should be an array in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 20

    • Dan Mandle

      The data that you are trying to run through the tool is a nested array. This tool does not support nested arrays at this time.

  16. Rich Whiting

    Dan,

    When I paste my simple json data into your on-line example, it works just fine, but when I download and install your code from github and paste the data into it, I get the following errors:

    Warning: array_keys() [function.array-keys]: The first argument should be an array in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 20

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 20

    Warning: array_merge() [function.array-merge]: Argument #1 is not an array in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 23

    Warning: array_merge() [function.array-merge]: Argument #2 is not an array in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 23

    Warning: Cannot modify header information – headers already sent by (output started at C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php:20) in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 34

    Warning: Cannot modify header information – headers already sent by (output started at C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php:20) in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 35

    Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\pfs\libs\json2csv\json2csv.class.php on line 39

    And yet, when I load the same json data into it as a file (rather than pasting it into the window), it works fine! Could there be something wrong with the source code on github?

    Thanks!

    • Dan Mandle

      The code on GitHub is the same code that is being executed on my website. Perhaps it’s an issue with the Windows line endings? From your error messages, it looks like the script is not able to decode the JSON that you’re inputting. It’s likely due to a syntax error in the JSON when you’re pasting it, which leads me to believe that it may have to do with the line ending type.

      • Rich Whiting

        Dan,

        Thank you very much for your reply. Apologies in advance, but I’m unclear on how I would go about fixing this. The server is running php on Linux (Apache), only the browser is running on Windows (of course the jquery is running client-side, isn’t it). But again, the json validates when I check it in jsonlint, and it works fine when I paste it into your website. It’s the same json, only pasted into different instances of the script. Only when I paste it into the script downloaded from github and run on Linux do I get the errors.

        Would you be able to give me some further clues as to how I might address “Windows line endings”? Or perhaps some ideas on tests I could run to narrow down the underlying problem? I do appreciate your taking the time to help.

        • Rich Whiting

          Dan,

          I resolved the problem. By putting a die(print_r($JSONdata) at the beginning of the readJSON function, it was able to see slashes before the double quotes surrounding my key-value pairs. So I merely added a stripslashes to json2csv.php, thusly:

          } elseif ($_POST[‘json’] != NULL) {
          $JSON2CSV->readJSON(stripslashes($_POST[‘json’]));
          $JSON2CSV->browserDL(“JSON2.CSV”);
          }

          and that appears to have solved the problem. I’m probably not doing this in the best practices fashion, but I’m somewhat new to much of the technology. Perhaps I should be using some other php syntax rather than stripslashes, but at least that appears to work!

          Thanks for pointing me in the direction I needed to go.

  17. Rich Whiting

    Thanks, Dan. Really appreciate the help!

  18. haim evgi

    great tool great tool, 10x

  19. Andy

    It’d be great if this could support nested and multidimensional arrays! Is that in the cards for the future?

    • Dan Mandle

      It is something that I would like to do, and I would release the code, but I likely wouldn’t run it on my website as it would be rather resource intensive. If you have the ability to do it, feel free fork the project on GitHub and do a pull request on the changes. Of course, a donation might encourage me to get on it sooner rather than later ;-)

  20. elnaz

    I have a big json file to convert to csv. I did split it to 100 files and I stored the file names in “file_name” variable. I wrote the following script to run the converter for each of them.

    #!/bin/bash
    shopt -s expand_aliases

    while read line; do
    #cat $line
    org_file_name=`echo $line | awk -F “/” ‘{print $3}’`
    csv_name=`echo “${org_file_name}.csv”`
    php JSON2CSV-master/json2csv.php –file=”${line}” –dest=”${csv_name}”
    done < file_name

    #echo This is the end.

    But it returns error. Why?
    (When I run the program for one file manually it works well)

  21. Amey

    Hi,
    well i am stuck at a point where i have a josn file with around 25lac lines and around 1lac records in it.
    i want to convert it into csv file.
    now if i try to write java scripts to do the same i get out of memory error.
    tried an online solution and obviously i don’t see any online conversion tool getting me any solution.
    what do you have to say about it?

    • Dan Mandle

      I’m sorry, but I don’t really understand what you’re getting at. I don’t know what a ‘lac’ is and I’m not sure what you’re trying to do with the JavaScript.

  22. I suppose that you have not had an opportunity to create a converter that can handle nested arrays. If you have any suggestions, please let me know.

  23. darren

    Always getting this reply Dan

    Warning: array_keys() expects parameter 1 to be array, null given in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 21

    Warning: Invalid argument supplied for foreach() in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 21

    Warning: array_merge() [function.array-merge]: Argument #1 is not an array in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 24

    Warning: Invalid argument supplied for foreach() in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 56

  24. HI Guys,

    I have a large JSON file – size: 1.8 GB. Need to convert it into csv. Then i will try to import that csv in phpmyadmin to incorporate in mysql db. But as the file is very large i can’t make it a success using your technique.as mentioned above. I am using XAMPP on Windows. Is there any command line tool that accomplish my purpose..? Any help will be highly appreciated.

    • Dan Mandle

      I haven’t used Windows for a while now, but you should be able to find a way run PHP from the command line. From there, you can run the script from the command line. You might have to also increase the max run time in the php.ini as well.

  25. Lee

    I want to set this up as a Cron Job on my server, basically so that it will take the JSON data from a URL and then convert it to a CSV file and place it into a folder without me having any input.
    Will that be possible with this code?
    It does the job fine manually, when I paste the code into the box.

    I’ve looked everywhere for the last 2 days and my hairs falling out… There’s not much left and I don’t want to be bald. Please help!

    • Dan Mandle

      Yup, it’s very possible and not too difficult, however it requires more detail than is worth going into here. I am available as a freelancer if you want help.

      • Lee

        Thanks for the offer! I’ve actually just managed to do it! I takes the JSON and imports it directly into MySQL. Without the need of converting to CSV first (I thought that was going to be my only option). But I’ll keep the offer in mind for future. Thanks.

Leave a Reply