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

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:

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.

1. danmandle

It’s possible that your JSON isn’t valid. Try validating it using JSONlint: http://jsonlint.com/

2. Oren

Great! Thanks! 🙂

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

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

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

1. Terry

That did the trick! Thanks.

4. ken

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

Warning: fputcsv() expects parameter 2 to be array

1. Dan Mandle

Did you confirm on jsonlint.com that you have valid JSON?

5. ken

Yes, the JSON was validated on jsonlint.com.

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

1. Dan Mandle

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

1. Darius

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

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

1. Dan Mandle

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

8. Vittorio Ziliani

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

1. Dan Mandle

Currently the script doesn’t support nested arrays.

2. Vittorio Ziliani

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

1. Dan Mandle

Did you validate the JSON at jsonlint.com? Is it a non-multidimensional array?

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

1. Dan Mandle

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

12. Teddy

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

1. Dan Mandle

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

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.

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

1. Stuart

That’s a good idea. I’ll take a look and see if I’m able.

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

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

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

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

1. 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. 1. Dan Mandle So I did some looking around for you, and it sounds like you have MagicQuotes enabled http://php.net/manual/en/security.magicquotes.php . This is only available on PHP versions older than 5.4.0. That’s why you have slashes in your JSON, it’s trying to escape the quotes. 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? 1. 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?

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

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.

1. Dan Mandle

You can’t, it has nested objects.

23. darren

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

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.

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

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

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

26. Alex

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?

1. Dan Mandle

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

27. nilsdavis

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