4

Is there any tool available that can convert a json file to a xml or csv file which can be opened in either of OpenOffice or Excel.

I am working on a web application and using MongoDB as backend. Have exported the data as json from MongoDB and need to convert the data in some format which can be opened by some spreadsheet software. I need to give this data to client and also slice-and-dice and decorate it before handing it over. I am planning to implement a feature which will export the required data in .csv and .xls format, but need a quick dirty solution now.

I had to do this same thing some time back also and found something(a simple script) which saved my day. Can't locate the script again, any help would be very much appreciated.

UPDATE: Still looking for a solution and was trying to assemble a quick ruby script which can solve the problem. Anybody here knows which gem in rails adds to_xml to hash and array?

rubish
  • 10,887
  • 3
  • 43
  • 57

2 Answers2

1

I tried to convert the json to xml with following code in rails console:

arr = []
File.readlines("some_name.json").each {|l| arr << JSON.parse(l)}
File.open("some_name.xml", "w").write(arr.to_xml)

But the json had some keys with "$" in them, and Excel refused to open the xml file. So I tried again with this in the console:

class Array
  def sanitize_for_xml
    self.each{|entry| entry.sanitize_for_xml if entry.respond_to?(:sanitize_for_xml)}
    self
  end
end

class Hash
  def sanitize_for_xml
    self.each do |key, value|
      self[key] = value.sanitize_for_xml if value.respond_to?(:sanitize_for_xml)
    end

    self.keys.each do |key|
      self[key.gsub(/\W/, "")] = self.delete(key)
    end
    self
  end
end

arr = []
File.readlines("galgotias.2010-08-02.json").each {|l| arr << JSON.parse(l)}
arr.sanitize_for_xml
File.open("galgotias.2010-08-02.xml", "w").write(arr.to_xml)

And was able to open generated xml in Excel, although it was not a neat excel and I spent a lot of time slicing and dicing it.

rubish
  • 10,887
  • 3
  • 43
  • 57
  • i have an issue in Jquery-TokenInput.. Please check this link and give me a solution if u can..http://stackoverflow.com/questions/13558856/what-should-be-the-correct-response-from-web-service-to-display-the-jquery-token – Xavier Nov 26 '12 at 05:40
0

If your data is simple / flat, then Quests Toad for Cloud Databases (Free), or Jaspersofts iReports can be used to connect directly to your MongoDB instances / clusters and produce an extract. If you have multiple levels of nesting then your likely find the existing toys are lacking, as I did last year so hacked something together as a stop gap. This EE answer of mine is Perl based, but may give you a few ideas, as it will happily display a JSON file as a Dojo based Grid, or convert it to XLS or XLSX, and will cope with multiple levels of nesting.

Andrew Barber
  • 39,603
  • 20
  • 94
  • 123
arober11
  • 1,969
  • 18
  • 31
  • I had multiple sub-documents in a mongodb document. Some of them also arrays. Can you share the "Perl based" thing you mentioned?? – rubish Nov 27 '12 at 19:44
  • I'm sure the link was in the post (on the word "ideas", it's definitely in a previous version of the post), anyway have added the link back, on the word "following", but it's to the EE site, so it may suddenly disappear again. – arober11 Nov 28 '12 at 15:31
  • @arober11 You can view the [revision history](http://stackoverflow.com/posts/13540327/revisions) for this post and see that you are incorrect; this is the first edit on this post. That said, I am editing it now to be in line with the requirement to disclose your authorship of the link you have posted. – Andrew Barber Nov 28 '12 at 17:17
  • @Andrew Barber: The EE link was in the post inexplicably deleted by casperOne – arober11 Nov 29 '12 at 10:57
  • @arober11 **No, it was not**. I posted the revision history; Anyone can see it. You are incorrect. Your initial post did not include a link on the word "ideas". You are thinking of your deleted version of this post, which you posted on Nov 5, and *no one deleted the link there, either*; it was the *post* that was deleted. – Andrew Barber Nov 29 '12 at 11:03