B u s L o g i c:- prolog, code, chatter.

Convert JSON to CSV

At work a client wanted a dump of the users and roles that were set for their webapp. I didn't have access to the database, only to an API which provided all the users in JSON format. The client didn't want JSON but rather CSV so they could search and filter as required. Prolog to the rescue!

For context, the users come with the following format:

        "userName": "jbloggs",
        "firstName": "Joe",
        "lastName": "Bloggs",
        "email": "Joe.Bloggs@blahville.com",
        "roles": [
            "Project Manager"
    ... etc ... 

And the output should in the form:

userName,firstName,lastName,email,roles(as comma separated list)

I discovered along the way the adding a comma separated list requires quotes around the value ... duh!

To start with, we need to include the http json libraries to read in original document.

:- use_module(library(http/json)).
:- use_module(library(http/json_convert)).

The CSV output will be done manually, so print the title first.

write_title(Fd) :-
    format(Fd, "Username,First Name,Last Name,Email,Roles,~n", []).

Convert the json dictionary to a comma separated list. The roles are a list so need to convert that to one value.

write_first(Fd, Y) :-
    atomic_list_concat(Y.roles, ",", Roles), 
    format(Fd, '~p,~p,~p,~p,"~w",~n', 
        [Y.userName, Y.firstName, Y.lastName, Y.email, Roles]).

The main predicate will handle the conversion.

convert_users :-

First open the JSON file and read in a dictionary.

open('users.json', read, Fd),
json_read_dict(Fd, Y),

Then open the output file, write the title and the contents.

open('users.csv', write, Fd2),
maplist(write_first(Fd2), Y),

After running this predicate there will be a users.csv file with one row per user.