betadata.rb

#~ Bharat Srinivasan
#~ 08/21/09
#~ Create SQL file from CSV data
 
#~ NOTE: Directories need changing in lines 9 & 16
 
 
#~ Creates a ".sql" file and writes all output to that file
File.open('c:/users/---/documents/beta_tester_database.sql', 'w') do |f|
  require "csv"
  # Just initialization of names, for database and table
  nDatabase = 'betadata'
  tablename = 'master_list'
  # raw is the filename for the google docs excel exported as a ".csv" file. Directory will need  changing for "filename"
  raw = 'master sheet final.csv'
  filename = 'C:/ruby/' + raw
  file_lines = CSV.read(filename )
 
  # Pops the first line of the csv out, because those are the column names
  fields = file_lines[0]
  file_lines.delete(file_lines[0])
 
  # From this point on is the code for the sql file, in sql syntax
  f.puts 'CREATE DATABASE ' + nDatabase + ';'
  f.puts 'use ' + nDatabase
  #Creates the table with the column names
  f.puts 'CREATE TABLE '+tablename+'('
  fields.each do |field|
    f.print '%s varchar(50)' % field
    f.puts ',' if fields.index(field) < fields.length-1
  end
  f.puts ');'
 
 
  #basic syntax to insert data, without the actual data. Data is inserted in the next loop
  insert_syntax = 'INSERT INTO ' + tablename + '('
  fields.each do|field|
    insert_syntax += field
    insert_syntax += ',' if fields.index(field) < fields.length-1
  end
  insert_syntax += ') VALUES('
 
  #~ f.puts insert_syntax
 
 
  # concatenates the data to the "insert_syntax" variable, and for row, a line of "insert_syntax + data" is outputed
  file_lines.each do |line|
    temp_insert = insert_syntax
    #~ f.puts '\n' + line.to_s + '\n'
    for i in 0...line.length
      temp_insert += '"' + line[i].to_s +  '"'
      temp_insert += ',' if i < line.length-1
      #~ temp_insert[temp_insert.length-1] = '  '
    end
    temp_insert += ');'
    f.puts temp_insert
  end
end