Solved

Text file manipulation

Posted on 2016-08-09
3
38 Views
Last Modified: 2016-08-10
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
Comment
Question by:phdit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 85

Accepted Solution

by:
oBdA earned 250 total points
ID: 41748795
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
 
LVL 10

Assisted Solution

by:Prakash Samariya
Prakash Samariya earned 125 total points
ID: 41748804
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
 
LVL 55

Assisted Solution

by:Bill Prew
Bill Prew earned 125 total points
ID: 41748818
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question