Solved

Text file manipulation

Posted on 2016-08-09
3
36 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
3 Comments
 
LVL 84

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 9

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 53

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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