Text file manipulation

Hi All,
I have the following text file which can contain thousands of lines each day.
Example

25,516582,B-003,C:\CAF\25-00000103\25-00000001.PDF

I need the script to read the first number 25 and the second number 516582 from each line (Thee numbers will be different on each line) and make the line look like below the only consistent thing about each line are the comma's.

25,516582,B-003,25_516582,25-00000103\25-00000001.PDF

Also need C:\CAF\ removing from each line

So from this
25,516582,B-003,C:\CAF\25-00000103\25-00000001.PDF
10,516983,B-003,C:\CAF\10-00000103\10-00000003.PDF
13,516972,N-102,C:\CAF\13-00000103\13-00000004.PDF
09,516940,N-102,C:\CAF\09-00000103\09-00000005.PDF

To This
25,516582,B-003,25_516582,25-00000103\25-00000001.PDF
10,516983,B-003,10_516983,10-00000103\10-00000003.PDF
13,516972,N-102,13_516972,13-00000103\13-00000004.PDF
09,516940,N-102,09_516940,09-00000103\09-00000005.PDF

Thanks

John H
phditAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
oBdAConnect With a Mentor Commented:
Something like this?
$OutFile = "C:\Temp\output.csv"
Import-Csv C:\Temp\input.csv -Header Col1, Col2, Col3, Col4 |
	Select-Object -Property Col1, Col2, Col3, @{n='Col4'; e={"$($_.Col1)_$($_.Col2)"}}, @{n='Col5'; e={$_.Col4 -replace [regex]::Escape('C:\CAF\'), ''}} |
	ConvertTo-Csv -NoTypeInformation |
	Select-Object -Skip 1 |
	ForEach-Object {$_.Replace('"', '')} |
	Set-Content -Path $OutFile

Open in new window

0
 
Prakash SamariyaConnect With a Mentor IT ProfessionalCommented:
wild guess, It is just a logic, you might need to check it!
(Import-Csv E:\Test.csv -Delimiter -Header ',') | ForEach-Object{

    if($_[3].StartsWith("C:\CAF\"))
    {
	  $_[3] = $_[3].Replace("C:\CAF\", "")
      $_[3] = $_[0]+","+$_[1]+","+$_[2]+","+$_[0]+"_"+$_[1]+","+$_[3];
	}
} | Export-Csv E:\Test.csv -Delimiter ',' 

Open in new window

0
 
Bill PrewConnect With a Mentor Commented:
For what it's worth, this one would be pretty easy in an old school BAT script also:

@echo off
setlocal EnableDelayedExpansion
(
  for /f "usebackq tokens=1-3* delims=," %%A in ("input.txt") do (
    set Col4=%%D
    echo %%A,%%B,%%C,%%A_%%B,!Col4:C:\CAF\=!
  )
) > "output.txt"

Open in new window

~bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.