Extract Data from Text Files

[Little Penny]

I’m looking for a excel macro the will extract the first 8 characters
in the first line of a text file and put that value in cell A1.
Extract characters 9 thru 14 in the first line and put that value in
cell B1. Then go to the last line in the file and extract characters 9
thru 14 and put that value in cell C1. Last I would like to run this
formula in cell D1 =C1-B1+1.
Some times I could have as many as 150 different file in my directory
so I would like the macro to do this for every file in my directory.

Sample Data:


FLEX764D1000751
FLEX764D1000752
FLEX764D1000753
FLEX764D1000754
FLEX764D1000755
FLEX764D1000756
FLEX764D1000757
FLEX764D1000758

Extraction would reurn

A B C D
FLEX764D1 000751 000758 8


Thank for any help..

 

[Guest]

Little Penny


I came across your situation.

This problem sounds more suitable to an Access solution - i.e. mass
importation of files.I had a similiar proble where I had to devise a solution
to import hundreds of excel files (.csv format).

If you feel adventurous - look in the MS Access group and search for "mass
importation" or "sysAccountant".In it you will find a discussion with the
relevant VBA code - you don't necessrily have to have knowledge of DAO
programming (but it'll be an asset if you do.). A basic understanding of SQL
would be helpful also - if not simply copy the table generated into a excel
file.

I know my solution might be a "leap in the dark" - but hope it helps.


Regards

sysAccountant

 

[Little Penny]

Little Penny

I came across your situation.

This problem sounds more suitable to an Access solution - i.e. mass
importation of files.I had a similiar proble where I had to devise a solution
to import hundreds of excel files (.csv format).

If you feel adventurous - look in the MS Access group and search for "mass
importation" or "sysAccountant".In it you will find a discussion with the
relevant VBA code - you don't necessrily have to have knowledge of DAO
programming (but it'll be an asset if you do.). A basic understanding of SQL
would be helpful also - if not simply copy the table generated into a excel
file.

I know my solution might be a "leap in the dark" - but hope it helps.

Regards

sysAccountant








- Show quoted text -

It would be a hugh help if I could get this done in excel but thanks
for your input..

 

[Ron Rosenfeld]

I’m looking for a excel macro the will extract the first 8 characters
in the first line of a text file and put that value in cell A1.
Extract characters 9 thru 14 in the first line and put that value in
cell B1. Then go to the last line in the file and extract characters 9
thru 14 and put that value in cell C1. Last I would like to run this
formula in cell D1 =C1-B1+1.
Some times I could have as many as 150 different file in my directory
so I would like the macro to do this for every file in my directory.

Sample Data:


FLEX764D1000751
FLEX764D1000752
FLEX764D1000753
FLEX764D1000754
FLEX764D1000755
FLEX764D1000756
FLEX764D1000757
FLEX764D1000758

Extraction would reurn

A B C D
FLEX764D1 000751 000758 8


Thank for any help..

This area is not my forte but the following may get you started, especially as
I see you've had no responses.

One change I've made is that your results do not meet your specifications.
E.g. FLEX764D1 is 9 characters and not 8, and your next two results are
characters 10-15 and not 9-14.

The general method is
Open the folder
Get the file list
Test them somehow for valid data
Save the first line
Save the last line
Enter the data and the formula in the appropriate cells

I didn't know how you would want to test the files for valid data. In the
sample below, I just tested that the files was a .txt file, but you should be
able to come up with something more appropriate to your situation.

In any event, try this:

=========================================
Option Explicit

Sub GetData()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long

Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("C:\Documents and Settings\Ron\Desktop\")
Set fc = f.Files

i = 0

For Each fl In fc
If Right(fl.Path, 4) = ".txt" Then
fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)
Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1

Res.Offset(i, 0).Value = Left(FirstLine, 9)
Res.Offset(i, 1).Value = Mid(FirstLine, 10, 6)
Res.Offset(i, 1).NumberFormat = "000000"
Res.Offset(i, 2).Value = Mid(ln, 10, 6)
Res.Offset(i, 2).NumberFormat = "000000"
Res.Offset(i, 3).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
Res.Offset(i, 3).NumberFormat = "0"
i = i + 1
End If
Next fl

End Sub
====================================
--ron

 

[Little Penny]

Ron



Thank you very much your code work exactly the way I requested.
Unfortunately I made an error in judgment and simplified the data in
my text file assuming that since I only care about the first 14
characters in the first line (the first 8 and 9 -14) and in the last
line characters (9-14) that it would not matter that there is another
600 plus characters (including space) on each line ending with a
period.

As I tested the sample data I provided it worked to perfection. But
when I used the data as it's actually formatted in my text file it did
not work. My fault for not being exact! Is there a way I can provide
you with a actual text file the way it formatted. I will post on line
here.


This is one line in the text file


SAQ56S1V00000100020002000000000000TLSX 00000 00000
000000000 BBBBB R R R
U U P Q N N N
N N N N N
00000 000000 99999999999 .



























n Tue, 14 Aug 2007 11:47:58 -0400, Ron Rosenfeld

I’m looking for a excel macro the will extract the first 8 characters
in the first line of a text file and put that value in cell A1.
Extract characters 9 thru 14 in the first line and put that value in
cell B1. Then go to the last line in the file and extract characters 9
thru 14 and put that value in cell C1. Last I would like to run this
formula in cell D1 =C1-B1+1.
Some times I could have as many as 150 different file in my directory
so I would like the macro to do this for every file in my directory.

Sample Data:


FLEX764D1000751
FLEX764D1000752
FLEX764D1000753
FLEX764D1000754
FLEX764D1000755
FLEX764D1000756
FLEX764D1000757
FLEX764D1000758

Extraction would reurn

A B C D
FLEX764D1 000751 000758 8


Thank for any help..

This area is not my forte but the following may get you started, especially as
I see you've had no responses.

One change I've made is that your results do not meet your specifications.
E.g. FLEX764D1 is 9 characters and not 8, and your next two results are
characters 10-15 and not 9-14.

The general method is
Open the folder
Get the file list
Test them somehow for valid data
Save the first line
Save the last line
Enter the data and the formula in the appropriate cells

I didn't know how you would want to test the files for valid data. In the
sample below, I just tested that the files was a .txt file, but you should be
able to come up with something more appropriate to your situation.

In any event, try this:

=========================================
Option Explicit

Sub GetData()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long

Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("C:\Documents and Settings\Ron\Desktop\")
Set fc = f.Files

i = 0

For Each fl In fc
If Right(fl.Path, 4) = ".txt" Then
fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)
Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1

Res.Offset(i, 0).Value = Left(FirstLine, 9)
Res.Offset(i, 1).Value = Mid(FirstLine, 10, 6)
Res.Offset(i, 1).NumberFormat = "000000"
Res.Offset(i, 2).Value = Mid(ln, 10, 6)
Res.Offset(i, 2).NumberFormat = "000000"
Res.Offset(i, 3).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
Res.Offset(i, 3).NumberFormat = "0"
i = i + 1
End If
Next fl

End Sub
====================================
--ron

 

[Little Penny]

Ron I take my last post back. It works. The problem was i was using
CAPS on my txt file extention.

Thanks for all your help

 

[Ron Rosenfeld]

Ron I take my last post back. It works. The problem was i was using
CAPS on my txt file extention.

Thanks for all your help

I'm glad my suggestion was helpful. Thanks for the feedback.

By the way, please note that your example data and output should that you were
using the first 9 positions and not the first 8. And that is how I wrote the
macro.
--ron