Posted By

bsrinivasan92 on 01/31/12


Tagged


Versions (?)

betadata.rb


 / Published in: Ruby
 

ruby file

  1. #~ Bharat Srinivasan
  2. #~ 08/21/09
  3. #~ Create SQL file from CSV data
  4.  
  5. #~ NOTE: Directories need changing in lines 9 & 16
  6.  
  7.  
  8. #~ Creates a ".sql" file and writes all output to that file
  9. File.open('c:/users/---/documents/beta_tester_database.sql', 'w') do |f|
  10. require "csv"
  11. # Just initialization of names, for database and table
  12. nDatabase = 'betadata'
  13. tablename = 'master_list'
  14. # raw is the filename for the google docs excel exported as a ".csv" file. Directory will need changing for "filename"
  15. raw = 'master sheet final.csv'
  16. filename = 'C:/ruby/' + raw
  17. file_lines = CSV.read(filename )
  18.  
  19. # Pops the first line of the csv out, because those are the column names
  20. fields = file_lines[0]
  21. file_lines.delete(file_lines[0])
  22.  
  23. # From this point on is the code for the sql file, in sql syntax
  24. f.puts 'CREATE DATABASE ' + nDatabase + ';'
  25. f.puts 'use ' + nDatabase
  26. #Creates the table with the column names
  27. f.puts 'CREATE TABLE '+tablename+'('
  28. fields.each do |field|
  29. f.print '%s varchar(50)' % field
  30. f.puts ',' if fields.index(field) < fields.length-1
  31. end
  32. f.puts ');'
  33.  
  34.  
  35. #basic syntax to insert data, without the actual data. Data is inserted in the next loop
  36. insert_syntax = 'INSERT INTO ' + tablename + '('
  37. fields.each do|field|
  38. insert_syntax += field
  39. insert_syntax += ',' if fields.index(field) < fields.length-1
  40. end
  41. insert_syntax += ') VALUES('
  42.  
  43. #~ f.puts insert_syntax
  44.  
  45.  
  46. # concatenates the data to the "insert_syntax" variable, and for row, a line of "insert_syntax + data" is outputed
  47. file_lines.each do |line|
  48. temp_insert = insert_syntax
  49. #~ f.puts '\n' + line.to_s + '\n'
  50. for i in 0...line.length
  51. temp_insert += '"' + line[i].to_s + '"'
  52. temp_insert += ',' if i < line.length-1
  53. #~ temp_insert[temp_insert.length-1] = ' '
  54. end
  55. temp_insert += ');'
  56. f.puts temp_insert
  57. end
  58. end

Report this snippet  

You need to login to post a comment.