User Tools

Site Tools


Address book converter, xml to csv

Target: convert company address book (more 900 records) for format of new VoIP PBX.

ab-xml2csv.rb
#!/usr/bin/env ruby
#
# $Id$
#
require 'nokogiri'
require 'json'
require 'pp'
 
dir = "orig0"
 
def stripNumber(str)
    str.sub!(/^[09],,/,'')
    str.sub!(/^0/,'')
    str.sub!(/^8/, '7')
    str.gsub!(/[+-. ()]/, '')
    str.gsub!(/^([0-9]{6})$/, '74012\1')
 
    #9520587264
    #1234567890
    str.gsub!(/^9([0-9]{9})$/, '79\1')
 
    # mark strange number
    #79520587264
    #12345678901
    str.gsub!(/([0-9]{1-10})$/, '0000\1')
    str
end
 
 
array = Array.new
keys = Array.new
begin
    Dir.open(dir).each do |name|
        next unless name.match('.contact$')
        fullname = dir + '/' + name
        next if File.size(fullname) == 0
        hash = Hash.new
 
        doc = Nokogiri::XML(File.open(fullname))
        begin
            doc.xpath('/c:contact/c:NameCollection/c:Name').each do |the|
                label = the.xpath('c:LabelCollection').text.strip
                label = 'undef' if label.nil? || label == ''
                hash["FormattedName"] = the.xpath('c:FormattedName').text.strip
                hash["FamilyName"] = the.xpath('c:FamilyName').text.strip
                hash["GivenName"] = the.xpath('c:GivenName').text.strip
                hash["MiddleName"] = the.xpath('c:MiddleName').text.strip
            end
 
            doc.xpath('/c:contact/c:PhoneNumberCollection/c:PhoneNumber').each do |the|
                label = String.new
                the.xpath('c:LabelCollection').each do |l|
                    l.xpath('c:Label').each do |t|
                        type = t.text.strip
                        next if type.match('Voice')
                        label = label + t.text.strip
                    end
                end
                hash["Number:#{label}"] = stripNumber(the.xpath('c:Number').text.strip)
            end
 
            doc.xpath('/c:contact//c:Position').each do |the|
                label = the.xpath('c:LabelCollection').text.strip
                hash["Department:#{label}"] = the.xpath('c:Department').text.strip
                hash["JobTitle:#{label}"] = the.xpath('c:JobTitle').text.strip
                hash["Company:#{label}"] = the.xpath('c:Company').text.strip
            end
 
            doc.xpath('/c:contact//c:PhysicalAddress').each do |the|
                label = the.xpath('c:LabelCollection').text.strip
                hash["Locality:#{label}"] = the.xpath('c:Locality').text.strip
                hash["Street:#{label}"] = the.xpath('c:Street').text.strip
            end
        array.push hash
        hash.keys.each do |key|
            keys.push key
        end
        keys.sort!
        keys.uniq!
        rescue Exception => e
            puts "error #{$!.inspect}"
            puts e.backtrace.join("\n")
        end
    end
rescue Exception => e
    puts "error #{$!.inspect}"
    puts e.backtrace.join("\n")
end
 
 
out = File.open('addrbook.csv', 'w')
 
#keys.reverse!
 
headline = String.new
keys.each do |key|
        headline = headline + ",#{key}"
end
headline.sub!(/^,/,'')
out.puts(headline)
 
array.each do |cont|
    line = String.new
    keys.each do |key|
        a = cont[key].to_s.gsub('"','\'')
        line = line + ",\"#{a}\""
    end
    line.sub!(/^,/,'')
#    puts line
    out.puts(line)
end
out.close
 
#EOF

Back to overviewNext PageLast Page