Extract text file data as column and write to excel

Question

0.00/5 (No votes)

See more:

Hi All

Below is the data I am getting in NewLines

-Ignored:31,Modified src data,all *file,MINOSFIC/UTMNUP10
-Ignored:33,Modified src & tgt data,all *file,MINOSFIC/UVEGAP10
-Ignored:92,Synchro is running,*file,MINOSFIC/VM010P50
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010U51
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010U52
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010U53
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010U54
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010U55
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010U56
-Ignored:01,Object hold (synchro),*file,MINOSFIC/VM010Z59
-Ignored:18,Object hold,*file,MINOSFIC/VM011P50
-Ignored:18,Object hold,*file,MINOSSVG/S100001154
-Ignored:18,Object hold,*file,MINOSSVG/S200002683
-Ignored:18,Object hold,*file,W2257993/MINOSWEB2

I am trying to put the data in csv but it is only printing numbers in the file.

What I have tried:

PowerShell

$allIGfiles = Get-ChildItem -Path 'C:\LG2' -Recurse -Filter "*LG_VFN*"

foreach($file in $allIGfiles)
{
    $filename = $file.FullName

$data = Get-Content $filename | Select -SkipLast 1
$Lines = @()
foreach ($line in $data){ 
if($line -match "Ignored")
{
    $Lines+=$line
}
}

$NewLines = @($Lines | % { ($_  -replace "\s{2,}",",") -replace "(\d) ", '$1,'} | Select-Object -SkipLast 1)

$NewLines | Export-Csv 'c:\file-LG.csv' -append -NoTypeInformation

Posted 4-Oct-21 23:33pm

Empty Coder

Updated 5-Oct-21 3:45am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2021-10-05T00:30:00

Solution 1

I just tried your code and the output was:

-Ignored:32 Modified tgt data        all *file   MANHIN_SV/MCRHA52268
-Ignored:32 Modified tgt data        all *file   MANHIN_SV/MCRHA52269
-Ignored:32 Modified tgt data        all *file   MANHIN_SV/MZRHA88827

So whatever you are doing, you must be running some different code.

Posted 5-Oct-21 0:30am

Richard MacCutchan

Updated 5-Oct-21 0:32am

v2

Comments

Empty Coder 5-Oct-21 9:09am

yes. but I want to put it in csv/excel in the format which I mentioned below that.

Richard MacCutchan 5-Oct-21 9:25am

Well you need to fix the code you are using first. Until you can extract the correct data, you cannot reformat into CSV

Empty Coder 5-Oct-21 9:35am

I have updated the code and new data which i am getting, but not able to put it in csv

Richard MacCutchan 5-Oct-21 9:46am

See below.

Richard MacCutchan · Answer 2 · 2021-10-05T03:45:00

Solution 2

Try this:

PowerShell

$data = Get-Content 'file.txt' | Select -SkipLast 1
$Lines = @("Error Code, type, object")
foreach ($line in $data){ 
    if($line -match "Ignored")
    {
        $parts = $data[0].Split(" ", [System.StringSplitOptions]::RemoveEmptyEntries)
        $field1 = @($parts[0], $parts[1], $parts[2], $parts[3]) -join " "
        $fields = @($field1, $parts[5], $parts[6]) -join ", "
        $Lines += $fields
    }
}
$Lines

The output will be in .csv form, like:

Error Code, type, object
-Ignored:32 Modified tgt data, *file, MANHIN_SV/MCRHA52268
-Ignored:32 Modified tgt data, *file, MANHIN_SV/MCRHA52268
-Ignored:32 Modified tgt data, *file, MANHIN_SV/MCRHA52268

Posted 5-Oct-21 3:45am

Richard MacCutchan

Comments

Empty Coder 5-Oct-21 10:23am

@Richard MacCutchan: This didn't worked because the occurrence of the values can be at any position. can't specify exact number. like
-Ignored:33
Modified
src
&
tgt
data
all
*file
MINOSFIC/UVEGAP10

Richard MacCutchan 5-Oct-21 10:48am

Sorry, but we can only work with the information you give us. My solution is based on your original question which has now been changed considerably.

Empty Coder 5-Oct-21 10:50am

If the data is fixed position, then it would have been easy. the positions are not constant that's where i am facing issue and posted the issue.

Richard MacCutchan 5-Oct-21 10:52am

Well you forgot to mention that in your original question. So you need to find a way of identifying the separate parts from all the different combinations.

Empty Coder 5-Oct-21 11:08am

That's what i did and got this output mentioned in the question. but i am not getting why i am not able to write to csv