From: anon1m0us <anon1m0us@...>
Date: 2006-12-16T00:55:11+09:00
Subject: Importing from text file to Excel

Hi;
Below is my code to import from a text file to EXCEL. I am encountering
a few issues.
1) the import file is larger than 65,536 which is the max Excel can
have as an input. The file is broken in to two files. However, I would
like to create files based on the alphabit. For example, All last name
from A-J is in one file, and the file should be names A-J. The second
should be from J-W and thrid X-Z.

2) I need to bold and highlight the top line of each file and the top
line is the header. And all columns should be autofit.

3) I need to read EXACTLY the input file content. The input file looks
like the following: first line is header, second is content.

activityDateTime	lastName	firstName	activityTypeName	expenseCode	retail	proRate
2005-12-09 13:44:18.310	Abe	Lincoln	Login to
Website	NULL	2.00	0.842458335

4) I know the code at the end of the file is wrong...that is where I am
having most of my issues.

5) I do NOT want to use WIN32OLE as much as possible since it takes
FOREVER to do the import. Unless someone can show be how to do it
efficently.
My issue is the Excel does not read the first column, 2005-12-09
13:44:18.310,  correctly. Each column is seperated by a tab.




require 'win32ole'
require 'zip/zip'
require "fileutils"

myname=""
print "What is the Location and Name of the Zip File? "
FILENAME=gets.chomp
print "Is this the correct File name and Path? (Y/N):"
ans=gets.chomp.upcase!
while ans!="Y"
	print "What is the Location and Name of the Zip File? "
	FILENAME<<gets.chomp
	print "Is this the correct File name and Path? (Y/N):"
	ans=gets.chomp.upcase!
end

print "The Zip file you entered was: #{FILENAME}\n"

oldname=FILENAME
name=File.basename("#{FILENAME}",".zip")
newname="#{name}"+Time.now.strftime("-%m-%d-%y-%H-%M-%S") +".zip"
File.copy("#{FILENAME}","c:\\#{newname}")

Dir.mkdir("c:\\#{name}")

Dir.chdir("#{name}")

Zip::ZipFile.open("#{FILENAME}").each{ |file|
	f=file.extract
	myname=f.to_s

}

max_output_lines = 60000
input_file = myname

output_base =File.basename("#{myname}",".txt")
n = 1
ifile = File.open(input_file,"r")
header = ifile.gets

until(ifile.eof?)
   ofn = output_base + sprintf("-%03d",n) + ".xls"
   ofile = File.open(ofn,"w")
   ofile.write(header)
   line = 2
   until(ifile.eof? || line > max_output_lines)
      ofile.write(ifile.gets)
      line += 1
   end
   ofile.close
   n += 1
end
ifile.close


excel = WIN32OLE::new("excel.application")
book = excel.Workbooks.Open("c:\\Hello3\\Nov 2006-002.xls")
	excel.Worksheets("Bear Nov 2006	-001").Name="test"
excel.Worksheets("test").Range("A:A").NumberFormat = ("yyyy-mm-dd
hh:mm:ss")
book.Worksheets("test").Range("a1:g1").Font.Bold = 1
book.Worksheets("test").Range("a1:g1").Interior.ColorIndex = 6
		excel.Worksheets(1).Range("a1:g1")['Value'] =
["activityDateTime","lastName","firstName","activityTypeName","expenseCode","retail","proRate"]
		excel.Worksheets(1).Columns.AutoFit
		excel.Worksheets(1).Select
		excel.Worksheets(1).Rows("2:2").Select
		excel.ActiveWindow.FreezePanes =1

excel.DisplayAlerts = 0
#book.SaveAs(xlsname)
excel.DisplayAlerts = 1 
book.close()
excel.quit()