Powershell script to convert text file to csv

spiceuser-319x7 · 2018-12-11T14:21:42.000Z

I have a text file which is generated every 24 hours in the following format

==================================================
Host Name :
IP Address :
Reply IP Address :
Succeed Count : 2
Failed Count : 0
Consecutive Failed Count:
Max Consecutive Failed Count:
Max Consecutive Failed Time:
% Failed : 0%
Last Ping Status : Succeeded
Last Ping Time : 6
Last Ping TTL : 60
Average Ping Time : 25
Description : Channels:ADR1-Location1-SB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On :
Minimum Ping Time : 6
Maximum Ping Time : 41
Order : 1

==================================================
Host Name :
IP Address :
Reply IP Address :
Succeed Count : 1
Failed Count : 1
Consecutive Failed Count: 1
Max Consecutive Failed Count: 1
Max Consecutive Failed Time: 12/11/2018 9:52:17 AM
% Failed : 50.00%
Last Ping Status : Succeeded
Last Ping Time : 5
Last Ping TTL : 60
Average Ping Time : 5
Description : Channels:ADR2-Location1-SB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On : 12/11/2018 9:52:17 AM
Minimum Ping Time : 5
Maximum Ping Time : 5
Order : 2

Similarly 29 other logs are created for the 29 other IP’s. However, i want this to formatted in the form of an csv file with the left hand column being the header for the csv. I am new to powershell but is there any way to format the text file to csv.

Thanks

rockn · 2018-12-11T14:31:13.000Z

That isn’t how a CSV is formatted so what you want will not work. What is this log coming from? You could probably just replicate something like this in Powershell that would give you more or better information.

Neally · 2018-12-11T14:31:18.000Z

What have you tried? Where are you stuck?

We’re happy to help if you post what you’ve tried so far?

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

Looks like it should be easy to split / delimit on “:”

spiceuser-319x7 · 2018-12-11T14:37:38.000Z

This log is coming from a pinging app in the form of a text file.

DoctorDNS · 2018-12-11T14:50:14.000Z

You could certainly do this and I have done similar things.

Basically, you’d use get-content to get the files then do a foreach on each line which filled in a hash table then at the end of each report turned the hash table to an object and emitted it.

Evan7191 · 2018-12-11T14:53:21.000Z

You could do something like this:

$Header = "Host Name,IP Address,Reply IP Address,Succeed Count,Failed Count,Consecutive Failed Count,Max Consecutive Failed Count,Max Consecutive Failed Time,% Failed,Last Ping Status,Last Ping Time,Last Ping TTL,Average Ping Time,Description,Last Succeed On,Last Failed On,Minimum Ping Time,Maximum Ping Time,Order"
$Header | Export-Csv -Path Drive:\Path\File.csv -Append -NoTypeInformation
$Text = Get-Content Drive:\Path\File.txt
$Text = $Text -ireplace '^[^:]*:\s*','' -ireplace '\s*$',','
$Text = ($Text | Out-String) -ireplace '\r\n','' | Export-Csv -Path Drive:\Path\File.csv -Append -NoTypeInformation

This code will create a header based on the property names that you provided and then turn the rest of the text into a comma-separated list that can be exported to a CSV file.

spiceuser-319x7 · 2018-12-11T14:54:39.000Z

Thank you.c

spiceuser-319x7 · 2018-12-11T15:22:25.000Z

Hi Evan, i GOT THE FOLLOWING ERROR

Get-Content : Cannot find path ‘C:\Users\PLahkar\Desktop\Ping_Success.txt’ because it does not exist.
At C:\Users\PLahkar\Desktop\Ping_Success\Ping.ps1:3 char:9

$Text = Get-Content C:\Users\PLahkar\Desktop\Ping_Success.txt
CategoryInfo : ObjectNotFound: (C:\Users\PLahka…ing_Success.txt:String) [Get-Content], ItemNotFoundException
FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand

@techadmin8

Neally · 2018-12-11T15:23:57.000Z

Make sure the path is right and the account you run it as has the right rights to access it.

spiceuser-319x7:

Get-Content : Cannot find path ‘C:\Users\PLahkar\Desktop\Ping_Success.txt’ because it does not exist.

PowerShell usually has pretty good error messages, that sounds like it can not find the file.

spiceuser-319x7 · 2018-12-11T15:47:11.000Z

I was able to run it but it only sent out length details instead of what I wanted. What is the error?

Length 304 7381 304 7381

Neally · 2018-12-11T15:49:50.000Z

spiceuser-319x7:

I was able to run it but it only sent out length details instead of what I wanted. What is the error?

Length 304 7381 304 7381

That is not an error. Looks like you are trying to export a String (A string only has the length proprty) to a CSV. that does not really work, it needs to be in a CSV format.

Easiest would probably be a calculated value or a custom object.

Evan7191 · 2018-12-11T16:01:17.000Z

spiceuser-319x7:

I was able to run it but it only sent out length details instead of what I wanted. What is the error?

Length 304 7381 304 7381

You can use Out-File instead of Export-CSV.

$Header = "Host Name,IP Address,Reply IP Address,Succeed Count,Failed Count,Consecutive Failed Count,Max Consecutive Failed Count,Max Consecutive Failed Time,% Failed,Last Ping Status,Last Ping Time,Last Ping TTL,Average Ping Time,Description,Last Succeed On,Last Failed On,Minimum Ping Time,Maximum Ping Time,Order"
$Header | Out-File -Path Drive:\Path\File.csv
$Text = Get-Content Drive:\Path\File.txt
$Text = $Text -ireplace '^[^:]*:\s*','' -ireplace '\s*$',','
$Text = ($Text | Out-String) -ireplace '\r\n','' | Out-File -Path Drive:\Path\File.csv -Append

spiceuser-319x7 · 2018-12-11T16:09:51.000Z

Hi, I was able to get something,but it is not the way I wanted it and only I could get for one log instead of the 31 logs that I have in the text file

The csv file is in the following format that was generated

==================================================,xxxxxxx,xxxxxxxx,xxxxxx,2,0,0%,Succeeded,6,60,25,Channels:ADR1-Location1-SB,12/11/2018 9:52:47 AM,6,41,1,==================================================,============================

@techadmin8

Evan7191 · 2018-12-11T16:28:29.000Z

$Header = "Host Name,IP Address,Reply IP Address,Succeed Count,Failed Count,Consecutive Failed Count,Max Consecutive Failed Count,Max Consecutive Failed Time,% Failed,Last Ping Status,Last Ping Time,Last Ping TTL,Average Ping Time,Description,Last Succeed On,Last Failed On,Minimum Ping Time,Maximum Ping Time,Order"
$Header | Out-File -Path Drive:\Path\File.csv
$Files = Get-ChildItem -Path Drive:\PathToLogFiles -Include *.txt
ForEach ($File in $Files) {
    $Text = Get-Content $File.FullName
    $Text = $Text -ireplace '^[^:]*:\s*','' -ireplace '\s*$',',' -ireplace '=',''
    $Text = ($Text | Out-String) -ireplace '\r\n','' | Out-File -Path Drive:\Path\File.csv -Append
}

This will look for a directory where the log files are, and then process each one and append the results to the same CSV file. This will also remove the lines created by the repeating equals signs (=).

spiceuser-319x7 · 2018-12-11T16:41:03.000Z

Thanks this works fine. However, is it possible to include each IP address in a separate row in the CSV file?

Currently, all the IP’S and their associated details are in a single row as shown below

xxxxxxxx,xxxxxxx,xxxxxxxxx,2,0,0%,Succeeded,6,60,25,Channels:ADR1-Location1-SB,12/11/2018 9:52:47 AM,6,41,1, xxxxxxxx,xxxxxxx,xxxxxxxxx ,1,1,1,1,12/11/2018 9:52:17 AM,50.00%,Succeeded,5,60,5,Channels:ADR2-Location1-SB,12/11/2018 9:52:47 AM,12/11/2018 9:52:17 AM,5,5,2, xxxxxxxx,xxxxxxx,xxxxxxxxx ,2,0,0%,Succeeded,12,60,25,Channels:ADR3-Location1-NB,12/11/2018 9:52:47 AM,12,38,3,

@techadmin8

Evan7191 · 2018-12-11T18:51:04.000Z

spiceuser-319x7:

Thanks this works fine. However, is it possible to include each IP address in a separate row in the CSV file?

Currently, all the IP’S and their associated details are in a single row as shown below

xxxxxxxx,xxxxxxx,xxxxxxxxx,2,0,0%,Succeeded,6,60,25,Channels:ADR1-Location1-SB,12/11/2018 9:52:47 AM,6,41,1, xxxxxxxx,xxxxxxx,xxxxxxxxx ,1,1,1,1,12/11/2018 9:52:17 AM,50.00%,Succeeded,5,60,5,Channels:ADR2-Location1-SB,12/11/2018 9:52:47 AM,12/11/2018 9:52:17 AM,5,5,2, xxxxxxxx,xxxxxxx,xxxxxxxxx ,2,0,0%,Succeeded,12,60,25,Channels:ADR3-Location1-NB,12/11/2018 9:52:47 AM,12,38,3,

Yes, it’s possible, assuming that you have the IP somewhere. I can’t give you specific code for it, because you have not provided an example of how you have the IP address.

spiceuser-319x7 · 2018-12-11T18:58:50.000Z

The IP’s are in sequential order as shown below. We have the list that is generated as shown below

==================================================
Host Name : 10.40.65.211
IP Address : 10.40.65.211
Reply IP Address : 10.40.65.211
Succeed Count : 2
Failed Count : 0
Consecutive Failed Count:
Max Consecutive Failed Count:
Max Consecutive Failed Time:
% Failed : 0%
Last Ping Status : Succeeded
Last Ping Time : 6
Last Ping TTL : 60
Average Ping Time : 25
Description : Channels:ADR1-Location1-SB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On :
Minimum Ping Time : 6
Maximum Ping Time : 41
Order : 1

==================================================
Host Name : 10.40.65.231
IP Address : 10.40.65.231
Reply IP Address : 10.40.65.231
Succeed Count : 1
Failed Count : 1
Consecutive Failed Count: 1
Max Consecutive Failed Count: 1
Max Consecutive Failed Time: 12/11/2018 9:52:17 AM
% Failed : 50.00%
Last Ping Status : Succeeded
Last Ping Time : 5
Last Ping TTL : 60
Average Ping Time : 5
Description : Channels:ADR2-Location1-SB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On : 12/11/2018 9:52:17 AM
Minimum Ping Time : 5
Maximum Ping Time : 5
Order : 2

==================================================
Host Name : 10.40.65.256
IP Address : 10.40.65.256
Reply IP Address : 10.40.65.256
Succeed Count : 2
Failed Count : 0
Consecutive Failed Count:
Max Consecutive Failed Count:
Max Consecutive Failed Time:
% Failed : 0%
Last Ping Status : Succeeded
Last Ping Time : 12
Last Ping TTL : 60
Average Ping Time : 25
Description : Channels:ADR3-Location1-NB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On :
Minimum Ping Time : 12
Maximum Ping Time : 38
Order : 3

==================================================
Host Name : 10.40.65.285
IP Address : 10.40.65.285
Reply IP Address : 10.40.65.285
Succeed Count : 2
Failed Count : 0
Consecutive Failed Count:
Max Consecutive Failed Count:
Max Consecutive Failed Time:
% Failed : 0%
Last Ping Status : Succeeded
Last Ping Time : 6
Last Ping TTL : 60
Average Ping Time : 24
Description : Channels:ADR4-Location3-WB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On :
Minimum Ping Time : 6
Maximum Ping Time : 42
Order : 4

==================================================
Host Name : 10.40.65.276
IP Address : 10.40.65.276
Reply IP Address : 10.40.65.276
Succeed Count : 2
Failed Count : 0
Consecutive Failed Count:
Max Consecutive Failed Count:
Max Consecutive Failed Time:
% Failed : 0%
Last Ping Status : Succeeded
Last Ping Time : 8
Last Ping TTL : 60
Average Ping Time : 25
Description : Channels:ADR5-Location3-EB
Last Succeed On : 12/11/2018 9:52:47 AM
Last Failed On :
Minimum Ping Time : 8
Maximum Ping Time : 41
Order : 5

The text file is located in the following folder : C:\Users\PLahkar\Desktop\Ping_Success\Pinsuccess_1.txt

Similarly, in the same folder I have C:\Users\PLahkar\Desktop\Ping_Success\Pinsuccess_2.txt in sequential order (a new file generated every 24 hours)

@techadmin8

Evan7191 · 2018-12-11T19:05:06.000Z

$Header = "Host Name,IP Address,Reply IP Address,Succeed Count,Failed Count,Consecutive Failed Count,Max Consecutive Failed Count,Max Consecutive Failed Time,% Failed,Last Ping Status,Last Ping Time,Last Ping TTL,Average Ping Time,Description,Last Succeed On,Last Failed On,Minimum Ping Time,Maximum Ping Time,Order"
$Header | Set-Content -Path Drive:\Path\File.csv
$Files = Get-ChildItem -Path Drive:\PathToLogFiles -Include *.txt
ForEach ($File in $Files) {
    $Text = Get-Content $File.FullName
    $Text = $Text -ireplace '^[^:]*:\s*','' -ireplace '\s*$',',' -ireplace '=',''
    $Text = ($Text | Out-String) -ireplace '\r\n','' | Add-Content -Path Drive:\Path\File.csv
}

Try this. It uses Add-Content instead of Out-File, because Add-Content is better for appending text to a new line during a loop. Out-File works better for writing all output at once.

spiceuser-319x7 · 2018-12-11T19:55:37.000Z

Evan this works perfectly. However, one of the issues seems to be not solved. The list that we get for IP’s is not in a sequential row since and we are basically pinging each IP separately and generating LOGS WITHIN THE SAME FILE. Please can you help

furicle · 2018-12-11T20:02:15.000Z

Have you asked Evan what his rates are yet?