Wednesday, June 13, 2007

VBA Read Text Files (With Leading & Trailing Spaces)

Read Data frm Text Files (VBA)


Reading Text Files using VBA is one of the major development activity of programmers. There are multiple ways to read a file

1. Input # Statement
2. Input Function
3. Get Function
4. File System Object Functions


Input # Statement

Dim MyString, MyNumber
Open "c:\test.txt" For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Input #1, MyString, MyNumber ' Read data into two variables.
Debug.Print MyString, MyNumber ' Print data to the Immediate window.
Loop
Close #1 ' Close file.

However, the bug here is Input # does not take the leading or trailing spaces with it. That is, ' My Name is ' becomes 'My Name is'. This will not be the correct one as we need to get the spaces also

Then Input function comes handy

Dim MyChar
Open "c:\test.txt" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
MyChar = Input(1, #1) ' Get one character.
Debug.Print MyChar ' Print to the Immediate window.
Loop
Close #1 ' Close file.
However, the bug here will be the input that one needs - the number of characters to be extracted.

The obvious option is File system object


Sub Read_text_File()

Dim oFSO As New FileSystemObject
Dim oFS


Set oFS = oFSO.OpenTextFile("c:\textfile.TXT")

Do Until oFS.AtEndOfStream
sText = oFS.ReadLine
Loop


End Sub

This will read line-by line. all you need to add the Microsoft Scripting Runtime in the reference

Happy reading files:)

17 comments:

  1. Anonymous11:13 PM

    thank you for your examples. it helps me today :)

    ReplyDelete
  2. Anonymous5:07 PM

    Thanks mate. Much appreciated. Especially the different approaches

    ReplyDelete
  3. Anonymous1:04 PM

    You are the best one. The last scrip me it has been most useful. The DLL that that you use is fantastic. You are my salvation

    ReplyDelete
  4. Anonymous12:31 PM

    Great approach. How about id i want to read the first line and the last line. Any ideas?

    ReplyDelete
    Replies
    1. Anonymous12:29 AM

      use a counter
      line=1
      Do Until oFS.AtEndOfStream
      sText = oFS.ReadLine
      if line = 1 then line1 = stext
      line = line + 1
      Loop
      lastline=stext

      Delete
  5. Anonymous8:02 PM

    Thanks, alot

    ReplyDelete
  6. Anonymous2:49 PM

    Guys, please help!
    How can I open a txt file, start reading, say, from line 10, position 4 until the end of the line, then write that into spreadsheet row 3, column 5, and so on - moving down through the lines of the text file, reading values starting from a specific position and writing those values into a spreadsheet?
    I would really appreciate your help! Thanks!

    ReplyDelete
  7. Don't forget 'Line Input' - useful for lines with commas (,) whereas an Input statement would start the next line from directly after the comma :)

    ReplyDelete
  8. Anonymous4:52 AM

    thanx 4 this example t made 2 understand what i did no understand

    ReplyDelete
  9. Erik van Noort10:39 PM

    Do you have any hints how to read a file with spaces in the name?

    ReplyDelete
  10. Anonymous11:10 PM

    Found the solution for opening files with spaces in filename. use FileSystemObject to read file

    ReplyDelete
  11. In the last sample of reading file, instead of reading text line by line in


    Do Until oFS.AtEndOfStream
    sText = oFS.ReadLine
    Loop



    We can write sText = oFS.ReadAll(). Thought I would share it.

    Thanks,
    Office Coder
    www.officecoders.com

    ReplyDelete
  12. How to do if I have several fields in each line (some of the fields having spaces in them), separated by comma or any other delimiter?

    For example

    (example of a text file i want to read)

    0017;Peter Griffin;Family Guy;68779
    0018;Ozzy Osbourne;Still fighting rock singer;89455
    0023:Stephen King;Horror novel writer;09863
    0026;Alexandros Patrakis;Greek friend of mine;83766
    0039;Comfortably Numb;Best rock song ever;90743

    Thanks for the help and the article. It is superb!

    ReplyDelete
  13. Anonymous11:35 AM

    I don't know if this will help you anymore, but using the last example, do something like

    Dim iCursor As Integer
    Dim sID, sStar, sShow, sLastField

    iCursor = InStr(1, sLine, ";")
    sID = Left(sLine, iCursor - 1)
    iCursor = InStr(iCursor, sLine, ";")
    sStar = Mid(sLine, Len(sID), iCursor - Len(sID) - 1

    And just move forward in that fashion.

    Happy reading!

    ReplyDelete
  14. Henry Frazier11:53 AM

    The 3rd example was very helpful. I did not realize until is started looking at the input closely that it was breaking lines before the end because of the spaces in between fields. This was very useful. Thanks

    ReplyDelete
  15. This article has code to open, read and write text files and also to merge multiple text files to one single text file. Hope the commands in this article helps for your purpose.
    http://officetricks.com/merge-multiple-files-to-one-file/

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.