• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48
  • Last Modified:

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
0
phdit
Asked:
phdit
3 Solutions
 
oBdACommented:
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 SamariyaIT 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 PrewCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now