# Things I've Figured Out

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.

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.

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

Categories: Coding

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

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

• That did the trick! Thanks.

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

• Dan Mandle

February 7, 2013 — 7:51 am

The issue is that you have nested arrays. I’m working on some new stuff to handle that.

• yup, same problem here – pls do update, a million thnaks, this is awesome. (cant believe no one has done this earlier)

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 } }]}

• Dan Mandle

February 7, 2013 — 11:12 am

The issue is that you have nested arrays. I’m working on some new stuff to handle that.

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

• Dan Mandle

February 9, 2013 — 9:29 am

Currently the script doesn’t support nested arrays.

• Sorry, i read the last comment. ok, i0m waiting for your new stuff…
Vittorio

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

• Dan Mandle

April 2, 2013 — 5:19 pm

That’s because you have a nested array. This doesn’t currently support nested arrays.

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

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

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. Hi, thanks for your script 🙂
How can i work with the script with this JSON:

https://api.todoist.com/API/query?token=d2518a6541e3c0e0702bab9d9d161450f72aba05&queries=%5B%22viewall%22%5D

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

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

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

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

• Dan Mandle

March 26, 2015 — 8:36 am

You can either download the PHP script and run it on your computer or you can hire me to do it for you.

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

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

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

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

• Hi Dan, I have figured it out by adding “ini_set(‘memory_limit’, ‘-1’);” Many thanks for your scripts.