drop table

Below is my script which works - but need to know how to drop the table footy and recreate it again before uploading data.


INPUT_DATAFILE = "u:/footy/footy.txt"

 require 'win32ole'
 connection = WIN32OLE.new('ADODB.Connection')
 connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=u:\footy\footy.mdb')


File.open(INPUT_DATAFILE) do |f|
  while line = f.gets
    values = line.split("\t")   #  data is tab delimited
    sql = ("INSERT INTO footy VALUES ('#{values[0]}', '#{values[1]}','#{values[2]}');")
    puts sql
     connection.Execute(sql)
  end
end

Regards
philsivyerAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
That wasn't the full code, just pseudo to show the Access query..
INPUT_DATAFILE = "u:/footy/footy.txt"

 require 'win32ole'
 connection = WIN32OLE.new('ADODB.Connection')
 connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=u:\footy\footy.mdb')

    sql = ("DELETE * FROM footy")
    puts sql
     connection.Execute(sql)

File.open(INPUT_DATAFILE) do |f|
  while line = f.gets
    values = line.split("\t")   #  data is tab delimited
    sql = ("INSERT INTO footy VALUES ('#{values[0]}', '#{values[1]}','#{values[2]}');")
    puts sql
     connection.Execute(sql)
  end
end

Open in new window

0
 
philsivyerAuthor Commented:
Forgot to mention that - using microsoft access 2003
0
 
Shinesh PremrajanConnect With a Mentor Engineering ManagerCommented:

INPUT_DATAFILE = "u:/footy/footy.txt"

 require 'win32ole'
 connection = WIN32OLE.new('ADODB.Connection')
 connection.Open('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=u:\footy\footy.mdb')

sql = ("drop table footy;")
puts sql
connection.Execute(sql)

sql = ("create table footy(name varcahr(100));") // need to have the column names here
puts sql
connection.Execute(sql)

File.open(INPUT_DATAFILE) do |f|
  while line = f.gets
    values = line.split("\t")   #  data is tab delimited
    sql = ("INSERT INTO footy VALUES ('#{values[0]}', '#{values[1]}','#{values[2]}');")
    puts sql
     connection.Execute(sql)
  end
end


Hope this helps
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
Don't drop it if you are only going to recreate.
Empty it instead:

Before this statement
    sql = ("INSERT INTO footy VALUES ('#{values[0]}', '#{values[1]}','#{values[2]}');")
Run this statement
    sql = ("DELETE * FROM footy")
0
 
philsivyerAuthor Commented:
When I use
sql = ("DELETE * FROM footy")
It does not complete the cycle and seems to hang - I am running this from ScITE.
0
 
philsivyerAuthor Commented:
Thats great - any good sites/books for ruby and windows (excel access etc)
0
 
JESiiConnect With a Mentor Commented:
Check out: http://rubyonwindows.blogspot.com/2007/03/automating-excel-with-ruby.html which gives some information on automating excel. There's also a reference to another library in the comments to the post.

I'm starting on a ruby project which uses Excel files as intermediaries -- they're already using a couple of gems and I'll try to get that info and post it here as soon as I learn more.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.