Find and Replace a column in text file
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

Thread: Find and Replace a column in text file

  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Find and Replace a column in text file

    I have 100sof flat files that have lots of data,every line of the file is having a space in between each column value,the first line is a header for all the columns,i want to modify all the date values (column named start date or date or anything) for a single column,how do i do that?
    I tried split function but i am not able to do it,i need the syntax and a sample code...

    Thanks

  2. #2
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,784

    Re: Find and Replace a column in text file

    One way would be to read the file line by line or you could use the readalllines method
    In either case once you get a line you need to use split() to split the line into its columns and then modify the column(s) you need and write the lines to a new file as you go.

    Have you tried search for readline or readalllines and/or split in VB.Net. There should be sample code in your online help and many examples already posted on the web
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Sep 2013
    Posts
    6

    Re: Find and Replace a column in text file

    There are no examples on the web so far and yes i tried readalllines,split,but i am not able to do it,i need some code,can you help?

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,784

    Re: Find and Replace a column in text file

    no examples? Took me 2 seconds to find an example for readalllines http://msdn.microsoft.com/en-us/libr...code-snippet-1
    Code:
    Imports System
    Imports System.IO
    
    Public Class Test
        Public Shared Sub Main()
            Dim path As String = "c:\temp\MyTest.txt" 
            Dim sw As StreamWriter
    
            ' This text is added only once to the file. 
            If File.Exists(path) = False Then 
    
                ' Create a file to write to. 
                Dim createText() As String = {"Hello", "And", "Welcome"}
                File.WriteAllLines(path, createText)
            End If 
    
            ' This text is always added, making the file longer over time 
            ' if it is not deleted. 
            Dim appendText As String = "This is extra text" + Environment.NewLine
            File.AppendAllText(path, appendText)
    
            ' Open the file to read from. 
            Dim readText() As String = File.ReadAllLines(path)
            Dim s As String 
            For Each s In readText
                Console.WriteLine(s)
            Next 
        End Sub 
    End Class
    A couple more seconds to find and example of split http://msdn.microsoft.com/en-us/library/b873y76a.aspx
    Code:
    Public Class SplitTest
        Public Shared Sub Main()
            Dim words As String = "This is a list of words, with: a bit of punctuation" + _
                                  vbTab + "and a tab character." 
            Dim split As String() = words.Split(New [Char]() {" "c, ","c, "."c, ":"c, CChar(vbTab) })
    
            For Each s As String In  split
                If s.Trim() <> "" Then
                    Console.WriteLine(s)
                End If 
            Next s
        End Sub 'Main
    End Class 'SplitTest
    And a little common sense to put them together
    Code:
     ' Open the file to read from. 
            Dim readText() As String = File.ReadAllLines(path)
            Dim s As String 
            Dim words() As String
            For Each s In readText
                Words=s.Split(" ")
                'Words will contain the column values for the current line at this point so here is where you would need to do something with the data and write to a  new file
            Next
    If you are still having problems then post your code and tell us what problem you are having along with any error messages you may be getting
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Sep 2013
    Posts
    6

    Re: Find and Replace a column in text file

    I did find examples like the above,here is the problem with my code..its not going into the loop to replace the date column into current date..i think the split function is not been used in the right way by this code or something


    Code:
          Dim SourceFileName As String = "c:\test\adjtime.txt"
    
            Dim TargetFileName As String = "c:\test\adjtime1.txt"
    
            Dim sAllLines As String()
    
            Dim sCurrentDate As String = DateTime.Now.ToString("yyyy-MM-dd")
    
    
            If File.Exists(SourceFileName) Then
    
     
                sAllLines = File.ReadAllLines(SourceFileName)
    
                Label1.Text = sAllLines.Length.ToString()
    
                Dim tCurrentDate As String = DateTime.Now.ToString("yyyy-MM-dd")
    
                Dim strdt As String
     
     
                For i As Integer = 1 To sAllLines.Length - 1
    
                    If sAllLines(i).Length > 4 Then
    
                        strdt = sAllLines(i).IndexOf(i).ToString()
    
                        MsgBox(strdt)
    
                        If IsDate(strdt) Then
    
                            sAllLines(i) = sAllLines(i).Replace(strdt, tCurrentDate)
    
                            MsgBox(sAllLines(i))
    
                        End If
    
                    End If
    
                Next
    
    
                'For i As Integer = 1 To sAllLines.Length - 1
                '    sAllLines(i) = Class1.UpdateCurrentDate(sAllLines(i))
                'Next
    
    
                If sAllLines IsNot Nothing Then
    
                    File.WriteAllLines(TargetFileName, sAllLines)
    
                End If
    
            End If


    i tried writing this using a function in a class(which is commented right now)



    Code:
        Public Shared Function UpdateCurrentDate(ByVal str As String) As String
    
            Dim sCurrentDate As String = DateTime.Now.ToString("yyyy-MM-dd")
    
            Dim strdt As String
    
            Dim sp As String() = str.Split(" "c)
    
            If sp.Length > 4 Then
    
                strdt = sp(3).ToString()
    
                If IsDate(strdt) Then
    
                    str = str.Replace(strdt, sCurrentDate)
    
                End If
    
            End If
    
            Return str
    
        End Function
    
        Public Shared Function IsDate(ByVal sdate As String) As Boolean
    
            Try
                Dim dt As DateTime = DateTime.Parse(sdate)
    
                If dt <> DateTime.MinValue AndAlso dt <> DateTime.MaxValue Then
    
                    Return True
                End If
    
                Return False
    
            Catch
    
    
                Return False
            End Try
    
        End Function
    Last edited by HanneSThEGreaT; September 25th, 2013 at 10:44 AM. Reason: code tags

  6. #6
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,784

    Re: Find and Replace a column in text file

    What happens when you uncomment the code for the loop that calls the function.
    Have you tried setting a break point and stepping through the code at runtime?

    Also note that STR() is a VB function and really should not be used as a variable name
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Sep 2013
    Posts
    6

    Re: Find and Replace a column in text file

    Yea i did change the variable to to st and tried to step thru the code,it just doesnt come into this loop below

    If sp.Length > 4 Then

    strdt = sp(3).ToString()

    If IsDate(strdt) Then

    st = st.Replace(strdt, sCurrentDate)

    End If

    End If


    I created a function sdate


    Public Shared Function IsDate(ByVal sdate As String) As Boolean

    Try
    Dim dt As DateTime = DateTime.Parse(sdate)

    If dt <> DateTime.MinValue AndAlso dt <> DateTime.MaxValue Then

    Return True
    End If

    Return False

    Catch

    Return False
    End Try

    End Function

    I tried this code instead of the function

    Dim tCurrentDate As String = DateTime.Now.ToString("yyyy-MM-dd")

    Dim strdt As String



    For i As Integer = 1 To sAllLines.Length - 1

    If sAllLines(i).Length > 4 Then

    strdt = sAllLines(i).IndexOf(i).ToString()

    'MsgBox(strdt)

    If IsDate(strdt) Then

    sAllLines(i) = sAllLines(i).Replace(strdt, tCurrentDate)

    'MsgBox(sAllLines(i))

    End If

    End If

    Next

    still doesnt work


    All i am trying to do is

    Open a FILE for read only and change all the date column data(in a text file)into current date and save it as a new file

    The ideal way to do would be to use the first row which has the headings and make a note of the column number that has Dates,like column 5 ,6 etc and from the next row onwards change all the columns that have dates to current date..

    Opening and closing the file part is done but looping thru is where i get stuck,plus the split function when it returns using a array is something i am having trouble understanding
    Last edited by shuk39; September 25th, 2013 at 01:25 PM.

  8. #8
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,784

    Re: Find and Replace a column in text file

    Quote Originally Posted by shuk39 View Post
    Yea i did change the variable to to st and tried to step thru the code,it just doesnt come into this loop below
    Code:
          If sp.Length > 4 Then
    
                strdt = sp(3).ToString()
    
                If IsDate(strdt) Then
    
                    st = st.Replace(strdt, sCurrentDate)
    
                End If
    
            End If
    That is not a loop ???

    I created a function sdate


    I tried this code instead of the function
    Code:
                Dim tCurrentDate As String = DateTime.Now.ToString("yyyy-MM-dd")
    
                Dim strdt As String
    
    
    
                For i As Integer = 1 To sAllLines.Length - 1
    
                    If sAllLines(i).Length > 4 Then
    
                        strdt = sAllLines(i).IndexOf(i).ToString()
    
                        'MsgBox(strdt)
    
                        If IsDate(strdt) Then
    
                            sAllLines(i) = sAllLines(i).Replace(strdt, tCurrentDate)
    
                            'MsgBox(sAllLines(i))
    
                        End If
    
                    End If
    
                Next
    still doesnt work
    What does doesn't work mean? What does it do?
    How many columns are in each line of the file?

    I do not see anywhere that you are using split to pull out the columns in that most recent post

    is SP the result of having used split on the var that holds the current line?

    Could you show a couple of sample lines from the file?

    Use code tags when you post code to keep the formatting and make it more readable
    Always use [code][/code] tags when posting code.

  9. #9
    Join Date
    Sep 2013
    Posts
    6

    Re: Find and Replace a column in text file

    Hi Datamiser,

    Actually it finally works,i dug thru some examples of split for flatfile reads and here is the final code,thanks a lot for helping me anyways



    Dim SourceFileName As String = "c:\test.txt"
    Dim TargetFileName As String = "c:\test1.txt"

    Dim sAllLines As String()

    If File.Exists(SourceFileName) Then
    sAllLines = File.ReadAllLines(SourceFileName)

    For i As Integer = 1 To sAllLines.Length - 1
    sAllLines(i) = Class1.UpdateCurrentDate(sAllLines(i))
    Next

    If sAllLines IsNot Nothing Then
    File.WriteAllLines(TargetFileName, sAllLines)
    End If
    End If

    Public Shared Function UpdateCurrentDate(ByVal str As String) As String

    Dim sCurrentDate As String = DateTime.Now.ToString("yyyy-MM-dd")

    Dim strdt As String

    Dim sp As String() = str.Split(" "c)

    If sp.Length > 4 Then
    strdt = sp(4).ToString()
    MsgBox(strdt)
    If IsDate(strdt) Then
    str = str.Replace(strdt, sCurrentDate)
    End If
    End If

    Return str

    End Function



    Public Shared Function IsDate(ByVal sdate As String) As Boolean

    Try
    Dim dt As DateTime = DateTime.Parse(sdate)
    If dt <> DateTime.MinValue AndAlso dt <> DateTime.MaxValue Then
    Return True
    End If
    Return False
    Catch
    Return False
    End Try
    End Function

    However i have one problem here,i am validating the column date for dates,but how to pick a column whose heading is date?that is look at the first row and have a pointer for that column and then change the column data for those columns that are named Date...can you help on that part alone?

  10. #10
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,784

    Re: Find and Replace a column in text file

    Well you would just need to use split on the first line and then check each resulting item to see if it had the word date in it and if so set a variable to the index of that column.
    You would then use that variable as the index for testing/replacing in the additional lines,

    If you may have more than one of these columns then you may need to use more vars. I would suggest an integer array for this. The first element could hold the number of columns to check and the others elements 1-? would hold the index of that column and then the code could use a for next loop to check the columns

    Lets say the match found 3 columns the 2nd, 5th and 7th column the resulting integer array may look something like this 3,2,5,7

    your loop may look something like
    Code:
    For x =1 to MyArray(0)
           Columns(MyArray(x))="Something"
    Next
    So it would loop through the array holding the indexs and then would assign a value to column(2), column(5) and column(7)
    Always use [code][/code] tags when posting code.

  11. #11
    Join Date
    Sep 2013
    Posts
    6

    Re: Find and Replace a column in text file

    Doing the first part is easy andi already have a function for that,its like a counter that stores the column number which has date as its name,But being a text file i dont know how i can change the values of the columns(starting from second line)data..how to point to those?its like like

    Columns(myarray(4)) will refer to the column 4..you know

  12. #12
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,784

    Re: Find and Replace a column in text file

    Quote Originally Posted by shuk39 View Post
    Doing the first part is easy andi already have a function for that,its like a counter that stores the column number which has date as its name,But being a text file i dont know how i can change the values of the columns(starting from second line)data..how to point to those?its like like

    Columns(myarray(4)) will refer to the column 4..you know
    No Columns(myarray(4)) will refer to the column whose number is in MyArray(4) so if the value in MyArray(4) is 9 then that code would refer to column(9)

    You would simple read the file as you are already doing. You then check the first line which would be sAllLines(0) for the names of the columns and assign your indexes
    You would then begin your loop that goes through each line and inside that loop would be a loop like I described which would cycle through the columns needed. In that loop you would do the tests and replacements

    Then of course you need to write the data to a new file and it should be the way you want it.
    Always use [code][/code] tags when posting code.

Posting Permissions

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


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center