PDA

View Full Version : [SOLVED] Parse Text And Read Into Cells



aerodoc
03-11-2014, 10:38 PM
I would like to search through the contents of a text file for desired values and then read those directly in. My current approach is not very good. It involves reading in the text file comma delimited and then using VBA to get the values. It works OK, but it leaves a lot of "mess" with an extra sheet and has some other undesirable effects. Rather, I just want to read everything into memory and only collect the needed data.

Text file looks something like:
----------------------------
5,5,5
Hello
5,5,5
1,2.0
2,2.1
3,4.5
-1,0
-----------------------------

The goal is to collect the values 2.0, 2.1, and 4.5 and read them directly into cells.

You have to first locate "Hello" and then the needed row will start 2 rows below that. The last needed row will be the one before the -1,0 row. There are some other aspects that would improve the results, but if I could this working, it would be a really good start.

Thanks for any help :)

GTO
03-11-2014, 11:28 PM
Hi there,

What code are you using now?

Mark

aerodoc
03-11-2014, 11:49 PM
Well, it has a lot comments and messy so I just extracted the most important parts, but something like this 3 step process

Step1 - Read Text file to Sheet
-------------------------------
Set myTextFile = Workbooks.Open("C:\file.txt")

myTextFile.Sheets(1).Range("A1").CurrentRegion.Copy _
ReadIn.Range("A1")
myTextFile.Close (False)

Step 2 - Convert to cells via comma delimiter
--------------------------------
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True

Step 3 - Extract Data
----------------------------------------

'Find the start of the row of interest
j = 1
Do Until Cells(j, 1) = ""
If Cells(j, 1) = "Hello" Then
rowstart = j + 2
Cells(1, 12) = rowstart
GoTo 10
End If
j = j + 1
Loop

10

'Find the end of the row of interest
Do Until Cells(j, 1) = ""
If Cells(j, 1) = "-1" Then
rowend = j - 1
Cells(2, 12) = rowend
GoTo 20
End If
j = j + 1
Loop

20

'Copy appropriate rows to new location
i = rowstart
'k is the the first row for the copied location
k = 3
'p is the column for the copied location
p = 15
'

Do Until i = rowend + 1
Cells(k, p) = Cells(i, 2)
k = k + 1
i = i + 1
Loop

lecxe
03-12-2014, 04:43 AM
Hi

For a file with the format you posted try:



Sub ExtractNumbers()
Dim sPathName As String, sText As String
Dim iFile As Integer
Dim vArray As Variant

sPathName = "c:\tmp\file.txt"

' Read the file into a string
iFile = FreeFile
Open sPathName For Input As #iFile
sText = Replace(Input$(LOF(iFile), #iFile), vbCrLf, vbLf)
Close #iFile

' Get text from the second line after "Hello" till the line before -1
sText = Split(Mid(sText, InStr(sText, "Hello")), vbLf, 3)(2) ' Text starting the second line after "Hello"
sText = Left(sText, InStr(sText, "-1") - 2) ' text to parse

' build the array with the values in the second column
vArray = Filter(Split(Replace(sText, ",", "," & vbLf), vbLf), ",", False)

' write the array to a range
Range("A2").Resize(UBound(vArray) + 1).Value = Application.Transpose(vArray)

End Sub

aerodoc
03-12-2014, 05:33 AM
Thanks! That could work, but I have to disect it some. It is beyond my current capability. But it doesn't quite work for my case because I did not give you a good enough example. Could you possibly recode it for a real scenario:

-------------
Hello
0.000000E+00, 3.332333E-01, 3.332333E-01,
10002, 10003, 10004, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
1, 11, 1, 7,
1, 1, 1
1, 0.000000E+00,
2, 4.823339E-03,
3, 1.864667E-02,
4, 4.047001E-02,
5, 6.929334E-02,
6, 1.041167E-01,
7, 1.439400E-01,
8, 1.877633E-01,
9, 2.345867E-01,
10, 2.834100E-01,
11, 3.332333E-01,
-1, 0. ,
1, 10002, 1,
-----------------------------

On the 6th line below "Hello", the data that I want starts --- 1, 0.000000E+00, ----
So the read in data should read:

0.000000E+00
4.823339E-03
...
3.332333E-01

- There is one caveat to this. I want to have the option to replace a 0 value with a "" (blank). In this case (if I want to use that option) the first read in cells would be blank and the second read in cell would be 4.823339E-03

lecxe
03-12-2014, 05:46 AM
Hi aerodoc

This is a file with a different format from the one you posted

1 -

In your first post you wrote


You have to first locate "Hello" and then the needed row will start 2 rows below that. The last needed row will be the one before the -1,0 row.

Now you write


On the 6th line below "Hello", the data that I want starts

Does this mean that for each file the data will start in a different line after the line with "Hello"?
If that's the case how do we know which one it is?

2 -

In the first example you posted, each line with the values you want had no comma at the end of the line



...
1,2.0
2,2.1
3,4.5
-1,0
...


Now it's like



...
1, 0.000000E+00,
2, 4.823339E-03,
3, 1.864667E-02,
4, 4.047001E-02,
...


It has now a comma at the end of each line.

Which is correct?

aerodoc
03-12-2014, 07:14 AM
The most recent is the correct version. I was trying to scale it down to a simple example, but missed some important details. I figured I would be able to mod as needed, but the code is beyond my ability to understand right now.

It will always start at the 6th line and will always have a comma at the end of the line.

lecxe
03-12-2014, 07:43 AM
In that case a small tweak in the code should do it.

This worked for me with the new format you posted:



Sub ExtractNumbers()
Dim sPathName As String, sText As String
Dim iFile As Integer
Dim vArray As Variant

sPathName = "c:\tmp\file.txt"

' Read the file into a string
iFile = FreeFile
Open sPathName For Input As #iFile
sText = Replace(Input$(LOF(iFile), #iFile), vbCrLf, vbLf)
Close #iFile

' Get text from the sixth line after "Hello" till the line before -1
sText = Split(Mid(sText, InStr(sText, "Hello")), vbLf, 7)(6)
sText = vbLf & Left(sText, InStr(sText, "-1") - 3) ' text to parse

' build the array with the values in the second column
vArray = Filter(Split(sText, ","), vbLf, False)

' write the array to a range
Range("A2").Resize(UBound(vArray) + 1).Value = Application.Transpose(vArray)

End Sub

aerodoc
03-12-2014, 09:12 AM
Great! This is very close. But there appears to be an issue. It seems to be tripping on the "-1" aspect.

It needs to stop if only a value in the first column is -1 (and exactly -1). As is, the sample below stops prematurely. I think
"-1.844687E-03" is tripping it up.

--------------------------

Hello
-3.314343E-02, 0.000000E+00, 3.314343E-02,
0, 10003, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
11, 1, 1, 7,
1, 1, 1
1, 0.000000E+00,
2, -4.773439E-04,
3, -1.844687E-03,
4, -4.007031E-03,
5, -6.869374E-03,
6, -1.033172E-02,
7, -1.429406E-02,
8, -1.865640E-02,
9, -2.331875E-02,
10, -2.818109E-02,
11, -3.314343E-02,
-1, 0. ,
1, 10004, 1,

lecxe
03-12-2014, 10:23 AM
You are right. It is looking for the first -1.

In this case if it always ends with the line "-1, 0. ,", that's what we'll look for.

Replace the search for the -1 with


sText = vbLf & Left(sText, InStr(sText, "-1, 0. ,") - 3) ' text to parse

or use any other string that you see that will determine the line after the end of the data

aerodoc
03-12-2014, 01:14 PM
Works great! Thanks :)

lecxe
03-13-2014, 03:53 AM
You're welcome. Thanks for the feedback.

snb
03-13-2014, 08:07 AM
or ?


Sub M_snb()
Open "c:\tmp\file.txt" For Input As #1
sn=split(split(join(filter(split(Input(LOF(1), 1), vbCrLf),""Hello",false),vblf),"-1, 0. ,")(0),vblf)
Close

Range("A2").Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub