convert excel to json file

Question

I'm new in creating jsons and having bais knowledge of java. I'm trying to convert database table data to json.

Having option to store table data in any format of file than convert that into json.

Here is my table data.

Table: PKGS

    Price,   pd,          Id,                 Level
  1    , 266     ,    59098      ,         5
  2    , 247     ,    59098      ,         5

I want my table data in this json format. Its just an example...to show level in JSON

"Id":59098
          "pd":266
                  "Level":5
                  "price":1
"Id":59098
          "pd":247
                  "Level":5
                  "price":2

In this json there is two loops are going If am not wrong. I was able to do it for one loop in ETL..but couldnt do it for two loops.

Not getting values for reimbursementId and packageId

Have goggled alot but couldn't find any code to understand properly and approach for the same.

Code tried little bit

FileInputStream inp = new FileInputStream("D:/json.xlsx" );
Workbook workbook = WorkbookFactory.create( inp );

Sheet sheet = workbook.getSheetAt( 0 );

    JSONObject json = new JSONObject();

    JSONArray rows = new JSONArray();

but dont know what to next !!

can anyone tell me how to do this ?

Possible duplicate of [Excel to JSON javascript code?](http://stackoverflow.com/questions/28782074/excel-to-json-javascript-code) — Govind Samrow, Aug 11 '16 at 16:09
Just FYI this is not JSON. It looks more like YAML than JSON — Slai, Aug 12 '16 at 10:40
JSON is the output that you got with the `{`. The output that you want is **not** JSON. You can easily convert the JSON output string with few string replace function. Also, it seems like you are missing `java` tag or whatever programming language you are using. — Slai, Aug 12 '16 at 12:41

Clément Duveau · Answer 1 · 2016-08-12T15:40:35.063

0

I advice you to use a specific tool. This is not a whole new case.

Try Talend Open Studio. It is not so complicate to use if you want to convert a file (CSV, JSON, Database directly, etc) to another. Please see TalendForge for basics.

In your case, you can connect to your database, and send all data in JSON.

Edit:

Your representation is not following the same logic than JSON. Here how I see it (and this is probably wrong because I can't understand)

If you just want Excel to JSON without any changes:

{
    "rows":[
        {
            "Price":"1",
            "pd":"266",
            "Id":"59098",
            "Level":"5"
        },
        {
            "Price":"1",
            "pd":"266",
            "Id":"59098",
            "Level":"5"
        },
        //and again and again
        {
            "Price":"2",
            "pd":"247",
            "Id":"59098",
            "Level":"5"
        }
    ]
}

If you want to reorganize, then define what you want. Try to imagine a sample of your data in a Java Object using ArrayList, int, String and subclass or even better in a JavaScript Object.

For the last example it will give you:

public class myJson{
    ArrayList<myObject> rows;

with

public class myObject{
    String Price;
    String pd;
    String Id;
    String Level; //Or int, or date, or whatever

If you want to reorganize your data model, please give us this model.

edited Aug 12 '16 at 15:40

answered Aug 11 '16 at 16:26

Clément Duveau

399
3
14

I've tried with talend as well..but it supports only one level of json. My requirement is for two level of json – Kate Aug 11 '16 at 17:03
Can you provide, based on the example you give, an example of the JSON you want to generate ? Just to be sure of the feasbility, but it is also simpler for further readers. You can generate multi levels JSON in Talend, but this is more complicate because the datastream in Talend is one level. – Clément Duveau Aug 12 '16 at 10:04
As @Slai said this is not really a JSON, feel free to ask how JSON should be formatted for your case. – Clément Duveau Aug 12 '16 at 10:46
In this example you created 3 JSON. You are looking for JSON arrays IMOO. If you want to be sure of what is the most suitable JSON format for your case, try to imagine (and create) a representation of your data in Javascript (an Object containing attributes + an Object containing attributes + an array of object...). As you seems to know about Java, this is the same as creating a class using subclass and ArrayList of subclass. – Clément Duveau Aug 12 '16 at 11:01

score 0 · Answer 2 · answered Apr 28 '17 at 07:31

Converting Excel file data to Json format is a a bit complex process, it depends on the structure of data, we do not have exact online tool as such so far...

custom code is required, there are various technologies available to be used, but best suited should be VBA, because VBA fits within Excel and can generate Json file quickly and flexible to edit code compared to any other technology that requires to automate to excel and import data and then process.

We can find there are various websites provide code to generate json from excel data, here is one such site looks expertise in this area. http://www.xlvba.net/tools/excel-automation-to-convert-excel-data-to-json-format.html

Ragavendra

convert excel to json file

2 Answers2