Question

Source:https://stackoverflow.com/questions/66796180/how-to-convert-a-csv-into-json-using-python-or-java-the-csv-is-representation-o

I get a CSV file like this:

attrib1_x , attrib1_y , attrib1_z_0_p , attrib1_z_0_c , attrib1_z_1_p , attrib1_z_1_c , attrib2_R , attrib2_K , attrib3

1 , 2 , 100 , 200 , 500 , 600 , 222 , 320 ,hello

---

The csv represents a JSON as follows:

---

{

"attrib1":{

"x":1,

"y":2,

"z":[{"p":100,"c":200},{"p":500,"c":600}]

},

"attrib2":{"R":222,"K":320},

"attrib3":"hello"

}

---

 

So basically here I get the above CSV, and need to convert it to the JSON structure shown

. Not sure how to do it. is there any library (Python/Java) which can help me with this?

If any solution/suggestion with different CSV header available that will also work. I can ask the team to provide me the CSV with different header names to represent the nested / arrays.

Answer

The first row of your CSV contains field names (which uses the underline to show the hierarchical relationship) and detail data starts from the second line, and you need to transform the CSV to JSON format. Here the difficulty is dynamic parsing. It will involve grouping, recursion, loop, conditional judgment and string concatenation. The code will be rather lengthy if you try to do this in Java.

Try using SPL, the open-source Java package, to do this. It is easy and five lines of code are enough:

	A	B
1	=file("data.csv").import@cw()
2	=i=0,ifn=false,A1(1).(~.split@p("_")).(~|A1(2)(#)).(~.(~=if(ifstring(~),"\""/~/"\"",~)))
3	func   recurse(AA)	>if(ifn==false,B1=left(B1,-i)/"{}"/right(B1,i),(B1=replace(B1,"{}","[]"),B1=left(B1,-i)/"{}"/right(B1,i),ifn=false)),i+=1,AA.group(~(1)).(if(ifnumber(~(1)(1)),(ifn=true,func(recurse,~.(~.m(2:)))),if(~.len()==1   &&   ~(1).len()==2,B1=left(B1,-i)/~(1).concat(":")/","/right(B1,i),(B1=left(B1,-i)/~(1)(1)/":"/right(B1,i),func(recurse,~.(~.m(2:)))))))
4	=func(recurse,A2)
5	=replace(replace(replace(B1,",}","}"),"}{","},{"),"}\"","},\"")

 

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as recurse.splx and invoke it in a Java application as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call recurse()");
st.execute();

…

View SPL source code.