Results 1 to 14 of 14

Thread: [RESOLVED] How do I extract only certain data from a text file?

  1. #1

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Resolved [RESOLVED] How do I extract only certain data from a text file?

    At the suggestion of Martin Liss, I've started a new thread for this question.

    I have a text file that contains a lot of verbiage, followed by data that is arranged in rows and columns (ala Excel), which is then followed by verbiage and then rows and columns of data and so on. The verbiage can be any length, as can the rows and columns of data also be of any length.

    What I want to do is to, when a group of data in rows and columns is detected, take that data and put it into an Excel file, and when the next group of data is detected, place that data into a new Excel file, and keep on doing this until the txt file EOF is encountered.

    One issue is that the row and column data can span multiple pages, which means that page headers and footers can be interspersed throughout the data I want to extract.

    If I have a group of data that spills from one page to another page to another page and so on, how might I keep all of the data in one file while records are being processed?

    I've attached a small sample file for review.

    If anyone can help with this overall question, I sure would be happy to hear what you all have to say. Thanks so much in advance!! Looking forward to it.
    Attached Files Attached Files

  2. #2
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: How do I extract only certain data from a text file?

    this looks familiar.. didnt I do this for u already? (or am I just tired! lol)

    basically just split the file by vbCrLf test for the firs col.. if it meets criteria then the line is good..
    like

    if left(Line(x),1) = "c" then
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  3. #3

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: How do I extract only certain data from a text file?

    Static,

    Yes, it is familiar, but I had added an additional question to the other post, namely, "One issue is that the row and column data can span multiple pages, which means that page headers and footers can be interspersed throughout the data I want to extract.

    If I have a group of data that spills from one page to another page to another page and so on, how might I keep all of the data in one file while records are being processed?

    I've attached a small sample file for review."

    And, in the other post, Martin Liss indicated he did not understand the question and suggested I start another thread, which I did, and indicated at the top of the new thread why it was started.

    In any case, there is my question......

    If a segment of data can span several pages, and therefore have header and other verbiage in its midst, how do I go about keeping only the data I want in one file and skip through the unwanted verbiage?

    Also, for each new piece of data I want from within the file, how do I go about puting each piece of desired data into its own output Excel file? The reason I ask is because each text file can contain multiple desired data.

    Thanks for replying, and I hope my explanation was clear enough to explain why a new thread was started.

  4. #4

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: How do I extract only certain data from a text file?

    Static,

    Hope you understood my explanation of why I started another thread.

    Also, checking for the letter "C" in the first column, while a good idea, won't work in all cases, since the data desired does not necessarily always start with the letter "C". It can start with any letter or number. The only thing the data has in common is that it's in a row - column format.

    And last, any idea on what I might do when and if data spans several pages?

    Thanks again!!

  5. #5
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: How do I extract only certain data from a text file?

    VB Code:
    1. Private Sub Command1_Click()
    2.     Dim tmp() As String
    3.     Dim Lines() As String
    4.     Dim GO As Boolean
    5.     Open "C:\greaseman.txt" For Input As #1
    6.         tmp = Split(Input(LOF(1), 1), vbCrLf)
    7.     Close #1
    8.     For x = 0 To UBound(tmp)
    9.         If Left(tmp(x), 3) = "DES" Then
    10.             GO = True
    11.             x = x + 1
    12.         End If
    13.         If Left(tmp(x), 2) = "* " Then
    14.             GO = False
    15.         End If
    16.         If GO Then
    17.             If tmp(x) <> "" Then
    18.                 Do While InStr(tmp(x), "  ") <> 0
    19.                     tmp(x) = Replace(tmp(x), "  ", " ")
    20.                 Loop
    21.                 Lines = Split(tmp(x), " ")
    22.                 For I = 0 To UBound(Lines)
    23.                     'add to excel here like this
    24.                     'xls.cells(row,I+1) = Lines(I)
    25.                     'remove the following lines.. just for testing...
    26.                     If I = UBound(Lines) Then
    27.                         Debug.Print Lines(I)
    28.                     Else
    29.                         Debug.Print Lines(I) & " ";
    30.                     End If
    31.                     '---------
    32.                 Next
    33.             End If
    34.            
    35.         End If
    36.     Next
    37. End Sub
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  6. #6

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: How do I extract only certain data from a text file?

    Static,

    Thank you for your suggested code!! I really appreciate you taking the time to help me out. I can't wait until I can wrap myself more around this sort of thing.

    I'll play around with your code suggestion and see what comes out.

    Thanks again.

  7. #7
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: How do I extract only certain data from a text file?

    Hey not a problem! glad to help.. I have parsed MUCH worse txt files than that lol

    its just a matter of finding a pattern.. something that either says this is a good line..
    or something that shows the start/finish of lines...

    Trial and error was my best teacher..
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  8. #8

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: How do I extract only certain data from a text file?

    Static,

    It's looking pretty good!! All I have left to do is try to figure out the code I'll need place into your suggested code so that the program will place only the first three pieces of data onto a line, increment the line, grab the next three pieces of data onto that line, and so on across the row of data.

    How would I go about doing that within the code you already suggested???

    Thanks SOOOOoooooo much !!!

  9. #9
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: How do I extract only certain data from a text file?

    Replace this:
    VB Code:
    1. For I = 0 To UBound(Lines)
    2.                     'add to excel here like this
    3.                     'xls.cells(row,I+1) = Lines(I)
    4.                     'remove the following lines.. just for testing...
    5.                     If I = UBound(Lines) Then
    6.                         Debug.Print Lines(I)
    7.                     Else
    8.                         Debug.Print Lines(I) & " ";
    9.                     End If
    10.                     '---------
    11.                 Next
    with something like this...

    VB Code:
    1. For I = 0 To 2
    2.     xls.cells(row, I + 1) = lines(I)
    3. Next
    4. For I = 3 To 5
    5.     xls.cells(row, I - 2) = lines(I)
    6. Next
    7. For I = 6 To 8
    8.     xls.cells(row, I - 5) = lines(I)
    9. Next
    10. I didnt count columns so im not sure where to stop...
    11. 'but the last set of three be sure to use the ubound...
    12. 'since I did notice some of the data lines do not have all the
    13. 'elements....
    14.  
    15. For I = 9 To UBound(lines)
    16.     xls.cells(row, I - 8) = lines(I)
    17. Next
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  10. #10
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: How do I extract only certain data from a text file?

    oops.. and dont forget

    Dim row as integer


    ..etc..

    row = row + 1
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  11. #11

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: How do I extract only certain data from a text file?

    Static,

    That's great!!!! Thank you, thank you, thank you!!


  12. #12
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: How do I extract only certain data from a text file?

    youre welcome (dont forget to mark it resolved)
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  13. #13

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: [RESOLVED] How do I extract only certain data from a text file?

    Hi, Static,

    At the risk of being overbearing, I tried your suggestion of

    visual basic code:--------------------------------------------------------------------------------
    For I = 0 To 2
    xls.cells(row, I + 1) = lines(I)
    Next
    For I = 3 To 5
    xls.cells(row, I - 2) = lines(I)
    Next
    For I = 6 To 8
    xls.cells(row, I - 5) = lines(I)
    Next
    For I = 9 To UBound(lines)
    xls.cells(row, I - 8) = lines(I)
    Next

    To change my data from a bunch of rows with data across, to data going down one column, but it didn't work too well. I had data from the first column followed by data from the fourth column and so on, but what I wanted was data from the first column to be followed by data from the second column and so on.

    I've included my code here.... if you wouldn't mind, would you please take a look at it and tell me where I made my mistake(s)? Also, I've I'm being too much of a bother, please tell me that.

    Thanks in advance!

    Private Sub ExportExcel()

    Dim tmp() As String
    Dim Data() As String
    Dim ALLData As String
    Dim strFilename As String
    Dim sFileName As String
    Dim sParts() As String
    Dim sFigure As String
    Dim sComponent As String
    Dim sFigureNumber As String
    Dim iEmptyCounter As Integer
    Dim iRowCounter As Integer
    Dim FName As Variant
    Dim x As Long
    Dim y As Long
    Dim I As Long
    Dim xls As Object
    Dim Lines() As String
    Dim GO As Boolean

    With dlgCommonDialog
    .Filter = "Text (*.txt)|*.txt"
    .DialogTitle = "Select your desired Rockwell Collins file to open"
    .Flags = cdlOFNFileMustExist
    .CancelError = True
    .InitDir = App.Path
    .ShowOpen
    strFilename = Trim$(.FileName)
    End With

    sFileName = Trim$(strFilename)
    sParts = Split(sFileName, "\")
    sFileName = sParts(UBound(sParts))

    Set xls = CreateObject("Excel.application")

    xls.workbooks.Add
    ' xls.Visible = True

    Open sFileName For Input As #1
    tmp = Split(Input(LOF(1), 1), vbCrLf)
    Close #1

    Dim Row As Integer
    Row = 1
    For y = x To UBound(tmp)

    NeedTheFile:

    If Left(tmp(y), 6) = "Figure" Then
    sFigure = Left(tmp(y), 6)
    sFigureNumber = Mid(tmp(y), 8, 2)
    If Mid(sFigureNumber, 2, 1) = "." Then
    sFigureNumber = "0" & Mid(sFigureNumber, 1, 1)
    End If
    End If

    If Left(tmp(y), 24) = "Component Location Chart" Then sComponent = Left(tmp(y), 24)

    If sFigure = "Figure" And sComponent = "Component Location Chart" Then

    strFilename = Mid(sFileName, 1, 11) & "-Fig" & sFigureNumber & "-Final"

    For x = y To UBound(tmp)
    If Left(tmp(x), 3) = "DES" Then

    GO = True
    x = x + 1
    End If
    If Left(tmp(x), 2) = "* " Then
    GO = False
    End If
    If GO Then
    If tmp(x) <> "" Then
    Do While InStr(tmp(x), " ") <> 0
    tmp(x) = Replace(tmp(x), " ", " ")
    Loop
    Lines = Split(tmp(x), " ")

    For I = 0 To UBound(Lines)

    ' xls.cells(Row, I + 1) = Lines(I)
    ' If Lines(I) Like "*TP*" Or Lines(I) = "" Or IsNull(Lines(I)) Then
    ' GoTo FileSaveStep
    ' End If
    ' Next
    ' Row = Row + 1

    For I = 0 To 2
    xls.cells(Row, I + 1) = Lines(I)
    Next
    Row = Row + 1
    For I = 3 To 5
    xls.cells(Row, I - 2) = Lines(I)
    Next
    Row = Row + 1
    For I = 6 To 8
    xls.cells(Row, I - 5) = Lines(I)
    Next
    Row = Row + 1
    For I = 9 To UBound(Lines)
    xls.cells(Row, I - 8) = Lines(I)
    Next
    Row = Row + 1

    End If
    End If

    Next

    FileSaveStep:

    xls.activeworkbook.saveas FileName:=strFilename & ".xls", ReadOnlyRecommended:=False, CreateBackup:=False

    sFigure = ""
    sComponent = ""
    strFilename = ""
    sFigureNumber = ""

    xls.activeworkbook.Close

    xls.quit

    Set xls = CreateObject("Excel.application")

    xls.workbooks.Add
    ' xls.Visible = True

    y = x

    Row = 1

    End If

    Next
    End Sub


    I appreciate your help and your knowledge on this kind of stuff. My head hurts!!!

  14. #14

    Thread Starter
    Hyperactive Member greaseman's Avatar
    Join Date
    Jul 2005
    Location
    Somewhere, out there...
    Posts
    295

    Re: [RESOLVED] How do I extract only certain data from a text file?

    Static,

    Are you there????

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width