Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help using Find and Replace

    I need help, please!

    I have a text file where each line starts with a date followed by data like this:

    2014-01-01 23 45 67 89 45 34
    2014-01-01 56 78 01 94 67 34
    2014-01-01 66 77 34 09 54 21

    The date and data is received from another system and what happens is that sometimes the DATE are wrong and the data this is just 00 which is normal behavior. like this:

    2010-01-01 00 00 00 00 00 00

    My problem is that this text file then is exported into another system and that system sorts the data based on date.

    What I would like to do is to read out the first date in the file and then replace all the other dates with that one.

    I don't think that this is a problem for someone who knows how to write script files but unfortunately I don't have this knowledge.

    I have been playing with FNR but I havenít been able to figure out how to do this.

    Please Help

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    The problem with find and replace is you need to know what you are finding and if the date is not what you expect it won't replace. To get around this you would use a regular expression search where you define a pattern to find / replace.

    This line should work for finding the date section: fnr.exe --cl --find "^\d{4)-\d{2}-\d{2}" --replace "Text To Replace" --useRegEx
    The regex command looks on the beginning of each line for 4 digits, a minus, 2 digits, a minus and 2 digits.

    Is the date in the file always the same, for that file, except for invalid dates?
    How do you work out what date to use as the replace value?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot Paul, I really appreciate your help.

    Some background to the problem:
    The data in the text file is collected from 8 different sources (data collectors) using UDP as protocol. When a data collector doesn’t answer in time or the transmission is lost for some reason the system just use a default date (2010-01-01) and sets all data to “00”, which is correct.

    At 00:00 hrs. every night a new text file is created and the old one is closed. The text file is then imported in to a database and if I then have lost data it is always dated 2010-01-01, now I would like to know when the data was lost to enable some more statistics from the system.

    The first line of the text file is always data collected from a local data collector and to make it less complicated I decided to assume that the first date in the text file always are correct, i.e. I use this as reference. I have done this because I have never seen any issues with this collector.

    So I would like to read out the date of the first line and replace all the other dates with that one before it is imported into the database.

    Regards
    Marcus

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    I would read a few dates from the file and see if they are not 2010-01-01, then use that as the replacement value. Unfortunately FNR won't do that for you so it's probably a bit of VBS or Powershell. Which would you prefer?

    cheers, Paul

  5. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again Paul,
    I think Powershell would be good, I can see that I have that installed and I guess I can understand and learn from you code, I really would like to learn how to do this.

    I am very gratefull for your help.

    Regards
    Marcus

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Marcus,

    Here's some PowerShell that should do the trick:
    Code:
    #Version: 1.0
    
    $Headers = "A","B","C","D","E","F","G" #One letter for each column!
    $FileLocation = "G:\BEKDocs"           #Change to your location!
    $FileFilter = "BlueBarbTestData.txt"   #Change to your filename filter
    
    $FileList = Get-ChildItem $FileLocation -Filter $FileFilter 
    
    ForEach ($FSpec in $FileList) {
      #--- Load File as CSV and add Headers to allow Sorting ---
      $X = import-csv -Path $FSpec.FullName  -Delimiter ' ' -Header $Headers
      $CurDate = $X.A[0]
    
      # Reload File as Text to use replace option
    
      (get-content $FSpec.FullName) -replace '2010-01-01' , $CurDate | `
       Set-Content $FSpec.FullName
    }
    Change the variables at the top to your data! Note if you have multiple filenames that follow a pattern you can use wildcards in the $FileFilter variable and it will process them all. If you want to output to a different file just let me know and I'll adjust the code.

    Test File:
    Code:
    2014-01-01 23 45 67 89 45 34
    2014-01-01 56 78 01 94 67 34
    2010-01-01 00 00 00 00 00 00
    2010-01-01 00 00 00 00 00 00
    2014-01-01 66 77 34 09 54 21
    2010-01-01 00 00 00 00 00 00
    Results File:
    Code:
    2014-01-01 23 45 67 89 45 34
    2014-01-01 56 78 01 94 67 34
    2014-01-01 00 00 00 00 00 00
    2014-01-01 00 00 00 00 00 00
    2014-01-01 66 77 34 09 54 21
    2014-01-01 00 00 00 00 00 00
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    I'm not very good at PS but something like this should work. Note: you have to change the file names.

    The odd line at the beginning is to retain the CRLF from your file as Get-Content removes them. Alternatively you can read all the $lines into a new variable with CRLF in between, then Set-Content with the new variable.

    cheers, Paul

    Code:
    $data = [string]::join([environment]::newline, (Get-Content 'D:\tmp\Test.txt'))
    ForEach ($line in $data)
    {
      if ($line -notcontains '2010-01-01')
      {
        Write-Host $line
        $dateval=$line.Substring(0,10)
      }
    }
    $dateval
    $data = $data -replace '2010-01-01', $dateval
    Write-Host $data
    
    Set-Content 'D:\tmp\Test1.txt' $data -Force
    [Edit] 1 minute!

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    RG, I see you assume the first date is always valid. Brave!

    cheers, Paul

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    No assumptions here!

    Quote Originally Posted by bluemarb View Post
    What I would like to do is to read out the first date in the file and then replace all the other dates with that one.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    RG, why didn't your Get-Content drop the CRLF?

    cheers, Paul

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    You got me! I didn't know it did! I haven't had it do that in other examples I've posted here.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    Maybe these are to blame in your code: [string]::join([environment]::newline?

    I reread the help files on get & set content and didn't see any mention of it stripping carriage returns. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Here's the blurb on that particular problem. I added the extra code to work around the missing CRLF.
    Must be where / how you use the replace that makes the difference.

    cheers, Paul

  14. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    I noted the date on that article is 2006! That was PS 1.0, maybe a bug/feature was fixed in a later version?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #15
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    I'm on W8.1 so if it was fixed it should have been by now and my testing showed the problem. Try my code with the first line as "$data = Get-Content 'D:\tmp\Test.txt'"

    cheers, Paul

Page 1 of 2 12 LastLast

Posting Permissions

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