Extracting Numerous Text files into Excel
 

Hi,
I have to extract 300+ text files (they are symmetric, same number of
columns) into an excel spreadsheet. Each file contains about 1800
lines. Will Excel support that many lines? What method do i use to
extract them. I don't want to open each file and do a copy all, then
paste into Excel. Please Help! Thanks

Extracting Numerous Text files into Excel
 

Hi, Wendy. At 300 files times 1800 lines each, you're going to need 540,000
rows, which is a bit more than Excel's 65,536 rows. I've heard others say
that Access or Quattro Pro can handle that, though I've not used either.

Ed

wrote in message
oups.com...
Hi,
I have to extract 300+ text files (they are symmetric, same number of
columns) into an excel spreadsheet. Each file contains about 1800
lines. Will Excel support that many lines? What method do i use to
extract them. I don't want to open each file and do a copy all, then
paste into Excel. Please Help! Thanks

Extracting Numerous Text files into Excel
 

Hence I'm now thinking of taking averages of every 30 lines to reduce
the files. I need help with that as well. I want to write a formula
that takes an average of every 30 lines in the same column. Thanks

Extracting Numerous Text files into Excel
 

Since you call these "text files", I'm assuming the data is all plain text
in columns separated by tabs or spaces? If so, is it possible to open one
of these with Word, select your data, use TableConvert Text to Table, and
come up with a nice consistent table?

My thinking is code that can iterate through all the text files in a folder
and open them in Word, convert each block of data into a table, copy that
table and paste it into an Excel file, average each column every 30 rows and
write the averages into your final Excel workbook. If you can't make it the
file into a pretty table, you might be able to scan through the text file
and grab strings from certain locations on each line to write into Excel.

That's my thinking because that's the kind of thing I deal with and how I
would approach it. There may well be an easier way using an Excel data
merge function that I'm not familiar with because I rarely use those. It
may be worth your time to see if you can open a blank workbook and use
DataImport External DataImport Data to get your data in Excel in a
format you can work with. If so, then it probably can all be done by
iterating through all the files and importing. And that's out of my
experience to this point.

Ed

wrote in message
oups.com...
Hence I'm now thinking of taking averages of every 30 lines to reduce
the files. I need help with that as well. I want to write a formula
that takes an average of every 30 lines in the same column. Thanks

Extracting Numerous Text files into Excel
 

Oh, I can extract these text files into Excel into nice tables. That's
not my main issue here, I would like to know how to write a small
script perhaps to extract the list of text files I have into Excel, so
I don't have to go into each text file manually to copy and paste into
Excel file.

Extracting Numerous Text files into Excel
 

We suggest :

rownr denote row number

rownr in excel at the beginning = 1;
loop open/append each file;
if rownr =1;
number column B with int(row() mod 30);
sort whole file by column B;
End if
if rownr1;
number B(int((row()-rownr+1) mod 30);
sort file by column B from rownr to last row;
End if
delete all rows below without value in cells B;
rownr= row number below last row;
next file;

wrote in message
ups.com...
Oh, I can extract these text files into Excel into nice tables. That's
not my main issue here, I would like to know how to write a small
script perhaps to extract the list of text files I have into Excel, so
I don't have to go into each text file manually to copy and paste into
Excel file.

Extracting Numerous Text files into Excel
 

How are the files delimited?
How many columns, and which columns should be averaged/imported?

You *could* try using the ADO text driver and running some SQL to summarize the data. That might work (depending on the format).
If you want to send some sample data you can contact me directly at <tim j williams at g mail dot com (no spaces and make the
obvious replacements...)

--
Tim Williams
Palo Alto, CA


wrote in message oups.com...
Hi,
I have to extract 300+ text files (they are symmetric, same number of
columns) into an excel spreadsheet. Each file contains about 1800
lines. Will Excel support that many lines? What method do i use to
extract them. I don't want to open each file and do a copy all, then
paste into Excel. Please Help! Thanks

Extracting Numerous Text files into Excel
 

Function GetAllTxtFiles(SrcFolder As String)
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim PathFolder As String


Dim from_file As Integer
Dim one_line As String

Dim ws As Worksheet
Dim ctr As Integer


Set objFS = CreateObject("Scripting.FileSystemObject")

If Right(SrcFolder, 1) < "\" Then
SrcFolder = SrcFolder & "\"
End If

Set objFolder = objFS.GetFolder(SrcFolder)
Set objFiles = objFolder.Files

For Each objF1 In objFiles
' Open the input file.
from_file = FreeFile
Open SrcFolder & objF1.Name For Input As from_file

Set ws = Worksheets.Add
ws.Name = Left(objF1.Name, Len(objF1.Name) - 4)
ws.Activate

ctr = 1

Do While Not EOF(from_file)
Line Input #from_file, one_line
ws.Cells(ctr, 1).Value = one_line
ctr = ctr + 1
Loop

' Close the files.
Close from_file

Next

Set objFiles = Nothing
Set objFolder = Nothing
End Function


I assume that all your text files are located in single folder