-1

Possible Duplicate:
convert csv/xls to json

I have in my assets an excel sheet showing 2 language translation. i want to convert the excel sheet values in a json format. How do i do that?

Community
  • 1
  • 1
SKB
  • 137
  • 2
  • 11

1 Answers1

0

Below is a possible solution. I hope that it will help you. Let assume that you have following table:

           A              B                C                 D
------------------------------------------------------------------------------------------
1    Hello               Translat1
------------------------------------------------------------------------------------------
2    Bye                 Translat2   
------------------------------------------------------------------------------------------
3    World               Translat3
------------------------------------------------------------------------------------------
4    How are you         Translat4     
------------------------------------------------------------------------------------------

In C1 copy and paste the following formula:

=""""&"data"&""""&":["&"{"&""""&"english"&""""&":"&""""&A1&""""&"},{"&""""&"other"&""""&":"&""""&B1&""""&"}"&"]"

After this, copy/paste this formula through all cells of column C. The column C should look like:

                   C
----------------------------------------------------
"data":[{"english":"Hello"},{"other":"Translat1"}]
"data":[{"english":"Bye"},{"other":"Translat2"}]
"data":[{"english":"How are you"},{"other":"Translat3"}]
"data":[{"english":"World"},{"other":"Translat4"}]

In cell D2 copy/paste this formula:

=C1&","&C2

In D3 copy/paste this formula

=D2&","&C3

The last formula from D3 should be copied in coming cells. In our case the output should look like

"data":[{"english":"Hello"},{"other":"Translat1"}],"data":[{"english":"Bye"},{"other":"Translat2"}],"data":[{"english":"How are you"},{"other":"Translat3"}],"data":[{"english":"World"},{"other":"Translat4"}]

In order the last result to be valid json you have add { at the begging and } at the end. This is not proved solution for many records.

user738686
  • 147
  • 2
  • 12