CSV to JSON in JAVA (org.json) Headers to keys not working

Question

This is the content of my CSV File:

FID,OBJECTID,SHAPE,LAGE,GRILLPLATZ_ID,RESERVIERUNG,WEBLINK1,SE_ANNO_CAD_DAT
"GRILLPLATZOGD.6748,6748,POINT (16.465255884594104 48.19018769574157),""22., Donauinsel, ca. 350 Meter stromab der Steinspornbrücke (Inselmitte, Erdwall)"",15,ja,http://www.wien.gv.at/amtshelfer/umwelt/wasserbau/donauinsel/grillplatzreservierung.html,"
"GRILLPLATZOGD.6749,6749,POINT (16.48177464603615 48.183356069714286),""22., Neue Donau, linkes Ufer, zwischen Steinspornbrücke und Waluliso Brücke (bei km 5,1) (Dammbereich) "",16,ja,http://www.wien.gv.at/amtshelfer/umwelt/wasserbau/donauinsel/grillplatzreservierung.html,"
"GRILLPLATZOGD.6750,6750,POINT (16.460158556964053 48.177745677669925),""11., Donaukanal, Alberner Hafenzufahrtsstraße, Nähe Margetinstraße"",0,nein,http://www.wien.gv.at/umwelt/wald/freizeit/grillen/,"
"GRILLPLATZOGD.6751,6751,POINT (16.22577870779843 48.20612009507929),""14., Auhof - Retentionsbecken"",0,nein,http://www.wien.gv.at/umwelt/wald/freizeit/grillen/,"

My program reads the file and then uses the org.json lib to produce a JSONArray filled with JSONObjects from the CSV.

The output of my program looks like this atm:

[{"FID":"GRILLPLATZOGD.6748,6748,POINT (16.465255884594104 48.19018769574157),\"22., Donauinsel, ca. 350 Meter stromab der Steinspornbrücke (Inselmitte, Erdwall)\",15,ja,http://www.wien.gv.at/amtshelfer/umwelt/wasserbau/donauinsel/grillplatzreservierung.html,"} and so on...

My problem is, that only the first part of the header (FID) from the CSV is used to build a JSON Key, the rest of the header (OBJECTID,SHAPE,LAGE,GRILLPLATZ_ID,RESERVIERUNG,WEBLINK1,SE_ANNO_CAD_DAT) is ignored.

Here is a snippet of my code from the CSV to JSON part.

import org.json.CDL;
import org.json.JSONArray;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

public class CSVReader {

    public static void main(String[] args) {
        String path = ""; //Insert path to CSV-File here
        StringBuilder content = new StringBuilder();
        String line;
        String stringtoJSON;

        try {
            BufferedReader reader = new BufferedReader(new FileReader(path));
           
            while ((line = reader.readLine()) != null) {
                content.append(line);
                content.append(System.lineSeparator());
            }
            stringtoJSON = content.toString();
            System.out.println(stringtoJSON);
            
            JSONArray jsonArray = CDL.toJSONArray(stringtoJSON);
            System.out.println(jsonArray);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

One JSON Object of the Array should look like this:

{"FID": "GRILLPLATZOGD.6748", "OBJECTID": "6748", "SHAPE": "POINT (16.465255884594104 48.19018769574157)", "LAGE": "22., Donauinsel, ca. 350 Meter stromab der Steinspornbrücke (Inselmitte, Erdwall)","GRILLPLATZ_ID": "15","RESERVIERUNG": "ja","WEBLINK1":"http://www.wien.gv.at/amtshelfer/umwelt/wasserbau/donauinsel/grillplatzreservierung.html"}

Found the solution with the help of @Skilled_Teaser

I altered his method to this:

public static String removeUnnecessaryQuotes(String s){
         String withoutQuotes;
         withoutQuotes = s.substring(0).replaceAll("\"", "");
         withoutQuotes.substring(0).replaceAll("\"\"", "\"");
         return withoutQuotes;
    }

and now it works like a charm. Thanks for the help.

Skilled_Teaser · Accepted Answer · 2022-04-08T19:38:09.233

0

Probably there's a problem with your CSV file. Try the one below.

FID,OBJECTID,SHAPE,LAGE,GRILLPLATZ_ID,RESERVIERUNG,WEBLINK1,SE_ANNO_CAD_DAT
"GRILLPLATZOGD.6748",6748,POINT (16.465255884594104 48.19018769574157),"22., Donauinsel, ca. 350 Meter stromab der Steinspornbrücke (Inselmitte, Erdwall)",15,ja,http://www.wien.gv.at/amtshelfer/umwelt/wasserbau/donauinsel/grillplatzreservierung.html
"GRILLPLATZOGD.6749",6749,POINT (16.48177464603615 48.183356069714286),"22., Neue Donau, linkes Ufer, zwischen Steinspornbrücke und Waluliso Brücke (bei km 5,1) (Dammbereich)",16,ja,http://www.wien.gv.at/amtshelfer/umwelt/wasserbau/donauinsel/grillplatzreservierung.html
"GRILLPLATZOGD.6750",6750,POINT (16.460158556964053 48.177745677669925),"11., Donaukanal, Alberner Hafenzufahrtsstraße, Nähe Margetinstraße",0,nein,http://www.wien.gv.at/umwelt/wald/freizeit/grillen/
"GRILLPLATZOGD.6751",6751,POINT (16.22577870779843 48.20612009507929),"14., Auhof - Retentionsbecken",0,nein,http://www.wien.gv.at/umwelt/wald/freizeit/grillen/

Consider to change the structure of your CSV file otherwise you'll need to use regex to split arguments into array and then create JSON.

Edit:

Okay, I found the problem. Please, add the method below and make the following change

while ((line = reader.readLine()) != null) {
            content.append(line);
            content.append(System.lineSeparator());
        }

Replace to:

while ((line = reader.readLine()) != null) {
        content.append(removeUnnecessaryQuotes(line));
        content.append(System.lineSeparator());
    }

And add method:

public static String removeUnnecessaryQuotes(String s){
    return s.substring(0, s.length() - 1).substring(1).replaceAll("\"\"", "\"");
}

edited Apr 08 '22 at 19:38

answered Apr 08 '22 at 18:02

Skilled_Teaser

51
4

Thank you for your reply and workaround. It does work with your file, but saly I am not allowed to alter the original file. I am not really sure how to split arguments into an array with regex, could you point out some articles or youtube videos, thanks - jj – javajarvis Apr 08 '22 at 18:45
Okay, I think i found the problem. Please, add the following method and make the changes. It should help. Unfortunately, I don't have any helpful materials about regex so I cant recommend anything. Good luck! :) – Skilled_Teaser Apr 08 '22 at 19:39
Thank you for your input. Could u please explain what the method removeUnnecessaryQuotes does and how it does it? That would really help a lot and I think then we could close the case. As far as I get it is that: return s.substring(0, s.length() - 1).substring(1) removes the first an last Quotes of each String, correct? But how does the .replaceAll("\"\"", "\"") work? Thanks again. – javajarvis Apr 09 '22 at 07:46
As you said s.substring(0, s.length() - 1).substring(1) removes the first an last Quotes of each String otherwise i'll be treated as an one argument. Then you have a double quotes in argument (`""22., Donauinsel,[..]""`) what is recognized as a faulty syntax. Method `replaceAll("\"\"", "\"")` replaces all occurences of double quote to single quote to fix the issue. To fully understand read more on: https://javarevisited.blogspot.com/2017/01/string-replaceall-example-how-to-replace-all-characters-and-substring.html https://stackoverflow.com/questions/1367322/what-are-all-the-escape-characters – Skilled_Teaser Apr 09 '22 at 10:10
So, I ve implemented your code, but that generates a new problem. Now the first "key":"value" pair {"FID":"GRILLPLATZOGD.6748",... is gone and the output starts with {"OBJECTID":"6748",... instead. – javajarvis Apr 09 '22 at 11:11

CSV to JSON in JAVA (org.json) Headers to keys not working

1 Answers1