In my recent post about JSON Table Editor, I listed reading CSV under feature to-do list. Working on it, I remember that I have blogged about reading CSV in the past.

So I ported the code from C# to JavaScript. The porting process is very much painless since C# and JavaScript has the same dialect. The JavaScript implementation is much simpler compared to C# because JavaScript is a dynamic language. You can compare the code side by side if you think I bluffed you :D.

Please understand that I wrote the C# implementation way back when I was only have Visual Studio 2005 in my workstation. So sadly, no anonymous object for me that time.

You could argue why complicated method is being used instead of just splitting the string using comma, but simple splitting won’t work when the CSV contains comma inside brackets.

Anyway, enjoy the code and have fun coding.

Cheers!

var CsvDataSource = {
   cleanQuotes : function (raw) {
	//Remove extra quotes/spaces in the beginning and end of string
	//Parse the string into Boolean or Number
      var str = raw;
      while (str.charAt(0) == '"' && str.charAt(str.length - 1) == '"')
         str = str.substr(1, str.length - 2);
		 
	  if (str.trim)
		str = str.trim();
		
      if (str == "true")
         return true;
      else if (str == "false")
         return false;
      else if (Number(str))
         return Number(str);
      else
         return str;
   },
   csvSplits : function (data, separatorChar) {
      //Split a string using the specified separator character. Will keep value between quotes
      //as a single value
      var me = CsvDataSource;
      var result = [];
      var lastpost = 0;
      var insideQuote = false;
      for (var i = 0; i < data.length; i++) {
         if (data.charAt(i) == separatorChar) {
            if (!insideQuote) {
               var str = me.cleanQuotes(data.substr(lastpost, i - lastpost));
               
               result.push(str);
               lastpost = i + 1;
            }
            
            if (i > 0 && i < data.length - 1) {
               if (data.charAt(i - 1) == '"' && insideQuote) {
                  var str = me.cleanQuotes(data.substr(lastpost, i - lastpost));
                  result.push(str);
                  lastpost = i + 1;
                  insideQuote = false;
               }
               if (data.charAt(i + 1) == '"' && !insideQuote)
                  insideQuote = true;
            }
         }
      }
      result.push(me.cleanQuotes(data.substr(lastpost)));
      return result;
   },
   readCSV : function (rawText, separatorChar) {
      var result = [];
      var lines = rawText.split('\n');
      for (var i = 0; i < lines.length; i++) {
         var line = lines[i];
         if (Ext.String.trim(line) != '') {
            var values = this.csvSplits(line, separatorChar);
            result.push(values);
         }
      }
      return result;
   }
}

Update 8th Feb 2013

• Removed the opening and closing quotes in CSV values
• Try to convert the String value into Boolean or Number, if failed, remains as a String

With so many JSON Editor tools available online, I still can’t find a decent editor with Grid interface.

So I decided to create my own. Considering my sloppy Sencha ExtJS knowledge, I conclude it should not be that difficult to implement with ExtJS. As a bonus, I can get my hand dirty with ExtJS 4.

Lo, and behold: JSON Table Editor.

Features (previously was To-Do List)

1. Ability to paste from HTML table/Excel
2. Auto save
3. Duplicates a row
4. Deletes rows
5. Loads remote JSON
6. Loads CSV
7. Load JSON nested object-array
8. Full support on Unicode characters

How to Use JSON Table Editor

1. Editing JSON/CSV
   1. Paste JSON/CSV text in the first textarea. Please note that for CSV, the first line will be used as column name.
   2. Click Load Model button. If failed, please fix the text to follow the correct JSON/CSV format. Note that you could change the default CSV separator character in the narrow textbox before the ‘Load JSON’ button.
   3. Add new record, or edit existing one in the Table Editor grid
   4. Click Output to JSON button to get the final JSON array object
2. Editing Remote JSON/CSV
   1. Type the URL of JSON/CSV in the textbox below the first text area.
   2. Click Load Model button. If failed, edit the JSON/CSV top text area, clear the URL, then click ‘Load JSON’ button. If you didn’t clear the URL textbox, it will always try retrieve the file and ignore your modified version.
3. Paste values from HTML/Excel
   1. Currently, the paste action is only available in Chrome, Safari and Opera. For now, only single column values is accepted.
   2. The values will be separated by newline character.
   3. Please ensure you select the appropriate Paste method before pasting.
   4. In Insert mode (Overwrite checkbox is unchecked), multiple copies of the selected row will be inserted below it. The number of copies will depend on the number of pasted values. These row copies will then have their corresponding column updated with the pasted value.
   5. In Overwrite mode , the values of the current cell and any cells below it will replaced. If the number of pasted values exceed the last row, Insert mode will be applied to the last row using the remaining pasted values
   6. To paste values, select the cell. Then press Ctrl+V
4. Editing complex JSON object
   Complex object here is referring to any other JavaScript type that is not Number, Boolean, String and Date. Imagine you have the following JSON that you want to edit (using John Bevilaqua‘s data)

   {
      'nodes' : {
         'grm155r61a105ke15d' : {
            'requests' : [{
                  'id' : '8985DEED-F7FA-4985-A709-2795BCB6A134',
                  'name' : 'GRM155R61A105KE15D',
                  'request' : {
                     'protocol' : 'HTTP',
                     'method' : {
                        'name' : 'GET',
                        'request-body' : false
                     },
                     'url' : 'www.findchips.com/ref/GRM155R61A105KE15D',
                     'body-type' : 'Text',
                     'headers-type' : 'Form',
                     'url-assist' : false
                  },
                  'modified' : 'Wed, 27 Feb 2013 13:57:59 -0500'
               }, {
                  'id' : '505AFB4F-9311-4261-B33B-8806B3325B6E',
                  'name' : 'GRM155R61A105KE15D oemstrade',
                  'request' : {
                     'protocol' : 'HTTP',
                     'method' : {
                        'name' : 'GET',
                        'request-body' : false
                     },
                     'url' : 'oemstrade.com/search/GRM31CR61E106KA12L/',
                     'body-type' : 'Text',
                     'headers-type' : 'Form',
                     'url-assist' : false
                  },
                  'modified' : 'Wed, 27 Feb 2013 14:52:04 -0500'
               }, {
                  'id' : 'D916947E-DA7C-4D73-8F67-91BF826E8A92',
                  'name' : 'GRM155R61A105KE15D EEM.COM',
                  'request' : {
                     'protocol' : 'HTTP',
                     'method' : {
                        'name' : 'GET',
                        'request-body' : false
                     },
                     'url' : 'www.eem.com/search/GRM155R61A105KE15D',
                     'body-type' : 'Text',
                     'headers-type' : 'Form',
                     'url-assist' : false
                  },
                  'modified' : 'Wed, 27 Feb 2013 14:58:35 -0500'
               }
            ]
         }
      },
      'version' : 1,
      'modified' : 'Wed, 27 Feb 2013 14:58:35 -0500'
   }
   

   1. Any complex object will shown as (Grid icon) in the table
   2. Clicking the Grid icon will open a new page where each object’s property will be used as column
   3. You can check at which level you are from the Window’s title. Any child window will have “child-” in its title.
   4. If you edit anything in a child window, the changes will be reflected to its parent only after you clicked the “Save to Upper Level” button.
   5. If you decided to close the window before saving, a dialog will ask for your confirmation.

Feature Request

1. Elijahu : Loading non-linear JSON (each record has different field). link
2. Will Baumbach: Should be able to fetch JSON stored in location which use self-signed certificate
3. Joe Garfield: Can’t import larger datasets. Unclear how to change data types of columns
4. Carlin Scott: Export to Excel
5. Konstantin Portnov: When handling unicode, the unicode characters shown as escape characters link

Do you have any other suggestions of functionality that would improve JSON Table Editor? Did you spot any bugs?

I previously created a post about this topic using YUI. But now that I’ve completed many projects using Ext.NET/Sencha ExtJS, I feel I can complete the same thing faster and simpler using ExtJS.

Compared to the YUI version, the ExtJS version is better because we can do filtering on each column. The code also much cleaner in ExtJS version.

I also updated the data. I removed codaset because they no longer online. I also added a new favourite of mine, BitBucket.

I hope it’s useful 🙂