But since I already talked about how to consume webservices (whether REST or SOAP) I am going to focus on the part that caught my attention: how to go from JSON to CSV.
Let’s clarify the many before going to the details:
What is JSON
JSON stands for JavaScript Object Notation, that is: JavaScript object notation. It’s beyond the scope of this article (and this blog in general) to talk about the benefits (or lack thereof) of JavaScript… there’s a lot of great stuff out there.
The point is that regardless of what you like or dislike, JavaScript has a very handy syntax for describing objects: anything enclosed in {} is an object (with the possible exception of function bodies). …).
This makes it an extremely convenient format for the exchange of information on complex structures, since it is a very simple way to go from an object in memory to a textual representation (which can be sent through a text exchange protocol… by HTTP example :)).
In short, a text that looks like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 | { "name": "Hayri", "friends": [ { "name": "Ahmet" }, { "name": "Huseyin" } ] } |
It would be representing an object with two properties: name and friends. The first is a string, the second is an array of other objects.
More information here.
It looks very simple (and it is!) but it has a small drawback: its syntax is very non-permissive (Leave one, drop and hold on :p), therefore, it is very convenient to use PHP’s own functions to manipulate it ( json_encode and json_decode).
And here it makes me a bit nostalgic to talk about the older post I wrote for this blog, but I think knowing how to iterate over a JSON using PHP can contribute to the answer.
What is CSV
CSV stands for Comma Separated Values. It is a text format that is also used for the exchange of information between systems.
Usually the content of this type of file is a succession of rows that correspond to records whose number of fields can vary from one to another and the length of each field also varies.
Example:
1 2 3 4 5 6 7 | Name, Surname, Mail, Age, City Mauro,Chojrin,mauro.chojrin@leewayweb.com,40,Buenos Aires, Steve,Jobs,sjobs@apple.com,,, Alfredo,,alopez@gmail.com,,, ,Gutierrez,dgutierrez@hotmail.com,46,, |
From JSON to CSV
A first caveat that must be made is that JSON is a format that allows complex structures (multilevel for example), while CSV is a much simpler format (it is based on “flat” structures), with which, something is will lose along the way (or some assumption or transformation of the information will have to be made).
For practical purposes, let’s assume that the starting JSON is single level, something like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | [ { "Name": "Mauro", "Surname": "Chojrin", "Mail": "mauro.chojrin@leewayweb.com", "Age": 40, "City": "Buenos Aires" }, { "Name": "Steve", "Surname": "Jobs", "Mail": "sjobs@apple.com", "Age": "", "City": "" }, { "Name": "Alfredo", "Surname": "", "Mail": "alopez@gmail.com", "Age": "", "City": "" }, { "Name": "", "Surname": "Gutierrez", "Mail": "dgutierrez@hotmail.com", "Age": 46, "City": "" } ] |
The corresponding CSV would be:
1 2 3 4 5 6 | name, surname, email, Mauro,Chojrin,mauro.chojrin@leewayweb.com, Pedro,Alvarez,palvarez@gmail.com, Laura,Perez,lau.perez@hotmail.com", |
A PHP code that achieves that is:
1 2 3 4 5 6 7 8 9 10 | <?php $f = fopen( 'output.csv', 'w+' ); $array = json_decode( file_get_contents( 'entry.json' ), true ); array_unshift( $array, [ 'name', 'surname', 'email' ] ); foreach ( $array as $object ) { fputcsv( $f, $object ); } fclose( $f ); |
Now, if the need is more complex… nothing better than searching a bit :).
