Ubuntu Wiki

   1 #!/usr/bin/env ruby
   2 # == Synopsis
   3 #
   4 # xls2sql converts XLS files used as DB to SQL commands for import.
   5 #
   6 #  Assumptions:
   7 #  1. First row [1] contains field names
   8 #  2. Starting from row 2 foreward, one row per field.
   9 #
  10 #  Based on this source:
  11 #
  12 #   |    A    |    B    |    C    |
  13 #  -+---------+---------+---------|
  14 #  1| Field 1 | Field 2 | Field 3 |
  15 #  -+---------+---------+---------|
  16 #  2| foo     | bar     | ipsum   |
  17 #  -+---------+---------+---------|
  18 #  3| lorem   | etc     | etc     |
  19 #  -+---------+---------+---------|
  20 
  21 #  Build:
  22 #  Create Table SomeTable (
  23 #  `Field 1` varchar(255),
  24 #  `Field 2` varchar(255),
  25 #  `Field 3` varchar(255));
  26 #
  27 #  Insert Into Sometable 
  28 #  (`Field 1`,`Field 2`,`Field 3`)
  29 #  Values
  30 #  (foo,bar, ipsum),
  31 #  (lorem,etc,etc);
  32 # 
  33 #
  34 #  By Carlos Troncoso Phillips
  35 #  schmickcl@gmail.com
  36 #  https://wiki.ubuntu.com/CarlosTroncoso
  37 # == Usage
  38 # 
  39 # xls2sql -x <file> -s <file> [-t <table> -w <workbook>]
  40 #
  41 #  -h, --help:
  42 #        Shows this help
  43 #
  44 #      --synopsis:
  45 #        Short explanation of this program
  46 #
  47 #  -x, --xls:
  48 #        xls input file
  49 #
  50 #  -s, --sql:
  51 #        sql file to output
  52 #
  53 #  -t, --table:
  54 #        table name to create (Defaults to source filename)
  55 #        use '' with tables with spaces.
  56 #
  57 #      --size:
  58 #        Field size to create.
  59 #        xls2sql creates all fields as varchar(255),
  60 #        but 255 is the default value. Use other value by setting --size.
  61 #
  62 #  -w, --workbook:
  63 #        workbook name (A.K.A. Sheet) (Defaults to first workbook)
  64 #
  65 #  Note: if workbook has any linking, linked cells won't be parsed acurately.
  66 #        Copy and paste as value to fix sheet.
  67 #
  68 
  69 
  70 require 'getoptlong'
  71 require 'rdoc/usage'
  72 require 'rubygems'
  73 require 'spreadsheet' 
  74 
  75 opts = GetoptLong.new(
  76 [ '--help', '-h', GetoptLong::NO_ARGUMENT ],
  77 [ '--synopsis', GetoptLong::NO_ARGUMENT ],
  78 [ '--xls', '-x', GetoptLong::REQUIRED_ARGUMENT ],
  79 [ '--sql', '-s', GetoptLong::REQUIRED_ARGUMENT ],
  80 [ '--workbook', '-w', GetoptLong::OPTIONAL_ARGUMENT ],
  81 [ '--size', GetoptLong::OPTIONAL_ARGUMENT ],
  82 [ '--table','-t', GetoptLong::OPTIONAL_ARGUMENT ]
  83 
  84 )
  85 
  86 xls = nil
  87 sql = nil
  88 workbook = nil
  89 size = nil
  90 table = nil
  91 opts.each do |opt, arg|
  92   case opt
  93     when '--help'
  94       RDoc::usage('Usage')
  95     when '--synopsis'
  96       RDoc::usage('Synopsis')
  97     when '--xls'
  98       if arg == ''
  99         puts '--xls needs a file as parameter (i.e.: --xls file.xls)'
 100         puts 'program halted.'
 101         exit 1        
 102       else
 103         xls = arg
 104       end
 105     when '--sql'
 106       if arg == ''
 107         puts '--sql needs a file as parameter (i.e.: --sql file.sql)'
 108         puts 'program halted.'
 109         exit 1  
 110       else
 111         sql = arg
 112       end
 113     when '--table'
 114       if arg == ''
 115         puts '--table needs a file as parameter (i.e.: --table \'my table\')'
 116         puts 'program halted.'
 117         exit 1  
 118       else
 119         table = arg
 120       end
 121     when '--workbook'
 122       if arg == ''
 123         puts 'no workbook option, defaulting to first sheet'
 124         workbook = 0
 125       else
 126         workbook = arg
 127       end
 128     when '--size'
 129       if arg == ''
 130         puts 'no size option, defaulting to 255'
 131         size = 255
 132       else
 133         size = arg
 134       end
 135   end
 136 end
 137 if !xls
 138   puts "Mandatory option --xls not found"
 139   puts "program halted."
 140   exit 1
 141 end
 142 if !sql
 143   puts "Mandatory option --sql not found"
 144   puts "program halted."
 145   exit 1
 146 end
 147 if !workbook
 148   puts 'no workbook option, defaulting to first sheet'
 149   workbook = 0 
 150 end
 151 if !size
 152 puts 'no size option, defaulting to 255'
 153   size = 255
 154 end
 155 if !table
 156   puts "no table option, defaulting to #{xls[/^(.*)\./,1]}"
 157   table = xls[/^(.*)\./,1]
 158 end
 159 
 160 
 161 
 162 puts "Reading file #{xls}"
 163   if File.file?(xls)
 164     book = Spreadsheet.open(xls)
 165     puts 'File read.'
 166   else
 167     puts "File #{xls} doesn't exist."
 168     puts "program halted."
 169     exit 1
 170   end
 171 
 172 puts "Finding first sheet..."
 173   sheet=book.worksheet(workbook)
 174   puts "Using #{workbook} as default sheet..."
 175 
 176 puts "Finding last column with data..."
 177   firstrow=sheet.row(0)
 178   while firstrow[-1].nil?
 179     firstrow.pop
 180   end
 181   last_column=firstrow.length-1
 182   puts "Last column is #{last_column+1}"
 183 
 184 puts "Creating array for data..."
 185   sql_field_def=""
 186   fields=[]
 187 
 188 puts "Starting Loop..."
 189   0.upto(last_column) do |column|
 190 #    print "Working on column #{column+1}. "
 191     data = sheet[0,column]
 192 #    puts "Found #{data}"
 193     if data
 194       fields << data.strip
 195       sql_field_def << "`#{data.strip}` varchar(#{size}),\n"
 196     end
 197   end
 198 
 199 #Cleanup
 200 sql_field_def.chomp!(",\n")
 201 command=<<EOS
 202 Drop table if exists `#{table}`;
 203 Create Table if not exists `#{table}` (
 204 #{sql_field_def});
 205 
 206 EOS
 207 puts "Create loop finished..."
 208 
 209 puts "Starting Data Creation Section..."
 210   command << "Insert Into `#{table}`\n(#{fields.collect{|x| "`" + x + "`," }.to_s.chop})\nValues\n"
 211 
 212   insert_values=''
 213   last_row = sheet.row_count-1
 214   1.upto(last_row) do |row|
 215   if (row%100==0)
 216     print "\e[0KProcessing record #{row} of #{last_row}...   #{((row.to_f/last_row.to_f)*100).truncate}%\r"
 217     $stdout.flush 
 218   end
 219     if sheet[row,0]
 220       insert_values<<"("
 221       0.upto(last_column) do |column|  
 222         data = sheet[row,column]
 223         insert_values << "'#{data}',"
 224       end
 225       insert_values.chop!
 226       insert_values << "),\n"
 227     end
 228   end
 229   print "\e[0KProcessing record #{last_row} of #{last_row}...   100%\r"
 230   $stdout.flush 
 231   puts
 232   insert_values.chomp!(",\n")
 233   command << insert_values
 234 
 235 puts "Writing SQL script...\n"
 236 File.open(sql,'w') do |f|
 237   f.print command
 238 end
 239 
 240 puts "--Output End--\n"