Text File to Excel Cells

[Guest]

I wish to import financial data from numerous text files into Excel
worksheets. The text files are fixed width, designed for printing one page
each. But the column formatting varies from file to file, and sometimes
varies within file (the top half will have 5 columns, the bottom half two). I
*think* the following is the general approach I need to use (and my questions
are contained in brackets):
1. Open text file and read start a loop to read in one line at a time.[I
think I know how to code this; pretty sure I want sequential input.]
2. Search for key words (e.g. Assets, Securities) that signify the desired
line [how do I say 'if line contains "Assets" then...'? The keyword may not
always be at the start of the line.]
3. When desired line is found, then use MID function to extract the desired
substring [I should be able to use specific position counts as long as the
file format doesn't change.] Convert that sub-string to a number [syntax??
for this step] [Will leading blanks in the sub-string cause a conversion
error? How would a string of blanks be converted, if at all?]
4. Place number in array and end loop. [How, if I need to, do I begin again
at line one of a particular file for a new keyword search?]
5. When all data is collected, write array to an excel column.

Final question: A total of about 240 files are involved. Is there a more
efficient method out there, or will this be fast enough (I'm prepared to wait
5 minutes, not 30, for this to chug along).

As you can tell from the questions, I'm a newbie. Any and all help
appreciated.

 

[PY & Associates]

Can you let us have a glimpse of the typical text file (you can edit it
prior to posting) and the resulting file please

 

[Guest]

Here are a few lines from one of the text files:

| Balance | | Credit | Repaid |
| | |
| Last | New | Line | and | Loans
| Charge | Ending |
| Quarter | Loans | Draws | Matured | Sold
| Offs | Balance |
| --------- | --------- | --------- | --------- |
--------- | --------- | --------- |
National Corporate | $ | $ | $ | $ | $
| $ | $ |
Credit Lines | 26.5 | 10.1 | -7.8 | .5 |
0 | 0 | 28.279 |
Term | 29.7 | 4.8 | 0 | 1.9 |
0 | 0 | 32.627 |
------------------ | --------- | --------- | --------- | --------- |
--------- | --------- | --------- |
Middle Market | | | | |
| | |
Credit Lines | 20.9 | 10.8 | -4.7 | 1.9 |
0 | 0 | 25.118 |
Term | 66.1 | 10.0 | 0 | 4.6 |
0 | 0 | 71.531 |
------------------ | --------- | --------- | --------- | --------- |
--------- | --------- | --------- |
Small Business | | | | |
| | |
Credit Lines | 27.9 | 9.4 | -2.2 | 4.9 |
0 | .0 | 30.209 |
Term | 58.2 | 6.3 | 0 | 4.3 |
0 | .1 | 59.970 |

If expanded to a full page it would be neatly formatted.

So, I might want to pull the 'new loans' amount for National Corporate
Credit Lines (among others). My sense of the solution is that first I would
have to detect the line that contained the unique phrase 'National
Corporate.' Then I would know from prior inspection and a faith that the page
layout won't change that the next line is 'Credit Lines'. I might then
extract from that line characters 32 through 41, which is the 'new loans'
column, using the MID function. Then I need to convert that to a number,
place it in an array, repeat numerous times, then paste the array into an
unremarkable column in an Excel spreadsheet. If I showed you another one of
these file/pages, the theme of the problem remains the same, just the search
technique might change.

Any help appreciated.

 

[MrScience]

I wish to import financial data from numerous text files into Excel
worksheets. The text files are fixed width, designed for printing one page
each. But the column formatting varies from file to file, and sometimes
varies within file (the top half will have 5 columns, the bottom half two). I
*think* the following is the general approach I need to use (and my questions
are contained in brackets):
1. Open text file and read start a loop to read in one line at a time.[I
think I know how to code this; pretty sure I want sequential input.]
2. Search for key words (e.g. Assets, Securities) that signify the desired
line [how do I say 'if line contains "Assets" then...'? The keyword may not
always be at the start of the line.]
3. When desired line is found, then use MID function to extract the desired
substring [I should be able to use specific position counts as long as the
file format doesn't change.] Convert that sub-string to a number [syntax??
for this step] [Will leading blanks in the sub-string cause a conversion
error? How would a string of blanks be converted, if at all?]
4. Place number in array and end loop. [How, if I need to, do I begin again
at line one of a particular file for a new keyword search?]
5. When all data is collected, write array to an excel column.

Final question: A total of about 240 files are involved. Is there a more
efficient method out there, or will this be fast enough (I'm prepared to wait
5 minutes, not 30, for this to chug along).

As you can tell from the questions, I'm a newbie. Any and all help
appreciated.

 

[MrScience]

I wish to import financial data from numerous text files into Excel
worksheets. The text files are fixed width, designed for printing one page
each. But the column formatting varies from file to file, and sometimes
varies within file (the top half will have 5 columns, the bottom half two). I
*think* the following is the general approach I need to use (and my questions
are contained in brackets):
1. Open text file and read start a loop to read in one line at a time.[I
think I know how to code this; pretty sure I want sequential input.]
2. Search for key words (e.g. Assets, Securities) that signify the desired
line [how do I say 'if line contains "Assets" then...'? The keyword may not
always be at the start of the line.]
3. When desired line is found, then use MID function to extract the desired
substring [I should be able to use specific position counts as long as the
file format doesn't change.] Convert that sub-string to a number [syntax??
for this step] [Will leading blanks in the sub-string cause a conversion
error? How would a string of blanks be converted, if at all?]
4. Place number in array and end loop. [How, if I need to, do I begin again
at line one of a particular file for a new keyword search?]
5. When all data is collected, write array to an excel column.

Final question: A total of about 240 files are involved. Is there a more
efficient method out there, or will this be fast enough (I'm prepared to wait
5 minutes, not 30, for this to chug along).

As you can tell from the questions, I'm a newbie. Any and all help
appreciated.

Hi John,

On question #1, I would use InStr(lineVariableName, "Securities") so it
would something like . . .
If InStr(myVar,"Securities") then
myVar2 = mid(stringName, x, y)
'with x and y representing the beginning and ending position you want
to 'capture
end if

On question #3, I agree that leaving any leading spaces in the string
would cause a conversion error. I would test for leading spaces and
then remove.

Some VBA code in Excel like this would work . . .

Sub checkForLeadingSpaces()
Dim myVar As Variant
Dim x As Integer
Dim myBool As Boolean
Dim SLen As Integer

Set myVar = Range("A1")
Do While Not IsEmpty(myVar)
Set nextVar = myVar.Offset(1, 0)

myBool = True
x = 1

Do Until myBool = False

If Mid(myVar, x, 1) = " " Then 'look for leading spaces
SLen = Len(myVar)
myVar = Right(myVar, SLen - 1)
Else

myBool = False
End If
Loop
MsgBox myVar

Set myVar = nextVar
Loop
End Sub

What are you using to import the text file into Excel? The code above
obviously applies only if you sucessfully import the data first. I'm
wondering it your writing an import routine or if you've tried to
simply open the text file in Excel. The .txt file appears to be pipe
delimited. Is this the only delimiter?

 

[Guest]

MS, That code you posted will help me a lot. Your question below confused. I
plan to write an Excel macro that will open a text file, read it line by line
until I find the string(s) that I want, then clean up and copy the values
into Excel.

It may be more efficient to import the text file into an Excel sheet, then
find and clean up the desired data from within Excel. However, the formats
and delimiters for these 36 files are not consistent. Opinions welcome.

Some of the pages are pipe delimited, others are columnar with no
delimiters, all designed for pretty printed output, none designed for data
import and analysis.

Thanks for your help; hope I've explained the project better.

 

[MrScience]

MS, That code you posted will help me a lot. Your question below confused. I
plan to write an Excel macro that will open a text file, read it line by line
until I find the string(s) that I want, then clean up and copy the values
into Excel.

It may be more efficient to import the text file into an Excel sheet, then
find and clean up the desired data from within Excel. However, the formats
and delimiters for these 36 files are not consistent. Opinions welcome.

Some of the pages are pipe delimited, others are columnar with no
delimiters, all designed for pretty printed output, none designed for data
import and analysis.

Thanks for your help; hope I've explained the project better.

Sorry for the confusion, John, when I asked what you were using to
import the text file into Excel I was wondering if you were using a VB6
program, or something other than VBA code (Macro) in Excel. I often
have to do this same type of work and I switch back and forth between
VB6 and VBA code written in the Excel workbook. I usually only use a
VB6 program if I need to constuct something that others are going to
use for a long time then it's worth spending the time to do it that
way.

Anyway, back to your situation at hand. I guess you're planning on
using different workbooks for each time of file. As I think about it,
though, I guess you could use a procedure to check to see which type(s)
of delimiters are present in the .txt file and then call subsequent
procedures accordingly.

If InStr(myVar,"|") then
'call procedure to handle pipe delimiters
End If

When I'm faced with the situation you have, I usually go ahead and open
the file in Excel just to get a good look at how Excel will parse it.
Lots of times, because of strange delimiters or something Excel doesn't
quite know what to do with, it will concatenate or parse incorrectly.
If this happens, I feel much more comfortable writing the VBA code
based on what I can see in each column.

You could also use the Split function if you want to write code to
handle the importing of the text file . . .

Sub ImportFile()

Dim myFile As String
Dim myString As String
Dim myStringPart() As String 'note this is an array
Dim fileLen as Long
Dim fileNum as Integer

myFile = "C:\myFoler\myFileName"

Open fileName For Input as filenum

fileLen = Len(myFile)
fileNum = FreeFile

Do While Not EOF(fileNum)
Line Input fileNum, myString 'get one string at a time

'now that we have one string, let's parse it

myStringPart = Split(myString, "|") 'substitute in any delimiter you
need

'code here to either place into another array to do further work or
copy
'to Excel

Loop

End Sub


John, I copied this from one of my VB6 programs so it may need some
revision to work in Excel.