View Full Version : Looping through txt file and returning a section to excel
geeasa
05-01-2015, 03:33 PM
Hi I have the following vba that exports data in a .txt file to excel and working if I want to export everything in the .txt file to excel. I however want to bring only a section with a begin and end string in the text file but can't figire it out. Any help please...
Sub ReadStringData()
'To read "string" data, the string must be split in order to locate the values
'By Christos Samaras
'Declaring variables
Dim sLine As String
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long
Dim lColumn As Long
Dim vDataValues As Variant
Dim intCount As Integer
Dim eLine As String 'ea06065
'The full path of the text file that will be opened
sFName = Application.GetOpenFilename()
'Get an unused file number
intFNumber = FreeFile
On Error Resume Next
'Prepare text file for reading
Open sFName For Input As #intFNumber
'Check if the text file was found
If Err.Number <> 0 Then
MsgBox "Text file not found!", vbCritical, "Error!"
Exit Sub
End If
On Error GoTo 0
'Clearing the sheet
Sheet3.Cells.Clear
'First row for data
lRow = 1
'Loop until the end of file
Do While Not EOF(intFNumber)
'Do Until eLine = "NACMD DETAILS"
'Read data from file
Line Input #intFNumber, sLine
'Split values apart into an array
vDataValues = Split(sLine, vbTab)
With Sheet3
'First column for data
lColumn = 1
'Process each value in the array
For intCount = LBound(vDataValues) To UBound(vDataValues)
'Write the value to the worksheet
.Cells(lRow, lColumn) = vDataValues(intCount)
'Increase column count
lColumn = lColumn + 1
Next intCount
End With
'Next row of the worksheet
lRow = lRow + 1
Loop
'Close the file
Close #intFNumber
'Fitting column width
Sheet3.Cells.EntireColumn.AutoFit
'Just showing where the output data are
Sheet3.Activate
Range("A1").Select
'Inform the user about the process
MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "'!", vbInformation
End Sub
Thanks
Paul_Hossler
05-01-2015, 04:11 PM
1. Welcome
2. Use the [#] icon to insert [ CODE ] [/ CODE ] tags and paste any code between them
You didn't say where the 'begin' and 'end' were in the text file, so I assumed that it was the first split piece of text on the line
Not tested, but this might give you an idea -- I just set a flag when finding 'begin' and reset it when finding an 'end'
Only if the flag is set do I write the data.
Option Explicit
Sub ReadStringData()
'To read "string" data, the string must be split in order to locate the values
'By Christos Samaras
'Declaring variables
Dim sLine As String
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long
Dim lColumn As Long
Dim vDataValues As Variant
Dim intCount As Integer
Dim eLine As String 'ea06065
Dim bInsideBeginEnd As Boolean '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'The full path of the text file that will be opened
sFName = Application.GetOpenFilename()
'Get an unused file number
intFNumber = FreeFile
On Error Resume Next
'Prepare text file for reading
Open sFName For Input As #intFNumber
'Check if the text file was found
If Err.Number <> 0 Then
MsgBox "Text file not found!", vbCritical, "Error!"
Exit Sub
End If
On Error GoTo 0
'Clearing the sheet
Sheet3.Cells.Clear
'First row for data
lRow = 1
bInsideBeginEnd = False
'Loop until the end of file
Do While Not EOF(intFNumber)
'Do Until eLine = "NACMD DETAILS"
'Read data from file
Line Input #intFNumber, sLine
'Split values apart into an array
vDataValues = Split(sLine, vbTab)
If UCase(vDataValues(LBound(vDataValues))) = "BEGIN" Then
bInsideBeginEnd = True
End If
If bInsideBeginEnd Then
With Sheet3
'First column for data
lColumn = 1
'Process each value in the array
For intCount = LBound(vDataValues) To UBound(vDataValues)
'Write the value to the worksheet
.Cells(lRow, lColumn) = vDataValues(intCount)
'Increase column count
lColumn = lColumn + 1
Next intCount
End With
End If
If UCase(vDataValues(LBound(vDataValues))) = "END" Then
bInsideBeginEnd = False
End If
'Next row of the worksheet
lRow = lRow + 1
Loop
'Close the file
Close #intFNumber
'Fitting column width
Sheet3.Cells.EntireColumn.AutoFit
'Just showing where the output data are
Sheet3.Activate
Range("A1").Select
'Inform the user about the process
MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "'!", vbInformation
End Sub
geeasa
05-04-2015, 10:05 AM
1. Welcome
2. Use the [#] icon to insert [ CODE ] [/ CODE ] tags and paste any code between them
You didn't say where the 'begin' and 'end' were in the text file, so I assumed that it was the first split piece of text on the line
Not tested, but this might give you an idea -- I just set a flag when finding 'begin' and reset it when finding an 'end'
Only if the flag is set do I write the data.
Option Explicit
Sub ReadStringData()
'To read "string" data, the string must be split in order to locate the values
'By Christos Samaras
'Declaring variables
Dim sLine As String
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long
Dim lColumn As Long
Dim vDataValues As Variant
Dim intCount As Integer
Dim eLine As String 'ea06065
Dim bInsideBeginEnd As Boolean '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'The full path of the text file that will be opened
sFName = Application.GetOpenFilename()
'Get an unused file number
intFNumber = FreeFile
On Error Resume Next
'Prepare text file for reading
Open sFName For Input As #intFNumber
'Check if the text file was found
If Err.Number <> 0 Then
MsgBox "Text file not found!", vbCritical, "Error!"
Exit Sub
End If
On Error GoTo 0
'Clearing the sheet
Sheet3.Cells.Clear
'First row for data
lRow = 1
bInsideBeginEnd = False
'Loop until the end of file
Do While Not EOF(intFNumber)
'Do Until eLine = "NACMD DETAILS"
'Read data from file
Line Input #intFNumber, sLine
'Split values apart into an array
vDataValues = Split(sLine, vbTab)
If UCase(vDataValues(LBound(vDataValues))) = "BEGIN" Then
bInsideBeginEnd = True
End If
If bInsideBeginEnd Then
With Sheet3
'First column for data
lColumn = 1
'Process each value in the array
For intCount = LBound(vDataValues) To UBound(vDataValues)
'Write the value to the worksheet
.Cells(lRow, lColumn) = vDataValues(intCount)
'Increase column count
lColumn = lColumn + 1
Next intCount
End With
End If
If UCase(vDataValues(LBound(vDataValues))) = "END" Then
bInsideBeginEnd = False
End If
'Next row of the worksheet
lRow = lRow + 1
Loop
'Close the file
Close #intFNumber
'Fitting column width
Sheet3.Cells.EntireColumn.AutoFit
'Just showing where the output data are
Sheet3.Activate
Range("A1").Select
'Inform the user about the process
MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "'!", vbInformation
End Sub
geeasa
05-04-2015, 10:08 AM
Thanks for your quick response Paul. I must admit I am a newbie in VBA so please bear with me. I tried the code suggested but gts an error on the following line, any ideas?
If UCase(vDataValues(LBound(vDataValues))) = "EXCEPTIONS" Then
Thanks
Did you try to import the txt file into Excel ?
geeasa
05-04-2015, 02:36 PM
Did you try to import the txt file into Excel ?
Yes snb, I did but the file did not import.
You can import any txt file. & Please do not quote !!
Paul_Hossler
05-04-2015, 05:43 PM
...gts an error on the following line, any ideas?
Well, one idea would be to include the error message :yes
That would help a lot
This wasn't one of the lines I added, so I'm not sure where the error could be coming from
If UCase(vDataValues(LBound(vDataValues))) = "EXCEPTIONS" Then
geeasa
05-05-2015, 07:22 AM
Thanks Again Paul. I am getting the the following error when I run the script
Run-time error '9'
Subscript out of range.
Below are the details in teh sample file:
Exceptions File
5 8060781655 77543919 5151 2087.94 $- $(782.77) $6,976.34 4105.63 0 0 710255 0 SI
5 8060683633 43089826 5151 0 $- $670.39 $20,283.19 20953.58 0 0 700255 6 SI
5 8060412863 43154608 5151 2289.01 $- $555.38 $21,943.84 22499.22 2289.01 0 710255 0 SI
BEGIN EXCEPTIONS
No GL_CD PARTICGLCD OBL_NO OBG_NO CUR_BAL NET_BOOK_BAL PARTICCURBAL ST_CD PARTICSTCD
10 700255 710255 6000071033 6272287231 - - 0 0 7
10 700255 710255 6000091544 6272304275 - - 0 0 7
User Details
Number User Role Code Effective Date Status Code User Role Name Creator
30 Dummy 1/1/2013 A Suspense Clearer BO
31 Dummy2 1/1/2013 A Suspense Clearer BO
END XCXCXCX NET BAL
No OBG_NO OBL_NO PROC_TP CUR_BAL PARTICCURBAL CALC (C)CHGOFFAMTITD Recovery Paid_To_Prin NET_BOOK_BAL GL_CD FINAL_CLSE_IND SYS_CNVRTD_FROM
5 Sample 77543919 5151 2087.94 $- $(782.77) $6,976.34 4105.63 0 0 710255 0 SI
5 Sample 43089826 5151 0 $- $670.39 $20,283.19 20953.58 0 0 700255 6 SI
5 Sample 43154608 5151 2289.01 $- $555.38 $21,943.84 22499.22 2289.01 0 710255 0 SI
5 Sample 43092443 5151 6487.91 $- $(136.07) $6,653.99 30.01 0 0 710255 0 SI
5 Sample 43053292 5151 0 $- $50.63 $2,797.46 2848.09 0 0 710255 6 SI
Here is the vba I run:
Option Explicit
Sub ReadStringData()
'To read "string" data, the string must be split in order to locate the values
'By Christos Samaras
'Declaring variables
Dim sLine As String
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long
Dim lColumn As Long
Dim vDataValues As Variant
Dim intCount As Integer
Dim eLine As String 'ea06065
Dim bInsideBeginEnd As Boolean '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'The full path of the text file that will be opened
sFName = Application.GetOpenFilename()
'Get an unused file number
intFNumber = FreeFile
On Error Resume Next
'Prepare text file for reading
Open sFName For Input As #intFNumber
'Check if the text file was found
If Err.Number <> 0 Then
MsgBox "Text file not found!", vbCritical, "Error!"
Exit Sub
End If
On Error GoTo 0
'Clearing the sheet
Sheet3.Cells.Clear
'First row for data
lRow = 1
bInsideBeginEnd = False
'Loop until the end of file
Do While Not EOF(intFNumber)
'Do Until eLine = "NACMD DETAILS"
'Read data from file
Line Input #intFNumber, sLine
'Split values apart into an array
vDataValues = Split(sLine, vbTab)
If UCase(vDataValues(LBound(vDataValues))) = "BEGIN" Then
bInsideBeginEnd = True
End If
If bInsideBeginEnd Then
With Sheet3
'First column for data
lColumn = 1
'Process each value in the array
For intCount = LBound(vDataValues) To UBound(vDataValues)
'Write the value to the worksheet
.Cells(lRow, lColumn) = vDataValues(intCount)
'Increase column count
lColumn = lColumn + 1
Next intCount
End With
End If
If UCase(vDataValues(LBound(vDataValues))) = "END" Then
bInsideBeginEnd = False
End If
'Next row of the worksheet
lRow = lRow + 1
Loop
'Close the file
Close #intFNumber
'Fitting column width
Sheet3.Cells.EntireColumn.AutoFit
'Just showing where the output data are
Sheet3.Activate
Range("A1").Select
'Inform the user about the process
MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sheet3.Name & "'!", vbInformation
End Sub
Paul_Hossler
05-05-2015, 09:57 AM
I'm guess that's because you're trying to process an empty line
Try adding the 2 marked lines and see
'Loop until the end of file
Do While Not EOF(intFNumber)
'Do Until eLine = "NACMD DETAILS"
'Read data from file
Line Input #intFNumber, sLine
If Len(sLine) > 0 Then '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'Split values apart into an array
vDataValues = Split(sLine, vbTab)
If UCase(vDataValues(LBound(vDataValues))) = "BEGIN" Then
bInsideBeginEnd = True
End If
If bInsideBeginEnd Then
With Sheet3
'First column for data
lColumn = 1
'Process each value in the array
For intCount = LBound(vDataValues) To UBound(vDataValues)
'Write the value to the worksheet
.Cells(lRow, lColumn) = vDataValues(intCount)
'Increase column count
lColumn = lColumn + 1
Next intCount
End With
End If
If UCase(vDataValues(LBound(vDataValues))) = "END" Then
bInsideBeginEnd = False
End If
'Next row of the worksheet
lRow = lRow + 1
End If '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Loop
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.