PDA

View Full Version : Importing a Portion of a .txt file by pattern - Please Help



Jeremy42
05-28-2015, 12:20 PM
I am having trouble setting up the pattern recognition part of the macro for importing part of a .txt file. The part of the file I want imported looks like

There is more to the file above this with varying row #'s - meaning no fixed rows #'s or pattern

Component,Heading1,Heading2,Heading 3, Heading 4,Heading5
1, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
2, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
3, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
4, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
5, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
6, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
7, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11
8, 0.100E+11, 0.000E+00, 0.100E+11, 0.100E+11

There is more to the file below this with varying row #'s - meaning no fixed row #'s or pattern

What I need is to be able to do is essentially copy the part that starts with Component and then include all the lines below it up to a textbox defined value +1 eg:
D = Val(SimulationsTextBox.Text) +1 (the simulations text box will always be different so I need to have this variable as part of the pattern recognition and this is already defined elsewhere)
and in the above example the simulations text box value is 8 so it copies 9 lines total starting with Component

Unfortunately this value will always change and I cannot use the standard import file, plus the file is very large so I cannot copy all the file starting with Component then delete the rows I do not need- I just need those 9 lines in this example.

I already know how to open the file and/or read it. I am just looking for the code for the pattern that lets me highlight the desired section and import them into cell A1 of sheet 2 in the already open excel file.

Please describe what each line of code does because I am a beginner at VB and Excel Macro coding.

Once that selection is imported into excel I would like to split it up into separate cells using comma delineated method.

Due to large file size, listing the fastest way for VB to execute this would be greatly appreciated.

Thanks,
Jeremy

Kenneth Hobs
05-28-2015, 08:03 PM
Welcome to the forum!

I looked at this a bit and hope to post a solution by tomorrow if no one helps by then. These things generally garner more help if you can attach short example file(s) though your example should be fine for most that could help.

SamT
05-28-2015, 09:26 PM
MsgBox is substituting for the actions you need to take

Sub SamT()
Const Found As String = "Component"
Dim HeaderLine As String
Dim i As Long
Dim TextStream As Variant
Dim TxtFile As Variant
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")


Set TxtFile = FSO.GetFile("Your file Path and Name goes here")
Set TextStream = TxtFile.OpenAsTextStream(OpenFileForReading)
Do
HeaderLine = TextStream.Readline
If Not Left(HeaderLine, 9) = Found Then HeaderLine = ""
Loop Until HeaderLine <> ""

MsgBox HeaderLine
For i = 1 To CLng(SimulationsTextBox.Text)
MsgBox TextStream.Readline
Next i


'Play nice
TextStream.Close
Set TxtFile = Nothing
Set FSO = Nothing
End Sub

snb
05-29-2015, 04:02 AM
I think this is sufficient:


Sub M_snb()
c00=split(split(CreateObject("Scripting.FileSystemObject").opentextfile("G:\OF\example.txt").readall,"Component")(1),vbcrlf & inputbox("number") & ",")(0)
End Sub

Kenneth Hobs
05-29-2015, 07:47 AM
Good work guys.

Jeremy, you will need to tweak a few things to fit your needs but these accomplish your first goal.

Importing the result by the delimited comma should accomplish the second goal.

I would use snb's code like this:

Sub M_snb()
c00 = Split(Split(CreateObject("Scripting.FileSystemObject").opentextfile("x:\FileReadWrite\csv\ImportAtStringPlusLines.txt").readall, "Component")(1), vbCrLf & InputBox("number") & ",")(0)
MsgBox "Component" & c00
End Sub




Here is how I would use Sam's code:


Sub SamT()
Const Found As String = "Component"
Dim HeaderLine As String
Dim i As Long
Dim TextStream As Variant
Dim TxtFile As Variant
Dim FSO As Object
'https://msdn.microsoft.com/en-us/subscriptions/ebkhfaaz(v=vs.84).aspx
Const OpenFileForReading = 1

Set FSO = CreateObject("Scripting.FileSystemObject")

Set TxtFile = FSO.GetFile("x:\FileReadWrite\csv\ImportAtStringPlusLines.txt")
Set TextStream = TxtFile.OpenAsTextStream(OpenFileForReading)
Do
HeaderLine = TextStream.Readline
If Not Left(HeaderLine, 9) = Found Then HeaderLine = ""
Loop Until HeaderLine <> ""

MsgBox HeaderLine
'For i = 1 To CLng(SimulationsTextBox.Text)
For i = 1 To 8
MsgBox TextStream.Readline
Next i


'Play nice
TextStream.Close
Set TxtFile = Nothing
Set FSO = Nothing
End Sub

Jeremy42
05-29-2015, 10:29 AM
Thanks for the help. I am still having problems however. When i use the following code:
D = Val(SimulationsTextBox.Text)
F = Split(Split(CreateObject("Scripting.FileSystemObject").opentextfile(ActiveWorkbook.Path & "\" & "temp.input.out").readall, "Component")(1), vbCrLf & D & ",")(0)
MsgBox "Component" & F

The message box display does not stop after 9 lines beginning with Component, it displays the rest of the file which is rather large. (SimulationsTextBox.Text is defined as 8 earlier but this changes every run so this must be a changeable variable.) I only need the line starting with Component and ending x number lines after where x is defined in the simulationstextbox. I cannot copy the whole file starting with Component because of the size.

Also if I use this method how do i then import the result into Cell1 of the "output summary" sheet. When I modified the code to:
D = Val(SimulationsTextBox.Text)
F = Split(Split(CreateObject("Scripting.FileSystemObject").opentextfile(ActiveWorkbook.Path & "\" & "temp.input.out").readall, "Component")(1), vbCrLf & D & ",")(0)
With Sheets("Output Summary").Cells(1).Value = "Component" & F
End With
It never enters anything into the appropriate cell. Also I really cannot use the message box at all because that is reserved for error codes in the macro I am running. I just need the appropriate text copied from the ActiveWorkbook.Path & "\" & "temp.input.out" file and then inserted into cell 1 of the Output Summary sheet of the already active Excel file into separate cells by comma delineation. Please explain step-by-step because I am a complete beginner to coding in VB and Excel macros.

Thanks again for all your help. I greatly appreciate it.

Kenneth Hobs
05-29-2015, 10:53 AM
Jeremy, you have two errors.
Change "ActiveW orkbook.Path" to "ActiveWorkbook.Path.
Remove the With and EndWith.

MsgBox and Debug.Print are used as illustrative help and quick debugging tools by some coders.

Press F1 with the cursor in or next to a key word to get help.

Jeremy42
05-29-2015, 11:38 AM
Thanks for the quick reply. The space between W and o in Workbook was just a copy paste error in this post, it was correct in the excel code. When I modify the code to remove the With and End With it reads:
D = Val(SimulationsTextBox.Text) +1
F = Split(Split(CreateObject("Scripting.FileSystemObject").opentextfile(ActiveWorkbook.Path & "\" & "temp.input.out").readall, "Component")(1), vbCrLf & D & ",")(0)
Sheets("Output Summary").Cells(1).Value = "Component" & F

The text is now being entered into cell 1, however this does not address the part where it is selecting the whole file starting with Component and not just the 9 lines (in this example) the code is meant to select, or how to split the text into separate cells by comma delineated method.

Thanks again for your continued help.

Edit " the copy past bug in the space between the W and o in ActiveWorkbook.Path is still showing up in this post but it doesnt in the edit. In reality there is no space between them.

2nd Edit: if I use the code:
D = Val(SimulationsTextBox.Text)
instead of
D = Val(SimulationsTextBox.Text) +1
it only imports 8 lines where i need it to import 9 lines

It seems like a simple syntax error but im not sure what is causing it.

SamT
05-29-2015, 12:12 PM
Jeremy, we really do expect a coder to take the time to understand our examples and edit them to suit before they run them. That is why some of us give personal names to Subs. Do you fully understand snb's example? It is what you seem to be trying to use.

Have you tried my offering and Kenneth's version of same? Did they work at all? It is obvious in both where to use the TextBox value. You merely have to edit the code to properly access it. Where is the TextBox located? We can't know that, so we can't properly code it. His version uses "For i = 1 to 8" is a way to test the sub without the issues of trying to get access to the TextBox to work. Both use MsgBox for the same reason. One step at a time, Ya know? We fully understand that you don't need the results in a MsgBox even a few times, it will work in production

Did you put "ActiveWorkbook.Path & "\" & "temp.input.out" into my code where directed to in the code itself?

You have to help us out here.

Kenneth Hobs
05-29-2015, 01:07 PM
It is rare that I would post code that does not work for me.

To poke the values into a cell, one needs to tweak the 2 methods just a bit.


Sub M_snb2()
i = 8
Worksheets("Sheet2").Range("A1").Value = "Component" & _
Split(Split(CreateObject("Scripting.FileSystemObject").opentextfile _
("x:\FileReadWrite\csv\ImportAtStringPlusLines.txt").readall, "Component")(1), vbCrLf & i & ",")(0)
End Sub

Sub SamT2()
Const Found As String = "Component"
Dim HeaderLine As String
Dim i As Long
Dim TextStream As Variant
Dim TxtFile As Variant
Dim FSO As Object
Dim s() As String, fn As String, r As Range, ii As Integer
'https://msdn.microsoft.com/en-us/subscriptions/ebkhfaaz(v=vs.84).aspx
Const OpenFileForReading = 1

On Error GoTo PlayNice

'Inputs/Output
ii = 8 'Number of more lines to read.
fn = "x:\FileReadWrite\csv\ImportAtStringPlusLines.txt"
Set r = Worksheets("Sheet2").Range("A1") 'Output sheet and range cell.
ReDim s(0 To ii) As String 'String array that will hold the output string values.

Set FSO = CreateObject("Scripting.FileSystemObject")

Set TxtFile = FSO.GetFile(fn)
Set TextStream = TxtFile.OpenAsTextStream(OpenFileForReading)
Do
HeaderLine = TextStream.readline
If Not Left(HeaderLine, 9) = Found Then HeaderLine = ""
Loop Until HeaderLine <> ""

'Fill array to contain snipped lines from the file.
s(0) = HeaderLine
With TextStream
For i = 1 To ii
s(i) = .readline
Next i
End With

'Poke output into one cell.
r.Value2 = Join(s(), vbCrLf)

'Play nice
PlayNice:
TextStream.Close
Set TxtFile = Nothing
Set FSO = Nothing
End Sub

Howsoever, keep in mind that when asking a question, it is best to state a goal rather than detailing what steps you think are needed to achieve that goal. I suspect that ultimately, you would probably want the data snippet poked into a range of cells just as if you had imported a normal CSV file.

To achieve the 2nd goal using Sam's code:

Sub SamT3()
Const Found As String = "Component"
Dim HeaderLine As String
Dim i As Long
Dim TextStream As Variant
Dim TxtFile As Variant
Dim FSO As Object
Dim s() As String, ss() As String, fn As String, r As Range, ii As Integer
'https://msdn.microsoft.com/en-us/subscriptions/ebkhfaaz(v=vs.84).aspx
Const OpenFileForReading = 1

'On Error GoTo PlayNice

'Inputs/Output
ii = 8 'Number of more lines to read.
fn = "x:\FileReadWrite\csv\ImportAtStringPlusLines.txt"
Set r = Worksheets("Sheet2").Range("A1") 'Output sheet and range cell.
ReDim s(0 To ii) As String 'String array that will hold the output string values.

Set FSO = CreateObject("Scripting.FileSystemObject")

Set TxtFile = FSO.GetFile(fn)
Set TextStream = TxtFile.OpenAsTextStream(OpenFileForReading)
Do
HeaderLine = TextStream.readline
If Not Left(HeaderLine, 9) = Found Then HeaderLine = ""
Loop Until HeaderLine <> ""

s(0) = HeaderLine
With TextStream
For i = 1 To ii
s(i) = .readline
Next i
End With

'Poke comma delimited output into cells.
For i = 0 To UBound(s)
ss = Split(s(i), ",")
r.Offset(i).Resize(, 1 + UBound(ss)).Value = ss()
Next i

'Play nice
PlayNice:
TextStream.Close
Set TxtFile = Nothing
Set FSO = Nothing
End Sub

It is my preference to code as Sam does where one can specify Option Explicit as the first line of code in a Module. Of course doing that requires that you make use of Dim for all of your variables. When testing code such snb's, you can not do that.

I could have posted my code which was similar to Sam's but longer as I tend to be a bit more verbose than some and way more than snb.

See, it does pay to procrastinate. Guess my king title is still intact.

Jeremy42
05-29-2015, 01:45 PM
Thanks for your continued help. I am looking into SamT and Kenneth's latest post. I will get back to you once I fully test them. Also for some reason I am unable to attach a temp.input.out or even a temp.txt file to the forum. I created a sanitized excel file and temp.input.out file to fully show what the problem is but as I said it kept saying invalid file type when I tried to attach them. (the excel file would upload w/o a problem, but w/o the associated file to import there was no point in just attaching that)

Thanks again.

snb
05-30-2015, 04:03 AM
You misformulated your requirements in the first post.


Sub M_snb()
c00=split(CreateObject("Scripting.FileSystemObject").opentextfile("G:\OF\example.txt").readall,"Component")(1)
msgbox split(c00,split(c00,vbcrlf)(Val(SimulationsTextBox.Text)))(0)
End Sub

SamT
05-30-2015, 08:39 AM
Also for some reason I am unable to attach a temp.input.out or even a temp.txt
Valid file extensions: accdb bmp csv doc docm docx gif jpe jpeg jpg pdf png pptm pptx txt xls xlsb xlsm xlsx zip

No out files allowed. The .txt file might have been too big. :dunno

We really only need to see a very small portion of most data, just so we can understand how its laid out and formated.

Jeremy42
06-02-2015, 10:04 AM
Thanks all for your continued help! I ended up using a different solution and I am posting it to help others:


'activates and clears the output summary worksheet
ActiveWorkbook.Sheets("Output Summary").Activate
Call clear_sheet("Output Summary")

fn = ActiveWorkbook.Path & "\temp.input.out"
Set Z = CreateObject("Scripting.FileSystemObject")
A = Z.OpenTextFile(fn).readall 'read the complete file temp.input.out into the variable a
Z.OpenTextFile(fn).Close

'store each line of temp.input.out into the array b
b = Split(A, vbCrLf)

k = 1
'Start with the 23rd line ... The first 23 lines are anyways input echo, might save unnecessary reading
For i = 23 To UBound(b)
y = InStr(b(i), "Component") 'look for the line which starts with "Component"
If y <> 0 Then
For j = i To i + Val(SimulationsTextBox.Text)
D = Split(b(j), ",") 'split line at comma and store the lives in the array D

With Sheets("Output Summary").Cells(k, 1).Resize(1, (UBound(D) + 1)) 'resize increases the range to accomodate the array
.Value = D
End With
k = k + 1
Next j
Exit For
End If
Next i

'Changes Cells A2 through E & p to General Format instead of Text
p = Val(SimulationsTextBox.Text) + 1
ActiveWorkbook.Sheets("Output Summary").Range("A2:E" & p).Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With

' Autofits all columns in this sheet
ThisWorkbook.Worksheets("Output Summary").Cells.EntireColumn.AutoFit

Kenneth Hobs
06-02-2015, 11:28 AM
Please use code tags. Click the # icon to insert them or type (code)'your code here(/code) and change ()'s to []'s. All forums are like that.

For those that don't know, this was cross-posted to many forums. This one has the links to the others as well. http://www.ozgrid.com/forum/showthread.php?t=194979