Getting columns in a text file using powershell

Question

I have a text file, which contains 10 columns.

Now my question is: I need to search for a string and get output with required columns.

Ex: My text file contains:

#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Referer) cs-host sc-status sc-win32-status sc-bytes cs-bytes time-taken
2014-06-01 00:00:00 W3SVC1 abc814 121.30.83.14 POST /ASP1/WebServices/cloud.asmx - 80 - 70.27.127.10 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E) http://cloud.each.com/ASP1/cloud/Operator.swf cloud.each.com 200 0 3889 5054 46
2014-06-01 00:00:00 W3SVC1 abc814 121.30.83.14 POST /ASP1/WebServices/range.asmx - 80 - 70.27.127.10 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E) http://cloud.each.com/ASP1/cloud/Operator.swf cloud.each.com 200 0 3889 5054 46

Now, i need to search range.asmx and get only limited columns (time, server name, IP) to a different excel file.

Thanks, Kalyan

mjolinor · Accepted Answer · 2015-05-27 10:46:23Z

1

That #Fields line isn't going to parse properly as the header, so you'll need to massage that a bit to get it to work in Powershell:

$search = 'range.asmx'

$header = (Get-Content data.csv -TotalCount 1).Replace('#Fields: ','').split()
Get-Content data.csv |
select -Skip 1 |
ConvertFrom-Csv  -Delimiter ' ' -Header $header |
where { $_.'cs-uri-stem' -like "*$search*" }|
select time,'s-ip','cs-host','sc-status'

answered May 27, 2015 at 10:46

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Venkata Kalyan Over a year ago

Hi,I need to get time-taken as-well. When I keep time-take in the script, it is not giving the result, it displays blank. For example: the first line, at the end there is the number 46 (that is time taken). Can you please guide me on this?

majkinetor · Accepted Answer · 2015-05-27 10:11:03Z

0

Remove #Fields to make it CSV format, space delimited. Then:

$search = 'range.asmx'
$data = Import-Csv data.csv -Separator ' '
$data | ? cs-uri-stem -like "*$search*" | select time,'s-ip','cs-host','sc-status'

answered May 27, 2015 at 10:11

majkinetor

9,1229 gold badges56 silver badges75 bronze badges

1 Comment

Walter Mitty Over a year ago

"#" is a common convention for introducing comments in a CSV file. however, I do not know if the parser in Import-Csv detects and weeds out comments.

Collectives™ on Stack Overflow

Getting columns in a text file using powershell

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related