Solved

Text file manipulation

Posted on 2016-08-09
3
37 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 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 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 54

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
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…

733 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