0

not done this before. looking for a little direction and help. thanks all.

I need to take a csv data file and convert it to a json file of specific format. I'm reading up on JsonObject and JsonArray right now, ... thanks!

I have something like this.

timestamp,NST_MD3_CRANK_CASE,NST_MD3-TAH-0001,NST_MD3-TAH-0002,NST_MD3-TAH-0003,NST_MD3-TAH-0004,NST_MD3-TAH-0005,NST_MD3-TAH-0006,NST_MD3-TAH-0007,NST_MD3-TAH-0008,NST_MD3-TAH-0009,NST_MD3-TAH-0010,NST_MD3-TAH-0011,NST...
4/10/2018 7:55:10 PM,101,261,258,276,267,274,279,273,286,273,276,288,292,268,297,285,316,81,136,100,0,0,388,395,84,956,20,1,0,100,0,17,0,31,16,31,18,23,694.7667,59.98439,0,0,0,1,14998,13,24
4/10/2018 7:58:10 PM,101,261,258,276,267,274,279,273,286,273,276,288,292,268,297,285,316,81,136,100,0,0,388,395,84,956,20,1,0,100,0,17,0,31,16,31,18,23,694.9326,59.92459,0,0,0,1,14998,13,24

And I need it in a Json format like this...

{ "tags": [ {"tagId": "NST_MD1_CRANK_CASE","data":[ {"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}] }, ... ] }

I am using camel, java and jackson. thanks again!

GLMills
  • 558
  • 1
  • 12
  • 37
  • I think you should check this question: [directly convert CSV file to JSON file using the Jackson library](https://stackoverflow.com/questions/19766266/directly-convert-csv-file-to-json-file-using-the-jackson-library) – ArtemisFool May 14 '18 at 15:49
  • thans Artemis, good, but doesn't fit my implementation, thinking more towards the use of JsonObject, JsonArray, JsonFactory, writter etc.. doesn't cover creating a specific structure, at least that I'm aware. thanks for your answer. – GLMills May 14 '18 at 17:09
  • I intend to use the splitter EIP, store the header, build the data into it, then map to Json. at least that's what I'm thinking out of the gate. – GLMills May 14 '18 at 17:10
  • I'm hoping to get some feel on how to build a json structure like: { "tags" : [ {"tagId": "NST_MD1_CRANK_CASE","data":[{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}]}, learning json objects and json arrays. string : value=array object : string string= array list of name:values – GLMills May 14 '18 at 17:24

2 Answers2

0

Here is it using org.json: if you need to add more data just add more objects (data2,data3...) to JSONArray. Be careful with parenthesis while working with JsonArrays!

Expected output:

{
"tags": [{
    "data": [{
        "q": "3",
        "v": "100",
        "ts": "2017-11-15T19:55:00"
    }],
    "tagId": "NST_MD1_CRANK_CASE"
}]

}

here is the code:

package test;

import org.json.JSONArray;
import org.json.JSONObject;

public class jsonExample {


    public static void main(String[] args) {
        //create our data
        String tagId = "NST_MD1_CRANK_CASE";
        JSONObject data1 = new JSONObject();        
        data1.put("ts", "2017-11-15T19:55:00");
        data1.put("v", "100");
        data1.put("q", "3");

        //create dataArray -> JSONArray
        /*
          [{ "ts": "2017-11-15T19:55:00",
             "v": "100",
             "q": "3" }] 
         */
        JSONArray dataArray = new JSONArray();
        dataArray.put(data1);

        //create internal JSON Object
        /*  
          {
            "tagId": "NST_MD1_CRANK_CASE",
            "data": [{
                "ts": "2017-11-15T19:55:00",
                "v": "100",
                "q": "3"
            }]
        }
         */
        JSONObject internal = new JSONObject();
        internal.put("tagId", tagId);
        internal.put("data", dataArray);

        //create tags -> JSON Array
        /*  
            [{
                "tagId": "NST_MD1_CRANK_CASE",
                "data": [{
                    "ts": "2017-11-15T19:55:00",
                    "v": "100",
                    "q": "3"
                }]
            }]
         */
        JSONArray tags = new JSONArray();
        tags.put(internal);

        //create final JSON Object
        /*
          {
            "tags": [{
                "tagId": "NST_MD1_CRANK_CASE",
                "data": [{
                    "ts": "2017-11-15T19:55:00",
                    "v": "100",
                    "q": "3"
                }]
            }]
         }
         */
        JSONObject finalJson = new JSONObject();
        finalJson.put("tags", tags);
        System.out.println(finalJson.toString());

    }


}
ArtemisFool
  • 1
  • 3
  • thank you Artemis, still playing around with java json classes, learning these new classes for me. greatly appreciate your help. this looks fairly straight forward, thanks – GLMills May 15 '18 at 12:55
  • Is there a Maven dependency to use these Json packages to put in the POM so these can be imported? – GLMills May 15 '18 at 14:42
  • it looks that Jackson is what is available for me to use. Jackson appears to be much more complicated than these libraries. do you know how to put this in Jackson? – GLMills May 15 '18 at 16:21
  • thanks Artemis, I wish I could've used your libraries but your answer paved the way for me to accomplish this with Jackson. I've accepted your answer. – GLMills May 16 '18 at 15:37
0

Using Jackson libraries, I figured out how to implement this in a stepwise fasion, using JsonFactory and JsonGenerator. thank you everybody.

Code:

public class JsonMapper implements Processor {

    private int noOfRows = 0;  //does not include the header record
    private int noOfColumns = 0;  //includes timestamp field

    private static Logger log = LoggerFactory.getLogger(JsonMapper.class.getName());

    @Override
    public void process(Exchange exchange) throws Exception {

        log.info("Entering Json Mapper Processor...");

        String body = exchange.getIn().getBody(String.class);
        List<String> serviceRecords = new ArrayList<String>(Arrays.asList(body.split(System.lineSeparator())));
        noOfRows = serviceRecords.size() - 1;
        noOfColumns = getColumns(serviceRecords);
        StringBuilder header = getHeader( serviceRecords );
        serviceRecords.remove(0); //remove header record only data records in list
        String hdr[] = header.toString().split(",");
        //Noble_nst_mud-pump-2_NST_MUDPIT1_VOL,Noble_nst_mud-pump-2_NST_MUDPIT2_VOL,Noble_nst_mud-pump-2_NST_MUDPIT3_VOL,Noble_nst_mud-pump-2_NST_MUDPIT4_VOL,Noble_nst_mud-pump-2_NST_MUDPIT5_VOL,Noble_nst_mud-pump-2_NST_MUDPIT6_VOL,Noble_nst_mud-pump-2_NST_MUDPIT7_VOL,Noble_nst_mud-pump-2_NST_MUDPIT8_VOL,Noble_nst_mud-pump-2_NST_R_MUDPIT1_VOL,Noble_nst_mud-pump-2_NST_R_MUDPIT2_VOL,Noble_nst_mud-pump-2_NST_R_MUDPIT3_VOL,Noble_nst_mud-pump-2_NST_R_MUDPIT4_VOL,Noble_nst_mud-pump-2_NST_MP2_MOT_RUN_001,Noble_nst_mud-pump-2_NST_MP2_AI_021,Noble_nst_mud-pump-2_NST_MP2_AI_023,Noble_nst_mud-pump-2_NST_MP2_AI_022,Noble_nst_mud-pump-2_NST_MP2_AI_004,Noble_nst_mud-pump-2_NST_MP2_AI_005,Noble_nst_mud-pump-2_NST_MP2_AI_006,Noble_nst_mud-pump-2_NST_MP2_AI_031,Noble_nst_mud-pump-2_NST_MP2_AI_033,Noble_nst_mud-pump-2_NST_MP2_AI_032,Noble_nst_mud-pump-2_NST_MP2_AI_011,Noble_nst_mud-pump-2_NST_MP2_AI_012,Noble_nst_mud-pump-2_NST_MP2_AI_013,Noble_nst_mud-pump-2_NST_MP2_MOT_RUN_010,Noble_nst_mud-pump-2_NST_MP2_MOT_RUN_004,Noble_nst_mud-pump-2_NST_MP2_MOT_RUN_005,Noble_nst_mud-pump-2_NST_MP2_MOT_RUN_006,Noble_nst_mud-pump-2_NST_MP2_MOT_RUN_009
        //04/10/2018 07:55:10 PM, 1, 0, 2, 0, 81, 64, 64, 0, 0, 0, 0, 0, 0, 0, 0, 0, 289.4146, 288.65, 288.65, 0, 0, 0, 288.9146, 291.15, 289.2283, 0, 0, 0, 0, 0
        //String [] record = serviceRecords.toArray(new String[serviceRecords.size()]);

        JsonFactory factory = new JsonFactory();
        StringWriter sw = new StringWriter();
        JsonGenerator generator = factory.createGenerator(sw);
        generator.setPrettyPrinter(new DefaultPrettyPrinter());

        generator.writeStartObject();  // {
        generator.writeFieldName("tags"); // {"tags":
        generator.writeStartArray(); // {"tags": [

        for ( String rec : serviceRecords ) {
            String[] data = rec.split(",");

            for ( int i = 0; i < (noOfColumns - 1); ++i ) {  //header has 1 less field than data record         
                generator.writeStartObject(); // {
                generator.writeStringField("tagId", hdr[i].trim()); // { "tagId": "NST_MD1_CRANK_CASE" 
                generator.writeFieldName("data"); // { "tagId": "NST_MD1_CRANK_CASE", "data"
                generator.writeStartArray(); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [
                generator.writeStartObject(); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{
                generator.writeStringField("ts", data[0].trim()); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00"
                generator.writeStringField("v", data[i+1].trim()); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100"
                generator.writeStringField("q", "3"); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"
                generator.writeEndObject(); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}
                generator.writeEndArray();  // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}]
                generator.writeEndObject(); // { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}]}
            }
        }
        generator.writeEndArray(); // {"tags": [ { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}]} ]
        generator.writeEndObject();  // {"tags": [ { "tagId": "NST_MD1_CRANK_CASE", "data" : [{"ts":"2017-11-15T19:55:00","v" : "100","q":"3"}]} ]}
        generator.flush();
        generator.close();

        log.info("JSON: " + sw.toString());

        exchange.getIn().setBody(sw.toString());
        sw.close();
        log.info("Leaving Json Mapper Processor...");

   }

    public StringBuilder getHeader(List<String> serviceRecords) {
        StringBuilder sb = new StringBuilder();
        for ( int i = 0; i < 1; i++ ) {
            log.debug( "count: " + i + " record: : " + serviceRecords.get(i).toString() );
            if ( i == 0 ) {
                String[] sa = serviceRecords.get(i).toString().split(",");
                for ( int j = 0; j < sa.length; ++j) {
                    if ( j != 0 ) {
                        sb.append(sa[j]).append(",");
                    }
                }
                sb.deleteCharAt(sb.lastIndexOf(",", sb.length())); 
            } else {
                break;
            }
        }
        return sb;
    }

    public int getColumns(List<String> serviceRecords) {
        int columns = 0;
        for ( int i = 0; i < 1; i++ ) {
            log.debug( "count: " + i + " record: : " + serviceRecords.get(i).toString() );
            if ( i == 0 ) {
                String[] sa = serviceRecords.get(i).toString().split(",");
                columns = sa.length;
            } else {
                break;
            }
        }
        return columns;
    }

}
GLMills
  • 558
  • 1
  • 12
  • 37