none
working with string and records from a text file RRS feed

  • Question

  • How does it work? I need to understand what goes on when you open a text file and then want to work with it.

    The open command loads the text file into memory ??? Is this correct ?

    how can you work with the records of that file. Is each record a string or is the record an array ??


    Jim Neely

    Thursday, January 10, 2013 5:13 AM

Answers

  • If you look at the reply marked as Answer in your recent thread import multiple text files to a spreadsheet using array's, the _chr.txt text file is read using

            ...
            ' Loop through the rest
            Do While Not EOF(f)
                ' Read a line
                Line Input #f, strLine
                ' Get out if we reached END
                If strLine = "END" Then
                    Exit Do
                End If
                ' Move to next row
                r = r + 1
                ' Enter filename in column A
                wsh.Cells(r, 1) = strFilename
                ' Enter line in column B
                wsh.Cells(r, 2) = strLine
            Loop
            ...

    Instead of dumping the entire strLine into a cell (to be parsed later on using TextToColumns), you could use the Split function to parse strLine and to extract parts from it:

        Dim arrParts
        Dim strPartA As String
        Dim strPartB As String
        ...
            ...
            ' Loop through the rest
            Do While Not EOF(f)
                ' Read a line
                Line Input #f, strLine
                ' Get out if we reached END
                If strLine = "END" Then
                    Exit Do
                End If
                ' Move to next row
                r = r + 1
                ' Enter filename in column A
                wsh.Cells(r, 1) = strFilename
                ' Split line
                arrParts = Split(strLine, vbTab)
                strPartA = arrParts(2) ' 3rd part
                strPartB = arrParts(5) ' 6th part
                ' Do something with these parts
                ...
            Loop
            ...

    This is just an example, it doesn't actually do anything. Please note that the elements of the array arrParts are numbered starting at 0: arrParts(0) is the first element, arrParts(1) the second element etc.

    You'll have to modify the above code to suit your needs.


    Regards, Hans Vogelaar

    • Marked as answer by STARFALLS Friday, January 11, 2013 12:26 AM
    Friday, January 11, 2013 12:17 AM
  • Which column is arrParts(5) added to? Let's say it is column D (column 4). You'd have something like

            wsh.Cells(r, 4).Value = arrParts(5)

    Add the following below it:

            If ArrParts(10) <> "" Then
                wsh.Cells(r, 4).Interior.Color = vbYellow
            End If


    Regards, Hans Vogelaar

    • Marked as answer by STARFALLS Sunday, January 13, 2013 11:17 PM
    Sunday, January 13, 2013 10:38 AM
  • You always come thru for me. Thanks a million times over !!!!!!

    Jim Neely

    • Marked as answer by STARFALLS Friday, January 11, 2013 12:26 AM
    Friday, January 11, 2013 12:26 AM
  • WOW You make it look so easy.... Thank You once again !

    Jim Neely

    • Marked as answer by STARFALLS Sunday, January 13, 2013 11:17 PM
    Sunday, January 13, 2013 11:17 PM

All replies

  • Every file is allocated in memory (RAM) you can reed it on Wikipedia , but i fell that you want to do something with this file

    and this is not academic conversations - do you?


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, January 10, 2013 6:43 AM
    Answerer
  • Hi Starfalls

    "STARFALLS" schrieb im Newsbeitrag news:8eea8525-071a-444d-9cba-9a4b9e33b5c8@communitybridge.codeplex.com...

    How does it work? I need to understand what goes on when you open a
    text file and then want to work with it.
    The open command loads the text file into memory ??? Is this correct ?
    how can you work with the records of that file. Is each record a string
    or is the record an array ??

    If you want to use a text file like you would use a table in a database then I recommend to access the file by DAO and the appropriate driver. Then you can work through the "records" as you would work with a table. This way you only keep the current record in memory.

    If you want to do it in VBA directly with the Open Method the file is not read completely but only the record that you read into a variable (with Get for example).

    Maybe it would be better if you would describe what exactly you want to do and where your concerns in regard of the memory are.

    Henry

    Thursday, January 10, 2013 10:29 AM
  • I am trying to figure out how I can via VBA open a text file and use the records and how to used a word within that record. I am writing a program that I want to open a text file and either put the whole text file in a spreadsheet or be able  to pick a record then a word from that record and put that on a spreadsheet.

    Jim Neely

    Thursday, January 10, 2013 11:41 PM
  • Thanks but I understand what a record is and I know the file I am dealing with is TAB delimited. What I need is the VBA to manipulate the file to use one or all records in a file and to pull a word out of a record if I need it. My first though is to read a text file into an array but I know very little about VBA and how it handles text files. Any help or code would be greatly appreciated.

    Jim Neely

    Thursday, January 10, 2013 11:47 PM
  • If you look at the reply marked as Answer in your recent thread import multiple text files to a spreadsheet using array's, the _chr.txt text file is read using

            ...
            ' Loop through the rest
            Do While Not EOF(f)
                ' Read a line
                Line Input #f, strLine
                ' Get out if we reached END
                If strLine = "END" Then
                    Exit Do
                End If
                ' Move to next row
                r = r + 1
                ' Enter filename in column A
                wsh.Cells(r, 1) = strFilename
                ' Enter line in column B
                wsh.Cells(r, 2) = strLine
            Loop
            ...

    Instead of dumping the entire strLine into a cell (to be parsed later on using TextToColumns), you could use the Split function to parse strLine and to extract parts from it:

        Dim arrParts
        Dim strPartA As String
        Dim strPartB As String
        ...
            ...
            ' Loop through the rest
            Do While Not EOF(f)
                ' Read a line
                Line Input #f, strLine
                ' Get out if we reached END
                If strLine = "END" Then
                    Exit Do
                End If
                ' Move to next row
                r = r + 1
                ' Enter filename in column A
                wsh.Cells(r, 1) = strFilename
                ' Split line
                arrParts = Split(strLine, vbTab)
                strPartA = arrParts(2) ' 3rd part
                strPartB = arrParts(5) ' 6th part
                ' Do something with these parts
                ...
            Loop
            ...

    This is just an example, it doesn't actually do anything. Please note that the elements of the array arrParts are numbered starting at 0: arrParts(0) is the first element, arrParts(1) the second element etc.

    You'll have to modify the above code to suit your needs.


    Regards, Hans Vogelaar

    • Marked as answer by STARFALLS Friday, January 11, 2013 12:26 AM
    Friday, January 11, 2013 12:17 AM
  • You always come thru for me. Thanks a million times over !!!!!!

    Jim Neely

    • Marked as answer by STARFALLS Friday, January 11, 2013 12:26 AM
    Friday, January 11, 2013 12:26 AM
  • Hans, help me. I want to be able to change the color of a cell if there is something in another cell. this is for this project. this will happen when the text file line is read in. a comparsion between strPartB = arrParts(5) strPartB = arrParts(10) is made. if 10 has data in it, I want to change the cell color on the spreadsheet to Yellow for strPartB = arrParts(5).

    Jim Neely

    Sunday, January 13, 2013 12:10 AM
  • In which columns do these data end up?

    Regards, Hans Vogelaar

    Sunday, January 13, 2013 12:12 AM
  • the arrparts(10) is only for looking at. If the cell has any information then the cell where arrparts(5) is added is to be changed to yellow. I am doing this because arrparts(10) is not on the spreadsheet so i need someway to indicate to the user that arrparts(10) is not blank.

    FYI arrparts(10) data is a number indicating  above tollerence

          arrparts(5) data is the measurement

    so if a measurement "arrpart(5)" is above tollerence, the amount above the tollerence is in "arrpart(10)". for what ever reason arrpart(10) is not part of the spreadsheet so I decided to, at the least, indicate with a cell color that the tollerence has been exceeded.


    Jim Neely

    Sunday, January 13, 2013 6:34 AM
  • Which column is arrParts(5) added to? Let's say it is column D (column 4). You'd have something like

            wsh.Cells(r, 4).Value = arrParts(5)

    Add the following below it:

            If ArrParts(10) <> "" Then
                wsh.Cells(r, 4).Interior.Color = vbYellow
            End If


    Regards, Hans Vogelaar

    • Marked as answer by STARFALLS Sunday, January 13, 2013 11:17 PM
    Sunday, January 13, 2013 10:38 AM
  • WOW You make it look so easy.... Thank You once again !

    Jim Neely

    • Marked as answer by STARFALLS Sunday, January 13, 2013 11:17 PM
    Sunday, January 13, 2013 11:17 PM