From: Paul Shapiro <fightoplankton@...>
Date: 2009-06-22T00:54:47+09:00
Subject: if column header contain regexp, delete column

I need to see if a csv column header matches a pattern (see columns 2/3
below...S31 and S32) then delete the entire column. I've tried using:
/(S[0-9]+\s\b)/
but i don't think the regexp is the problem. My script is using bother
the rio and fastercsv gems. The fastercsv documentation has been very
difficult for me to figure out (I'm new to ruby). Thanks.

CSV:
Device ID,S31 Which best describes how you answered the online reading
comprehension quiz?,S32 Which best describes how you answered the online
timed retrieval quiz?,If you want your product to be easy to find in the
supermarket then you should make its container,"So that he can shift
attention between the radio and his incessantly talking girl friend when
she is in the car, Joe adjusts his radio",Early selection is most likely
to occur for,Early selection for a red target is most likely to occur
when there is,"In a lexical decision task, when the target is a bird
name, e.g. robin, it is usually preceded by the prime BODY but is
sometimes preceded by the prime BIRD.","In a lexical decision task, when
the target is a dog name, e.g. collie, it is usually preceded by the
prime CAR but is sometimes preceded by the prime DOG.","Suppose that
that you see a brief display with 12 colored letters: 4 red, 4 white,
and 4 blue.  At the offset of the display you hear tone.  A tone
instructs you to report only the letters of a particular color: high for
red, medium for white, and low for blue.  About how many letters do you
report?",Sperling (1960) found that partial report produced the highest
estimate of the number of available letters when the tone occurred
,"According to the logic of Sperling’s (1960) partial report method, an
observer who reports three letters from a row in a 4 x 4 display that
was cued at the display’s offset must have seen at least",Sperling
(1960) found that the greatest difference between full and partial
report in the number available of letters was when the tone occurred
____ milliseconds after the offset of the visual display
96A39,6,4,4 c,4 c,5 c,5 c,5 c,4 i,3 c,1 c,1 i,5 i
1E90A4,5,3,4 c,4 c,5 c,5 c,2 i,5 c,3 c,1 c,4 i,4 i
1F7EE1,5,3,4 c,4 c,5 c,5 c,5 c,5 c,3 c,1 c,4 i,1 c
B8D35,4,3,4 c,5 i,5 c,5 c,5 c,4 i,3 c,1 c,3 c,1 c
9867B,6,4,4 c,1 i,5 c,-,-,3 i,4 i,1 c,3 c,4 i
1F7EDF,5,3,4 c,4 c,5 c,5 c,5 c,4 i,4 i,1 c,1 i,3 i
1F7DEC,5,3,1 i,4 c,5 c,5 c,4 i,5 c,3 c,3 i,3 c,-
1F7EF7,5,3,4 c,4 c,5 c,5 c,5 c,5 c,3 c,1 c,3 c,1 c
95738,6,-,4 c,2 i,5 c,5 c,2 i,5 c,4 i,1 c,4 i,1 c
9C46C,5,3,4 c,4 c,5 c,5 c,5 c,4 i,3 c,1 c,3 c,1 c
BEFC2,5,-,4 c,4 c,5 c,5 c,5 c,4 i,3 c,1 c,2 i,1 c
1F082A,-,1,4 c,4 c,5 c,5 c,5 c,4 i,3 c,1 c,1 i,1 c
68CE4,-,3,4 c,4 c,5 c,5 c,2 i,3 i,3 c,1 c,3 c,2 i
1F7E05,-,3,4 c,2 i,5 c,5 c,5 c,-,3 c,1 c,5 i,3 i
2020B2,-,3,4 c,4 c,5 c,5 c,5 c,4 i,3 c,1 c,1 i,1 c
1F7EED,-,1,4 c,4 c,5 c,5 c,5 c,-,3 c,1 c,1 i,1 c
1F7F11,-,3,4 c,5 i,5 c,5 c,2 i,5 c,4 i,4 i,3 c,5 i
BB147,-,-,4 c,4 c,5 c,5 c,1 i,4 i,3 c,1 c,3 c,1 c

Current Script:
#!/usr/bin/env ruby

require 'rubygems'
require 'roo'
require 'csv'
require 'fileutils'
require 'rio'
require 'fastercsv'

FileUtils.mkdir_p "/Users/pshapiro/Desktop/Excel/xls"
FileUtils.mkdir_p "/Users/pshapiro/Desktop/Excel/tmp"
FileUtils.mkdir_p "/Users/pshapiro/Desktop/Excel/csv"

@filesxls = Dir["/Users/pshapiro/Desktop/Excel/*.xls"]
for file in @filesxls
  FileUtils.move(file,"/Users/pshapiro/Desktop/Excel/xls")
end

@filesxls = Dir["/Users/pshapiro/Desktop/Excel/xls/*.xls"]
@filetmp = Dir["/Users/pshapiro/Desktop/Excel/xls/*.xls_tmp"]

for file in @filesxls
  convert = Excel.new(file)
  convert.default_sheet = convert.sheets[0]
  convert.to_csv(file+"_tmp")
end

@filestmp = Dir["/Users/pshapiro/Desktop/Excel/xls/*.xls_tmp"]

for file in @filestmp
  FileUtils.move(file,"/Users/pshapiro/Desktop/Excel/tmp")
end

dir = "/Users/pshapiro/Desktop/Excel/tmp/"
files = Dir.entries(dir)
files.each do |f|
next if f == "." or f == ".."
oldFile = dir + "/" + f
newFile = dir + "/" + File.basename(f, '.*')
File.rename(oldFile, newFile)
end

files = Dir.entries(dir)
files.each do |f|
next if f == "." or f == ".."
oldFile = dir + "/" + f
newFile = dir + "/" + f + ".csv"
File.rename(oldFile, newFile)
end

@filescsv = Dir["/Users/pshapiro/Desktop/Excel/tmp/*.csv"]

for file in @filescsv
  FileUtils.move(file,"/Users/pshapiro/Desktop/Excel/csv")
end

FileUtils.rm_rf("/Users/pshapiro/Desktop/Excel/tmp")

@filescsv = Dir["/Users/pshapiro/Desktop/Excel/csv/*.csv"]

for file in @filescsv
  5.times {
  text=""
  File.open(file,"r"){|f|f.gets;text=f.read}
  File.open(file,"w+"){|f| f.write(text)}
  }
end

dir = "/Users/pshapiro/Desktop/Excel/csv/"
files = Dir.entries(dir)
files.each do |f|
next if f == "." or f == ".."
oldFile = dir + "/" + f
newFile = dir + "/" + File.basename(f, '.*') + ".tmp"
File.rename(oldFile, newFile)
end

@filescsv = Dir["/Users/pshapiro/Desktop/Excel/csv/*.tmp"]

for file in @filescsv
  csv = FasterCSV.read(file, :headers => true)
  lastc = csv.headers.length-1
#  puts lastc
  rio(file).csv.skipcolumns(1..2,lastc) > rio(file+".csv").csv(',')
end

@filescsv = Dir["/Users/pshapiro/Desktop/Excel/csv/*.tmp"]

for file in @filescsv
  FileUtils.remove(file)
end

dir = "/Users/pshapiro/Desktop/Excel/csv"
files = Dir.entries(dir)
files.each do |f|
next if f == "." or f == ".."
oldFile = dir + "/" + f
newFile = dir + "/" + File.basename(f, '.*')
File.rename(oldFile, newFile)
end

2.times {
files = Dir.entries(dir)
files.each do |f|
next if f == "." or f == ".."
oldFile = dir + "/" + f
newFile = dir + "/" + File.basename(f, '.*')
File.rename(oldFile, newFile)
end
}

files = Dir.entries(dir)
files.each do |f|
next if f == "." or f == ".."
oldFile = dir + "/" + f
newFile = dir + "/" + f + ".csv"
File.rename(oldFile, newFile)
end

#####################################

@filescsv = Dir["/Users/pshapiro/Desktop/Excel/csv/*.csv"]

for file in @filescsv
  csv = FasterCSV.read(file, :headers => true)
  csv = csv.to_s
  fields = FCSV.parse_line(csv)

  fields.each do |f|
    f.sub!(/[\d]+\)+[\s]/,'')
  end

#  puts fields

  wline = FCSV.generate_line(fields)
  astring = rio(file).contents
  rio(file).csv.print(astring).close

  text=""
  File.open(file,"r"){|f|f.gets;text=f.read}
  File.open(file,"w+"){|f| f.write(text)}

  astring = rio(file).contents
  rio(file).csv.print(wline+astring).close
end

puts "Successfully fixed Microsoft Excel documents!"
#puts teststr.gsub(/!(Device ID)|([BC\B0-9]+\.)\s/,'')

#puts "(Device ID)|[BC\B][0-9]+\."
#puts "(Device ID)|([BC\B0-9]+\.)"
#puts "!(Device ID)|([BC]\B[0-9]+\.\s)"

#####################################

@filescsv = Dir["/Users/pshapiro/Desktop/Excel/csv/*.csv"]

for file in @filescsv
  csv = FasterCSV.read(file, :headers => true)
  csv = csv.to_s
  fields = FCSV.parse_line(csv)

  fields.each do |f|
    f.sub!(/!(Device ID)|([BC\B0-9]+\.)[\s]*/,'')
  end

#  puts fields

  wline = FCSV.generate_line(fields)
  astring = rio(file).contents
  rio(file).csv.print(astring).close

  text=""
  File.open(file,"r"){|f|f.gets;text=f.read}
  File.open(file,"w+"){|f| f.write(text)}

  astring = rio(file).contents
  rio(file).csv.print(wline+astring).close
end
-- 
Posted via http://www.ruby-forum.com/.