CODEWITHSUNDEEP
xmljsonmongodb
rubish
rubish

Reputation: 10907

Convert json file to xml or csv, to be opened in OppenOffice/MS Excel

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?

Upvotes: 4

Views: 7005

Answers (2)

arober11
arober11

Reputation: 2019

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.

Upvotes: 0

rubish
rubish

Reputation: 10907

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.

Upvotes: 1

Related Questions