# Importing from text file to Excel

**URL:** <https://www.ruby-forum.com/t/importing-from-text-file-to-excel/79487>\
**Category:** Ruby\
**Created:** [December 15, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/importing-from-text-file-to-excel/79487 "2006-12-15T16:57:16Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![anon1m0us](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/d07c76/32.png) [@anon1m0us](https://www.ruby-forum.com/u/anon1m0us)\
**Post date:** [December 15, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/importing-from-text-file-to-excel/79487/1 "2006-12-15T16:57:16Z")

</div>

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

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

2. 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()

---

<div class="post-metadata">

**Author:** ![anon1m0us](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/d07c76/32.png) [@anon1m0us](https://www.ruby-forum.com/u/anon1m0us)\
**Post date:** [December 15, 2006, 5:41pm UTC](https://www.ruby-forum.com/t/importing-from-text-file-to-excel/79487/2 "2006-12-15T17:41:27Z")

</div>

— anon1m0us [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 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.

Use regular expression to get your data into (an)  
array(s), sort them, then import into excel

Li