0

I have a code which converts CSV File to JSON String

public static Optional<String> csvToJSON(String filePath) {
        try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(filePath),
                StandardCharsets.UTF_8))) {
            String inputString;
            StringBuilder csvStringBuilder = new StringBuilder();
            while (!Objects.isNull(inputString = br.readLine())) {
                csvStringBuilder.append(inputString).append(NEW_LINE);
            }
            if (Objects.isNull(CDL.toJSONArray(csvStringBuilder.toString()))) {
                return Optional.empty();
            }
            String csvToJsonString = CDL.toJSONArray(csvStringBuilder.toString()).toString()
                    .replaceAll(NON_ASCII_CHARACTERS, BLANK);
            return Optional.of(csvToJsonString);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

For a CSV File like this

ID,Colour
"1","Black,White"
"2","Black,White,Blue"
"3","Black"
"4","Black,White"

The file is currently returning a JSON String which looks like this:

[
   {
      "ID":"1"
      "Colour":"Black,White"
   },
   {
      "ID":"2"
      "Colour":"Black,White,Blue"
   },
   {
      "ID":"3"
      "Colour":"Black"
   },
   {
      "ID":"4"
      "Colour":"Black,White"
   }
]

However, I want that while reading and converting the CSV File, if the header is "Colour", in the JSON String the colours should be printed as an array of strings

[
   {
      "ID":"1"
      "Colour":["Black","White"]
   },
   {
      "ID":"2"
      "Colour":["Black","White","Blue"]
   },
   {
      "ID":"3"
      "Colour":["Black"]
   },
   {
      "ID":"4"
      "Colour":["Black","White"]
   }
]

Where can I apply the splitting of strings logic in the code pasted above?

user1233
  • 5
  • 1
  • 3
  • 1
    Instead of parsing Json as if it was a string, I highly suggest you to use a library such as Jackson or Gson. That would greatly simplify your task. – Matteo NNZ May 31 '22 at 08:45
  • Can you please give me an example? – user1233 May 31 '22 at 08:47
  • Check [this out](https://www.baeldung.com/jackson-object-mapper-tutorial), my favorite one is Jackson. Basically you need to create a class describing the shape of your Json, and annotate it accordingly to allow Jackson to parse it from Json to Java object (deserialization) and vice versa (serialization). Once you do that, you simply convert your input string into a Java Object, and then build your CSV file using that object (which is way more easy to manipulate than raw strings). – Matteo NNZ May 31 '22 at 08:49
  • But content of CSV will be unknown to me, Colour header may ir may not be present in the file. So I have to handle the condition for when the header maybe present. There maybe additional headers too – user1233 May 31 '22 at 08:52

1 Answers1

0

You can easily split json value to array of strings with esProc SPL(a java based open-source scripting language for data processing):

SPL code like this:

=json(file("data.csv").import@cqt().run(Colour=Colour.split@c()))

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as split.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 split.splx()");
st.execute();
...

with

<!-- https://mvnrepository.com/artifact/com.scudata.esproc/esproc -->
<dependency>
    <groupId>com.scudata.esproc</groupId>
    <artifactId>esproc</artifactId>
    <version>20220402</version>
</dependency>

You can also execute the SPL code within a Java program as we execute a SQL statement:

...
st = con.prepareStatement("==json(file(\"data.csv\").import@cqt().run(Colour=Colour.split@c()))");
st.execute();
...

Actually,column 'ID' read as integer by default,if you need parse to string,then SPL code like this:

=json(file("data.csv").import@cqt().run(ID=string(ID),Colour=Colour.split@c()))
ginola
  • 1
  • 1