-
September 23rd, 2013, 12:59 PM
#1
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
-
September 25th, 2013, 12:58 AM
#2
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.
-
September 25th, 2013, 08:16 AM
#3
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?
-
September 25th, 2013, 09:50 AM
#4
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.
-
September 25th, 2013, 10:10 AM
#5
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
-
September 25th, 2013, 11:34 AM
#6
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.
-
September 25th, 2013, 01:21 PM
#7
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.
-
September 25th, 2013, 01:29 PM
#8
Re: Find and Replace a column in text file
Originally Posted by shuk39
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.
-
September 25th, 2013, 01:50 PM
#9
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?
-
September 25th, 2013, 02:30 PM
#10
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.
-
September 25th, 2013, 03:17 PM
#11
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
-
September 25th, 2013, 05:05 PM
#12
Re: Find and Replace a column in text file
Originally Posted by shuk39
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|