We help IT Professionals succeed at work.

Count on Txt File

Nick Collins
Nick Collins asked
on
120 Views
Last Modified: 2018-05-16
Good Morning,

Just a simple request I have a text file that has various header records and I want to be able to do a count on the header record ‘Order Number’.

Then output to a text file and sorted in A to Z with a total at the bottom. Example below

Order Number.        Total

GB088776               10
VB66666.                 12
HF56778.                 6

Total                          28
Comment
Watch Question

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Assuming the text file is comma seperated (or using another delimiter):
$data = Import-CSV c:\input.csv | Group-Object 'Order Number' | Sort Name
$total =($data.Count | measure-object -Sum).Sum
( Write Output "Order NumnerˋtTotal"
  $data | % { Write-Output "$($_.Name)ˋt$($_.Count)" }
  Write-Output "Totalˋt$total"
) | Out-File C:\output.csv

Open in new window

Author

Commented:
I am getting an error with this code...

At D:\iauditor_exports_folder\ExportData\Run.ps1:3 char:37
+ ( Write Output "Order NumnerˋtTotal"
+                                     ~
Missing closing ')' in expression.
At D:\iauditor_exports_folder\ExportData\Run.ps1:4 char:3
+   $data | % { Write-Output "$($_.Name)ˋt$($_.Count)" }
+   ~~~~~
Unexpected token '$data' in expression or statement.
At D:\iauditor_exports_folder\ExportData\Run.ps1:6 char:1
+ ) | Out-File C:\output.csv
+ ~
Unexpected token ')' in expression or statement.
At D:\iauditor_exports_folder\ExportData\Run.ps1:6 char:3
+ ) | Out-File C:\output.csv
+   ~
An empty pipe element is not allowed.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInExpression

Open in new window

"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2014

Commented:
Don't give me any points for this.
Missing a hyphen ("-") in Write-Output in line 3.

Author

Commented:
The grand total on the bottom is not working of the powershell is not correct
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
Corrected script:
$data = Import-CSV C:\input.csv | Group-Object 'Order Number' -NoElement | Sort Name
$total =($data | measure-object Count -Sum).Sum
$( Write-Output "Order Numner`tTotal"
  $data | % { Write-Output "$($_.Name)`t$($_.Count)" }
  Write-Output "Total`t$total"
) | Out-File C:\output.csv

Open in new window

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.