Question

Source: https://stackoverflow.com/questions/68413018/how-can-we-ignore-the-commas-inside-barces-while-converting-csv-into-bean-or-st

Below is the sample CSV file. I’m trying to convert it to java bean/string[].

ID,NAME,DEP_DETAILS,REG_NO

P7-3001,JOHN,{"head_dep":"[{\"dep\":\"CS\",\"update_time\":\"2021-06-17T07:29:40.800Z\"}]","LN":false},009988

RT-9008,RIK,{"head_dep":"OPEN","dep_type":"S7"},889912

In the sample data, I want to read DEP_DETAILS in whole by ignoring comma's inside braces,

DEP_DETAILS is {"head_dep":"[{"dep":"CS","update_time":"2021-06-17T07:29:40.800Z"}]","LN":false} in 1st row and {"head_dep":"OPEN","dep_type":"S7"} in 2nd row.

So, the expected output of the 1st row, for example, will be:

- [0] = P7-3001,JOHN

- [1] = JOHN

- [2] = {"head_dep":"[{\"dep\":\"CS\",\"update_time\":\"2021-06-17T07:29:40.800Z\"}]","LN":false}

- [3] = 889912

How can I ignore the commas inside the brace and take whole as a string.

Are there any possibilities using openCsv or others?

Answer

Let me describe your need again. You want to parse each JSON string containing commas in a non-standard-format CSV file into a whole string. It is rather long to do the parsing in Java.

It is simple to get the job done in SPL, the open-source Java package. Only one line of code is enough:

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

…

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

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

st=con.prepareCall("call parseCsv ()");

st.execute();

…

Or execute the SPL string within a Java program in the way of executing a SQL statement:

…

st = con.prepareStatement("==file(\"json.csv\").import@cpt(ID,NAME,DEP_DETAILS:string,REG_NO)");
st.execute();

…

View SPL source code.