[Excel] Converting csv to JSON

Published on 14 mai 2014

For those Excel lovers out there, a simple snippet to convert Excel data to JSON.

  1. Go to the Developper tab and click on Visual Basic editor
  2. Double click on ‘Module 1’
  3. Past the code below:
Function ConvertToJSON(ByRef headers As Range, ByRef content As Range) As String
'init var
Dim res As String
initStr = "{"
res = "["
maxJ = content.Rows.Count
maxI = content.Columns.Count
'Main loop
For j = 1 To maxJ
res = res & initStr
    For i = 1 To maxI
          key_ = Chr(34) & headers(i) & Chr(34) & ": "
          value_ = Chr(34) & content(i)(j) & Chr(34) & ", "
          res = res & key_ & value_
    Next i
    'close json object
    res = Left(res, Len(res) - 2) + "},"
Next j
'close array
res = Left(res, Len(res) - 1) & "]"
ConvertToJSON = res
End Function
  1. You should now be able to call your new custom function like so:excel_json_converter
  2. Result is validated by JSONLint:jsonlint_valide
comments powered by Disqus