Things I've Figured Out

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




Categories: Coding

Automatically Remove Old AWS Elastic Beanstalk Application Versions » « Getting GPSd to work with Python and Threading

66 Comments

  1. 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. 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

      February 1, 2013 — 4:06 pm

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

      February 2, 2013 — 9:09 am

      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. I’ve tried a few times, with valid JSON, but always the same results:

    Warning: fputcsv() expects parameter 2 to be array

  5. Yes, the JSON was validated on jsonlint.com.

    • Dan Mandle

      February 4, 2013 — 10:51 am

      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. 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. 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. 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. This is very nice. Looking forward to support for nested arrays. Thanks a lot.

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

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

  13. 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

      May 9, 2013 — 9:04 pm

      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

      June 24, 2013 — 8:12 am

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

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

  15. 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

      July 2, 2013 — 10:03 am

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

      July 24, 2013 — 9:51 am

      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.

      • 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.

        • 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. Thanks, Dan. Really appreciate the help!

  18. great tool great tool, 10x

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

    • Dan Mandle

      August 7, 2013 — 9:10 pm

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

      December 2, 2013 — 8:15 am

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

      June 25, 2014 — 5:05 pm

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

      June 27, 2014 — 8:07 am

      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.

      • 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.

  26. My JSON file is 295mb and when I try this via Terminal on my mac it says:

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 295157801 bytes) in /path/JSON2CSV-master/json2csv.class.php on line 15

    Any ideas how I convert such a large file?

  27. Dan – just wanted to let you know that for my purposes this converter worked great. I had to fix a few items in my (many) json files, but was able to get them all converted and concatenated into one gigantor CSV. Many thanks for providing this.

    Nils

  28. I really would like to have a drink with you irrespective to our place of haveing the drink, but I am having a problem.
    I am getting the following error:

    Fatal error: Allowed memory size of 94371840 bytes exhausted (tried to allocate 72 bytes) in /home/mandledp/danmandle.com/projects/JSON2CSV/json2csv.class.php on line 15

    I know that my file size is too large but I desperately want to convert this JSON data to csv.

    Any help will be appretiated.

    • Dan Mandle

      August 29, 2016 — 9:09 am

      You can download the source code and run it on your local machine with a higher memory limit. If you need help with that, or want me to run it, I’m available for hire.

  29. thank you for your effort .. can you give me a way to convert 9G json file … it’s too large i can’t open it online and all the software i downloaded didn’t work

    • Dan Mandle

      June 9, 2017 — 8:35 am

      As mentioned above, you can download my code and run it on your local machine. Alternatively, I am available for hire.

  30. Hi Dan, many thanks for this effort. However, I got the following message.

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20480 bytes) in /Users/lesliechen/Downloads/JSON2CSV-master/json2csv.class.php on line 15

    What can I do with it? Many thanks in advance!

Leave a Reply

Copyright © 2023 Things I've Figured Out

Theme by Anders NorenUp ↑