0

I have a requirement to take multiple Excel spreadsheets or worksheets in tabs (XLS or CSV) and convert and combine them into a single JSON file.

I can easily convert one spreadsheet to JSON using a convertor tool, however each spreadsheet adds an new key/value to the planned schema so I’m not sure the best approach. If you read on below it should be clearer.

First I have a JSON schema that will hold the final data like so:

[
{
"FRANCE":{
    "2012":{
       "Paris":{
        am:{
          "lo": value,
          "hi": value,
          "avg": value
           },
        pm:{  
          "lo": value,
          "hi": value,
          "avg": value
          }
    },
      "Lyon":{
       am:{
          "lo": value,
          "hi": value,
          "avg": value
           },
       pm:{  
          "lo": value,
          "hi": value,
          "avg": value
          }
    }
    },
    "2013":{
       "Paris":{
        am:{
          "lo": value,
          "hi": value,
          "avg": value
           },
        pm:{  
           "lo": value,
          "hi": value,
          "avg": value

    etc………….for each year, city, period

Each spreadsheet/worksheet column holds the years and the rows hold the cities:

the spreadsheet that holds the “lo” data :

        2012    2013 
Paris   18.6    18.9
Lyon    20.2    21.6

…etc. ..same for the spreadsheet that holds the “hi” data :

        2012    2013
Paris   24.6    28.1
Lyon    30.2    35.2

…etc. ….and the same for the “avg” spreadsheet, etc…

So for the first spreadsheet (lo) I want to create the JSON file and schema with just the “lo” key and its value:

"Paris":{
       am:{
          "lo": value         
           },
       pm:{  
          "lo": value       
          }

this needs to add a key/value for “lo” in every year, city, am/pm in the spreadsheet.

then for the “hi” spreadsheet I want to append the key “hi” and it’s value for every year, city, am/pm:

"Paris":{
   am:{
      "lo": value,
      "hi": value       
       },
   pm:{  
      "lo": value,
      "hi": value       
      }

So I only want to add the "hi" key/value nothing else.

….likewise do the same for “avg” and any other spreadsheets. Hope that is clear.

Is there a tool available or a good method to do this, a C# method would be ideal.

Rod LeGybe
  • 1
  • Looks to me like your best bet is assembling this data yourself - if you have a library that parses CSV files in *any* form, then doing the data transformation yourself seems simple enough. – cloudfeet Dec 11 '13 at 13:53
  • possible duplicate of [How do I combine multiple rest queries using YQL?](http://stackoverflow.com/questions/4917144/how-do-i-combine-multiple-rest-queries-using-yql) – Paul Sweatte Jan 29 '14 at 19:06

0 Answers0