Reading a text file

[nepotist]

Hello,

I am trying to extract some information from a bunch of files in a folder in to excel. I haven't got a grasp of how to read a text file. I am aware that I could use the open and seek functions and also use stream reader. I am not able to find good example to understand it I would appreciate if some one get me a link or a good example for the same. I would like to read 6th line in the text file from with a start and end point.

Another question is My files are in .syn format, would the streamer or the open command will be able to read this file?

Any information that would help me understand this concept better would be greatly appreciate.

Thank you

[Kenneth Hobs]

You have to tell us the file type, we can only guess. http://filext.com/file-extension/syn

If you open the file in notepad, you can see if it is binary or not readily. If not, then text file methods would work.

[nepotist]

Hello Kenneth,

The extension of the file is .syn, but I can view my data when I open the same file in notepad. My trail and error has taught me to read line and full text , but I am still unable to understand how do I read a portion of line and a particular line.

Thank you

[shrivallabha]

You can have a look at the thread below. There could two approaches to handle what you are setting out to do:
1. Importing the Text File completely and then deleting unwanted data. This can be done by the OpenText Method. It will be useful if the data is parse-able either by fixed width or Constant Delimiter.
2. Importing data selectively from Text File. This would require Text File Open / Close (using #Freefile) method.
http://www.vbaexpress.com/forum/showthread.php?t=36479

[nepotist]

Hello Shrivallabha,

I have looked in to the link you have provided and it still doesn't answer my question on how to read a particular line (line 1 or 8 or 10) and characters in the line by providing a start and end point.
I do not want to import the text file in to excel. I have attached the .syn FILE and I have underlined the data that I am interested in retrieving form the .syn FILE. The attached zip file contains and image file and .syn file. You can open the .syn file with notepad.

[nepotist]

Alright,

I have achieved what I need and still need one tiny bit of correction in the code. I couldn't find any information regarding the method I have been asking, so I used Instr function to check if the readline info is need and then use the data. Here is code below that I am using and need help with two things. 1) Can I reduce the use of filesystemobject in anyway? (tried to use the variable with prefix Obj but throws an runtime error 91 so had to declare VARIABLE with prefix O) 2) When I am updating/retrieving the stationID value (Please see the comment in code) for example if the station ID in the text file is "001", it updates the excel sheet as "1" how do I fix it and have my excel sheet to show is it as '001".

Thanks to everyone who has put in time to look in to this.
Sub Import_Data()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim DirPath As String
Dim Lrow As Long
Dim Test As String
Dim T As String
Dim oFSO As New FileSystemObject
Dim oFS
Dim stext As String


Lrow = 1
DirPath = Application.GetOpenFilename()

DirPath = CurDir$(DirPath) & "\"
Set objFSO = CreateObject("Scripting.FileSystemObject")


'Get the folder object associated with the directory
Set objFolder = objFSO.GetFolder(DirPath)

With ActiveSheet
.Cells(Lrow, 1) = "County"
.Cells(Lrow, 2) = "Station"
.Cells(Lrow, 3) = "Daily Volume"
.Cells(Lrow, 4) = "Count Date"
.Cells(Lrow, 5) = "File Path"
End With

'Loop through the Files collection
For Each objFile In objFolder.Files
Lrow = Lrow + 1
Test = objFile.Path
Set oFS = oFSO.OpenTextFile(Test)

Do Until oFS.AtEndOfStream

stext = oFS.ReadLine
If (InStr(stext, "County:") = 1) Then
stext = Trim(Mid(stext, 9, Len(stext) - 8))
ActiveSheet.Cells(Lrow, 1).Value = CStr(stext) 'Updates County ID
ElseIf (InStr(stext, "Station:") = 1) Then
stext = Trim(Mid(stext, 9, Len(stext) - 8))
ActiveSheet.Cells(Lrow, 2).Value = CStr(stext) 'Updates StationID
ElseIf (InStr(stext, "Start Date:") = 1) Then
stext = Trim(Mid(stext, 12, Len(stext) - 11))
ActiveSheet.Cells(Lrow, 4).Value = CDate(stext)
ElseIf (InStr(stext, "24-Hour Totals:") = 1) Then
stext = Trim(Right(stext, 8))
ActiveSheet.Cells(Lrow, 3).Value = stext
End If
ActiveSheet.Cells(Lrow, 5) = Test
Loop
Next

'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set oFSO = Nothing
Set oFS = Nothing

End Sub

Any info is appreciated.

EDIT: i forgot to ask if it is possible to update a Oracle database with this table. This is the main purpose of the this program to retrive info from Different txt file formats and update the oracle database. I would appreciate if some one can guide me through a good reference.
Thank you

[BrianMH]

excel auto converts to numbers

ElseIf (InStr(stext, "Station:") = 1) Then
stext = Trim(Mid(stext, 9, Len(stext) - 8))
ActiveSheet.Cells(Lrow, 2).numberformat = "@"
ActiveSheet.Cells(Lrow, 2).Value = CStr(stext) 'Updates StationID

[nepotist]

Cool one down two to go. Thanks BrianMH

[Kenneth Hobs]

You mixed early binding and late binding methods. While one can do that, in this case, use one or the other. I tweaked your code a bit to get a result that I could test.

Sub Import_Data()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim DirPath As String
Dim Lrow As Long
Dim T As String
Dim oFSO As Object
Dim oFS As Object
Dim stext As String

Lrow = 1

'DirPath = Application.GetOpenFilename()
'DirPath = CurDir$(DirPath) & "\"
DirPath = ThisWorkbook.Path & "\"

Set oFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object associated with the directory
Set objFolder = oFSO.GetFolder(DirPath)

With ActiveSheet
.Cells(Lrow, 1) = "County"
.Cells(Lrow, 2) = "Station"
.Cells(Lrow, 3) = "Daily Volume"
.Cells(Lrow, 4) = "Count Date"
.Cells(Lrow, 5) = "File Path"
End With

'Loop through the Files collection
For Each objFile In objFolder.Files
If LCase(Right(objFile, 3)) <> "syn" Then GoTo nextFile
Lrow = Lrow + 1
Set oFS = oFSO.OpenTextFile(objFile, 1)
Do Until oFS.AtEndOfStream
stext = oFS.ReadLine
With ActiveSheet
If (InStr(stext, "County:") = 1) Then
stext = Trim(Mid(stext, 9, Len(stext) - 8))
.Cells(Lrow, 1).Value = CStr(stext) 'Updates County ID
.Cells(Lrow, 1).NumberFormat = "00"
ElseIf (InStr(stext, "Station:") = 1) Then
stext = Trim(Mid(stext, 9, Len(stext) - 8))
.Cells(Lrow, 2).Value = CStr(stext) 'Updates StationID
.Cells(Lrow, 2).NumberFormat = "000"
ElseIf (InStr(stext, "Start Date:") = 1) Then
stext = Trim(Mid(stext, 12, Len(stext) - 11))
.Cells(Lrow, 4).Value = CDate(stext)
ElseIf (InStr(stext, "24-Hour Totals:") = 1) Then
stext = Trim(Right(stext, 8))
.Cells(Lrow, 3).Value = stext
End If
.Cells(Lrow, 5) = objFile
End With
Loop
oFS.Close
nextFile:
Next

'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
Set oFSO = Nothing
Set oFS = Nothing

End Sub


I did not modify your IF/ELSEIF loop. That might be best done in a SELECT CASE loop but if the IF suits you, use it.

For the Oracle issue, that is best done in a separate question for a new thread with a link to one that might relate. Generically speaking though, I would use ADO to do it. Depending on security needs, you might add records to a Access database and update Oracle from it. It is up to you to decide what works best for you. For ADO examples, I recommend this site: http://www.erlandsendata.no/english/index.php?t=envbadac

Once you know ADO, you can connect to many database types if you know the connection string. For connection strings see: http://connectionstrings.com/

[nepotist]

Hello Kenneth,

Thanks for your feed back, Well you have commented out these two line in my code
DirPath = Application.GetOpenFilename()
'DirPath = CurDir$(DirPath) & "\" These line give the user (In this case I consider it to be the database Admin) the Flexibility to run the code form any where instead of having the excel file in the same folder. Unless there is a way to select a Folder (GetFloder) option avaialble.

I am a bit familiar with ADO but dint knew how to work it with excel. Thanks for the links I will keep this thread updated

[Kenneth Hobs]

I commented the lines out and set the file's folder for testing purposes. If you want a getfolder example then try:
Sub Test_GetFolder()
MsgBox Get_Folder("Pick One Folder", ThisWorkbook.Path)
End Sub

'http://www.vbaexpress.com/forum/showthread.php?t=34695
Function Get_Folder(Optional HeaderMsg As String = "", Optional initialFilename As String = "") As String
If HeaderMsg = "" Then HeaderMsg = "Select a Folder"
If initialFilename = "" Then initialFilename = Application.DefaultFilePath
With Application.FileDialog(msoFileDialogFolderPicker)
.initialFilename = initialFilename
.Title = HeaderMsg
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
Get_Folder = .SelectedItems(1)
Else
Get_Folder = ""
End If
End With
End Function

[shrivallabha]

Hi Nepotist,
I saw your post but had very little time on my hands to look at it. I've tweaked Kenneth's code a little more since you have mentioned in post#6 that you want to use less of FileSystemObject.

1. If you are using Excel 2003 or lower then you can skip FileSystemObject completely using Application.FileSearch but the code won't be compatible with 2007+ Versions which is not good (since we know we should not ignore)

2. Note the use of Select Statement. If the pattern in the *.syn file is fixed to T then perhaps you can change the last Case Else (to Case 35) as well but in most documents have fixed headers but the text body indeterminate the current approach is better.

3. Secondly, there are many methods of reading text file. The one which I have used is I'm more comfortable with. Here is the updated code:
Sub Import_Data()
Dim oFSO As Object, objFolder As Object, objFile As Object
Dim lRow As Long
Dim stext As String, sPath As String, DirPath As String

lRow = 1

'DirPath = Application.GetOpenFilename()
'DirPath = CurDir$(DirPath) & "\"
DirPath = ThisWorkbook.Path & "\"

Set oFSO = CreateObject("Scripting.FileSystemObject")

'Get the folder object associated with the directory
Set objFolder = oFSO.GetFolder(DirPath)

With ActiveSheet
.Cells(lRow, 1) = "County"
.Cells(lRow, 2) = "Station"
.Cells(lRow, 3) = "Daily Volume"
.Cells(lRow, 4) = "Count Date"
.Cells(lRow, 5) = "File Path"
End With

'Loop through the Files collection
For Each objFile In objFolder.Files
If LCase(Right(objFile, 3)) <> "syn" Then GoTo nextFile
sPath = ThisWorkbook.Path & "\" & objFile.Name
lRow = lRow + 1
Counter = 1
With ActiveSheet
Open sPath For Input As #1
Do While Not EOF(1)
Line Input #1, stext

Select Case Counter
Case 1
stext = Trim(Mid(stext, 9, Len(stext) - 8))
.Cells(lRow, 1).Value = CStr(stext) 'Updates County ID
.Cells(lRow, 1).NumberFormat = "00"
Counter = Counter + 1

Case 2
stext = Trim(Mid(stext, 9, Len(stext) - 8))
.Cells(lRow, 2).Value = CStr(stext) 'Updates StationID
.Cells(lRow, 2).NumberFormat = "000"
Counter = Counter + 1

Case 4
stext = Trim(Mid(stext, 12, Len(stext) - 11))
.Cells(lRow, 4).Value = CDate(stext)
Counter = Counter + 1

Case Else
If (InStr(stext, "24-Hour Totals:") = 1) Then
stext = Trim(Right(stext, 8))
.Cells(lRow, 3).Value = stext
Counter = Counter + 1
End If
Counter = Counter + 1
End Select

.Cells(lRow, 5) = sPath
Loop
Close #1
End With
nextFile:
Next

'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set oFSO = Nothing

End Sub


One more note of caution: *.syn is not a commonly used extension but if it is on your systems since you use it frequently then "Hide Extensions for known File Types" option in Windows Explorer >> Tools >> Folder Options must be unchecked otherwise you will end up with ubiquitous Error 9: Subscript out of range Error.

[Kenneth Hobs]

Building on what has been posted so far. The Dir() removes the need for FSO.

When using the function below to get a folder name, be sure to delete what is in the File name box before clicking OK if you want to use the parent folder that it first opens to.

Sub Import_Data2()
Dim lRow As Long, counter As Long, fileNum As Integer
Dim stext As String, sPath As String, DirPath As String, fn As String

lRow = 1

'DirPath = Application.GetOpenFilename()
'DirPath = CurDir$(DirPath) & "\"
'DirPath = ThisWorkbook.Path & "\"
DirPath = Get_Folder("Pick Folder with SYN Type Files", ThisWorkbook.Path)
If DirPath = "" Then Exit Sub
If Right(DirPath, 1) <> "\" Then DirPath = DirPath & "\"

With ActiveSheet
.Range("A1").Value2 = "County"
.Range("B1").Value2 = "Station"
.Range("C1").Value2 = "Daily Volume"
.Range("D1").Value2 = "Count Date"
.Range("E1").Value2 = "File Path"
End With

'Loop through the Files in dirpath
fn = Dir(DirPath)
If fn = "" Then Exit Sub
Do While fn <> ""
sPath = DirPath & fn
If LCase(Right(fn, 3)) <> "syn" Then GoTo nextFile
lRow = lRow + 1
counter = 1
With ActiveSheet
fileNum = FreeFile
Open sPath For Input As #fileNum
Do While Not EOF(1)
Line Input #fileNum, stext
Select Case counter
Case 1
stext = Trim(Mid(stext, 9, Len(stext) - 8))
.Range("A" & lRow).Value2 = CStr(stext) 'Updates County ID
.Range("A" & lRow).NumberFormat = "00"
counter = counter + 1

Case 2
stext = Trim(Mid(stext, 9, Len(stext) - 8))
.Range("B" & lRow).Value2 = CStr(stext) 'Updates StationID
.Range("B" & lRow).NumberFormat = "000"
counter = counter + 1

Case 4
stext = Trim(Mid(stext, 12, Len(stext) - 11))
.Range("D" & lRow).Value = CDate(stext)
counter = counter + 1

Case Else
If (InStr(stext, "24-Hour Totals:") = 1) Then
stext = Trim(Right(stext, 8))
.Range("C" & lRow).Value2 = stext
counter = counter + 1
End If
counter = counter + 1
End Select

.Range("E" & lRow).Value2 = sPath
Loop
Close #fileNum
End With
nextFile:
fn = Dir()
Loop
End Sub

'http://www.vbaexpress.com/forum/showthread.php?t=34695
Function Get_Folder(Optional HeaderMsg As String = "", Optional initialFilename As String = "") As String
If HeaderMsg = "" Then HeaderMsg = "Select a Folder"
If initialFilename = "" Then initialFilename = Application.DefaultFilePath
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.initialFilename = initialFilename
.Title = HeaderMsg
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
Get_Folder = .SelectedItems(1)
Else
Get_Folder = ""
End If
End With
End Function

[shrivallabha]

That's really nice. I have doubt in one regard which is:
fileNum = FreeFile
Open sPath For Input As #fileNum
Instead of #1, what advantage does FreeFile give us? I'd really like to know and then incorporate in future coding.

[Kenneth Hobs]

I recommend FreeFile as code can run so fast that the operating system may not completely close a file before it opens another. For most cases, it will probably not be an issue. Another process may be running that already uses that number for its text files which are usally temporary.


From the help:

Use FreeFile to supply a file number that is not already in use.

Some other links say similar things. http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=622

In my code, replace:
Do While Not EOF(1)
with:
Do While Not EOF(fileNum)

[shrivallabha]

Thank you very much, Kenneth.

[nepotist]

Even though we have open file statement within the code, I don't see the file actually being opened. How can you accomplish the same thing if I have to read an excel file?

[Kenneth Hobs]

You lost me. Files can be opened in the Foreground like Excel files or in the background which is invisible to the user. Background methods will open and close faster than foreground methods.

[nepotist]

I wasn't aware of it, where exactly would you set that visible or hidden property about opening a file.

[Kenneth Hobs]

It varies depending on what you are doing. For the Open command or any VBA command, in the Visual Basic Editor (VBE), put the cursor in the command word or at the beginning or end and press F1 for help or search after press F2.

Some commands like those in Reference objects may not be readily available by the F1 or F2 methods. For FSO commands, I use this help file though early binding methods makes the methods and properties easy to use: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=01592c48-207d-4be1-8a76-1c4099d7bbb9&displaylang=en

If you want to pursue those types of things, you can start a new thread.

[nepotist]

Thanks Kenneth