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.
January 29, 2013 — 8:27 am
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.
January 29, 2013 — 8:34 am
It’s possible that your JSON isn’t valid. Try validating it using JSONlint: http://jsonlint.com/
April 29, 2013 — 5:21 am
Great! Thanks! 🙂
February 1, 2013 — 3:59 pm
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.
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.
February 2, 2013 — 6:12 am
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?
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.csvFor Windows (I’m guessing):
php json2csv.php --file=c:\path\to\source\file.json --dest=c:\path\to\destination\file.csvNote: 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.
February 2, 2013 — 9:38 am
That did the trick! Thanks.
February 4, 2013 — 8:15 am
I’ve tried a few times, with valid JSON, but always the same results:
Warning: fputcsv() expects parameter 2 to be array
February 4, 2013 — 8:18 am
Did you confirm on jsonlint.com that you have valid JSON?
February 4, 2013 — 9:04 am
Yes, the JSON was validated on jsonlint.com.
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.
February 4, 2013 — 11:51 am
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
February 7, 2013 — 7:51 am
The issue is that you have nested arrays. I’m working on some new stuff to handle that.
April 24, 2013 — 6:11 am
yup, same problem here – pls do update, a million thnaks, this is awesome. (cant believe no one has done this earlier)
February 7, 2013 — 10:55 am
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 } }]}
February 7, 2013 — 11:12 am
The issue is that you have nested arrays. I’m working on some new stuff to handle that.
February 9, 2013 — 9:28 am
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
February 9, 2013 — 9:29 am
Currently the script doesn’t support nested arrays.
February 9, 2013 — 9:29 am
Sorry, i read the last comment. ok, i0m waiting for your new stuff…
Vittorio
February 16, 2013 — 4:39 am
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
February 16, 2013 — 11:28 am
Did you validate the JSON at jsonlint.com? Is it a non-multidimensional array?
March 27, 2013 — 8:20 am
This is very nice. Looking forward to support for nested arrays. Thanks a lot.
April 2, 2013 — 5:17 pm
All I get in the result is “Array”,”Array”,”Array”….
April 2, 2013 — 5:19 pm
That’s because you have a nested array. This doesn’t currently support nested arrays.
May 3, 2013 — 4:55 pm
Is there any chance that you could update this so that it captures the field names and uses those as column headers?
June 24, 2013 — 8:12 am
Good news! I finally got around to modifying the script and now it will also provide header data!
May 9, 2013 — 3:56 pm
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.
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.
May 15, 2013 — 8:14 am
That’s a good idea. I’ll take a look and see if I’m able.
June 24, 2013 — 8:12 am
Good news! I finally got around to modifying the script and now it will also provide header data!
May 19, 2013 — 11:53 pm
Hi can we do it in a reverse way? I have a cvs file and I want to convert it to JSON data.
January 6, 2014 — 10:42 am
Numerous online tools to convert CSV to JSON – here’s one:
http://www.convertcsv.com/csv-to-json.htm
July 2, 2013 — 9:51 am
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
“
July 2, 2013 — 9:52 am
And this was the API whose response I wanted to convert into CSV. http://api2.socialmention.com/search?q=%22teach+for+america%22&as_epq=&as_eq=allah%2C+islam%2C+Forest%2C+football&t=all&l=&tspan=m&num=100&lang=en&sort_by=date&as_fu=
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.
July 24, 2013 — 9:47 am
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!
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.
July 24, 2013 — 1:54 pm
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.
July 24, 2013 — 2:16 pm
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.
July 24, 2013 — 2:36 pm
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.
July 25, 2013 — 8:18 am
Thanks, Dan. Really appreciate the help!
July 31, 2013 — 5:48 am
great tool great tool, 10x
August 7, 2013 — 12:35 pm
It’d be great if this could support nested and multidimensional arrays! Is that in the cards for the future?
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 😉
August 28, 2013 — 1:53 pm
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)
December 2, 2013 — 4:34 am
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?
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.
March 14, 2014 — 10:07 pm
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.
June 3, 2014 — 5:54 am
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
June 19, 2014 — 1:54 pm
You can’t, it has nested objects.
June 15, 2014 — 9:21 am
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
June 25, 2014 — 2:59 pm
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.
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.
June 27, 2014 — 6:07 am
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!
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.
June 27, 2014 — 11:04 am
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.
March 26, 2015 — 8:23 am
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?
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.
April 8, 2015 — 3:37 pm
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
August 29, 2016 — 5:52 am
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.
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.
June 9, 2017 — 3:01 am
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
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.
November 30, 2017 — 2:19 am
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!
November 30, 2017 — 6:08 am
Hi Dan, I have figured it out by adding “ini_set(‘memory_limit’, ‘-1’);” Many thanks for your scripts.