#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    64
    Rep Power
    16

    Struggling With CSV


    I've been trying to figure this out for days and I really need help. I'm writing a ruby script that first converts an excel document to csv in order to make easier to work with. I need to delete the last column. There are no fixed number of columns and no standard header names. I also need to delete the second and third columns. I've tried messing around with require 'csv' and require 'faster_csv' and I just can't figure it out. Here is what I got:

    Code:
    require 'rubygems'
    require 'roo'
    require 'csv'
    require 'fileutils'
    
    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
    Please help me. Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jan 2004
    Location
    Constant Limbo
    Posts
    989
    Rep Power
    363
    I've only had a cursory look at your code but basically, any library that handles csv ends up storing it as an array (for each line parsed). Ruby provides mechanisms for removing the last element of any array - regardless of its size. For instance:
    Code:
    irb(main):001:0> [2,3,4,5,6,7].slice 0..-2
    => [2, 3, 4, 5, 6]
    As far as removing the 2nd and 3rd elements you can do something like:
    Code:
    irb(main):001:0> s = [2,3,4,5,6,7]
    => [2, 3, 4, 5, 6, 7]
    irb(main):002:0> [s[0]] + s[3..-1]
    => [2, 5, 6, 7]
    Both of these are without the proper range checks.
    To get the entire intended result you might do the following:
    Code:
    irb(main):001:0> s = [2,3,4,5,6,7]
    => [2, 3, 4, 5, 6, 7]
    irb(main):002:0> ([s[0]] + s[3..-1])[0..-2]
    => [2, 5, 6]
    That is probably not a good method to follow if you are worried about maintainability. In that case you would break each step into its constituent part and write it out in full.
    True happiness is not getting what you want, it's wanting what you've already got.

    My Blog
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    64
    Rep Power
    16
    Thanks a lot!

    Code:
    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

IMN logo majestic logo threadwatch logo seochat tools logo