Solved

Powershell to extract the important data and calculate if the amounts are correct

Posted on 2016-11-23
15
21 Views
Last Modified: 2016-11-24
I know this is not a simple and a long question so help is much appriciated
Is it possible to create the following in a script
i need to make an template to extract the important data from the attached order  
Count the amount of the orderlines and check to the total
I need the following data (i use the first orderline as example coca cola)
The VORnr in this example VOR16085519
Delivery date “leverdatum” in this example 7-11-2016
The Nr. allways 5 digits (21051)
The name (omschrijving (Coca Cola Krat 24x20 cl))
The quantity (Aantal (70))
The amount per piece (Stuksprijs(7,80))
The Packaging TAX  (Verp.bel(the cola has no packaging tax but on line 25 dr siemer there is)
Total (Bedr.incl.verp. (546,00))
On the last 2 line of the orderlines you see #DEEL/0! The meaning is that we order but the company doesnt has this in stock
Now the real problem start :)
The only way i see to identify, the name(omschrijving) is if the word "emballage"is in the sentence it will always be 3 amounts
If there is no word emballage the last letter always wil be a % sign or the letter L
Then I need to add up all the total amounts on the orderlines and check this against the 3 numbers starting after Totaal
in this example
Orderlines all the totals == 1.830,39 + 1.792,55 + 1.835,80
Than to add up the two tax amounts 384,38 + 107,55    
Now i need to verificate the amount after “Tot. incl.BTW” (5.950,67) against  “All the totals” and the TAX Lines
One other problem is that In this example there are 3 lines for Totaal (total) and 2 lines for BTW(TAX) but totaal(total) is minimal 1 line and max 3 lines TAX is minimal 0 lines and max 2 lines
Finaly i need a CSV or txt file with the above data if this was correct otherwise i need the text file with order VORxxxx was not correct
please ask any questions if i am not clear because my English is not all that :)
Thanks
Danny
EEexample.txt
0
Comment
Question by:Danny Kon
15 Comments
 
LVL 23

Expert Comment

by:Coralon
ID: 41899975
A sample file would help tremendously...

Coralon
0
 
LVL 39

Expert Comment

by:footech
ID: 41900112
Are there many input files, or is there a single file with all the orders?

Then I need to add up all the total amounts on the orderlines and check this against the 3 numbers starting after Totaal
in this example
Orderlines all the totals == 1.830,39 + 1.792,55 + 1.835,80
It is not clear what you need here.

Finaly i need a CSV or txt file with the above data if this was correct otherwise i need the text file with order VORxxxx was not correct
This needs to be clarified also.  What exactly should the .CSV file contain?  What exactly should the .TXT file contain?  Does it matter what the files are named?
The best help would be if you provide an input file (yes I saw the file you provided above), and the expected output file which would be generated by parsing the input file.  Provide a set of files for both conditions, correct data and incorrect data.
0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900202
Coralon,

There is an attached sample EEexample.txt

footech,

This is a scanned paper and i need to know if all the amounts on the paper are correct
21051 Coca Cola Krat 24x20 cl 70 7,80 546,00 <--
60547 Emballage Euro 5,00 70 5,00 350,00 <--
22005 Coca Cola Light Krat 24x20 cl 10 8,20 82,00<--
60547 Emballage Euro 5,00 10 5,00 50,00 <--
21043 Fanta Sinas Krat 24x20 cl 10 8,20 82,00 <--

So i need the last amount 546+350+82+50+82 etc etc
Then at the buttom you see the word and 3 numbers  
Totaal1.830,39
1.792,55
1.835,80
The total of all the last amounts have to be the same as the totals of the 3 numbers
(546+350+82+50+82 etc etc) == 1830,39+1792.55+1835.80
Then underneath Btw bedr. there are 2 amounts  384,38, 107,55
if the above totals are correct i need to add up the 5 amounts to check against the grand total (is in Dutch Tot. incl.BTW 5.950,67
so 1830,39+1792.55+1835.80 + 384,38, 107,55 == 5.950,67
If above is correct or incorrect i only need that the name of the file is changed so the file doesn't have to be changed
if correct the name of the file is VOR16085519.txt
if not correct name of the file is VOR16085519notcorrect.txt

Thanks Danny
0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900205
The
1830,39+1792.55+1835.80 + 384,38, 107,55 == 5.950,67  
has to be
1830,39+1792.55+1835.80+384,38+107,55 == 5.950,67

Danny
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41900322
The following script (save as Import-OrderFile.ps1) should parse an order file and return a custom object with the properties found in the file. With that, you should be able to do any verification you feel like doing.
The properties have their respective type, so the delivery date, for example, is a DateTime object, not a string.
For your first tests, always use the -Verbose argument to check if any vital lines were skipped.
For details:
Get-Help .\Import-OrderFile.ps1
is supported.
I'd suggest you use this unchanged as a stand-alone script. Call it from another script to create the custom object from a file, then access the required properties as desired in your verification script (see the Examples from Get-Help).
Note that all monetary amounts are represented as CENTs.
Monetary calculations are usually done that way, and then converted at the last moment to Euros/Cents format, because floating point calculations can produce unexpected results.
Paste this into a PS console for a demonstration:
0 + [single]5950.67

Open in new window

<#
.SYNOPSIS
Imports an order file and returns a PSObject.
.DESCRIPTION
The script Import-OrderFile.ps1 imports an order file and returns a PSObject.
Note: all monetary amounts are returned as CENTS!
Use the -Verbose argument to list any lines that could not be parsed.
See examples on how to convert/export the items as monetary values.
.PARAMETER <Path>
The path to the file.
.OUTPUTS
Custom PSObject with the following properties:
  - Number <string> - the VOR order number
  - OrderDate <datetime>
  - DeliveryDate <datetime>
  - Items <PSObject[]>- an array of the items in the order with the following properties:
      - Number <string> - "Nr."
      - Name <string> - "Omschrijving"
      - Quantity <uint32> - "Aantal"
      - Price <long> - "Stukprijs"
      - PackTax <long> - "Verp.bel"
      - Total <long> - "Bedr.incl.verp."
      - NotInStock <bool> - $True if "Stukprijs" is "#DEEL/0!", $False otherwise
  - Totals <long[]> - an array with the "Totaal" amounts
  - TaxPercents <int[]> - an array with the "BTW" percentages
  - TaxAmounts <long[]> - an array with the "Btw bedr." tax amounts
  - TotalInclTax <long> - "Tot. incl.BTW"
.EXAMPLE
$Order = .\Import-OrderFile.ps1 -Path 'C:\Temp\EEexample.txt' -Verbose
Imports the order file 'C:\Temp\EEexample.txt' and saves it in the variable $Order.
.EXAMPLE
$Order.Totals
Returns the Total amounts AS CENTS found in the file.
.EXAMPLE
_
$Order.Items |
	Select-Object -Property Number, Name, Quantity, @{n='Price';e={'{0:N2}' -f ($_.Price/100)}}, @{n='PackTax';e={'{0:N2}' -f ($_.PackTax/100)}}, @{n='Total';e={'{0:N2}' -f ($_.Total/100)}}, NotInStock |
	Export-Csv -NoTypeInformation -Path 'C:\Temp\EEexample.csv'
	
Converts the Items property of the previously imported $Order into monetary format and exports to a csv.
.EXAMPLE
$Order.Items | Format-Table -Property Number, Name, Quantity, @{n='Price';e={'{0:N2}' -f ($_.Price/100)};a='right'}, @{n='PackTax';e={'{0:N2}' -f ($_.PackTax/100)};a='right'}, @{n='Total';e={'{0:N2}' -f ($_.Total/100)};a='right'}, NotInStock
	
Converts the Items property of the previously imported $Order into monetary format and displays it on the console host.
.LINK
Powershell to extract the important data and calculate if the amounts are correct
https://www.experts-exchange.com/questions/28984972/
#>
[CmdletBinding()]
Param(
	[string]$Path = 'C:\Temp\EEexample.txt'
)

Function ConvertTo-Amount([string]$Amount)  {
	Return [long]$Amount.Replace('.', '').Replace(',', '')
}

$Order = Select-Object -InputObject '' -Property Number, OrderDate, DeliveryDate, Items, Totals, TaxPercents, TaxAmounts, TotalInclTax
$Order.Items = @()
$Order.Totals = @()
$Order.TaxAmounts = @()
$Order.TaxPercents = @()
$Order.TotalInclTax = 0
$CaptureAs = $Null
Switch -regex (Get-Content -Path $Path) {
	'\A\s*\Z' {}
	'\A.*Order\.nr\.\s+(?<VOR>VOR\d+)\s*\Z' {
		$Order.Number = $Matches['VOR']
	}
	'\A.*\sOrderdatum\s+(?<OrderDate>\d\d?-\d\d?-\d{4})\s*\Z' {
		$Order.OrderDate = [datetime]::ParseExact($Matches['OrderDate'], 'd-M-yyyy', $Null)
	}
	'\A.*\sLeverdatum\s+(?<DeliveryDate>\d\d?-\d\d?-\d{4})\s*\Z' {
		$Order.DeliveryDate = [datetime]::ParseExact($Matches['DeliveryDate'], 'd-M-yyyy', $Null)
	}
	"\A\s*(?<Number>\d{5})\s+(?<Name>.+)\s+(?<Quantity>\d+)\s+(?<Price>([0-9.]+,\d\d|#DEEL/0!))\s+(?:(?<PackTax>[0-9.]+,\d\d)\s+)?(?<Total>[0-9.]+,\d\d)\s*\Z" {
		$Result = Select-Object -InputObject '' -Property 'Number', 'Name', 'Quantity', 'Price', 'PackTax', 'Total', 'NotInStock'
		$Result.NotInStock =	$Matches['Price'] -eq '#DEEL/0!'
		$Result.Number =		[string]$Matches['Number']
		$Result.Name =			[string]$Matches['Name']
		$Result.Quantity =		[uint32]$Matches['Quantity']
		$Result.Price =			ConvertTo-Amount -Amount $(If ($Result.NotInStock) {'0'} Else {$Matches['Price']})
		$Result.PackTax =		ConvertTo-Amount -Amount $(If ($Matches.ContainsKey('PackTax') {$Matches['PackTax']} Else {'0'})
		$Result.Total =			ConvertTo-Amount -Amount $Matches['Total']
		$Order.Items += $Result
	}
	'\A\s*Totaal\s*(?<Total>[0-9.]+,\d\d)\s*\Z' {
		$CaptureAs = 'Totals'
		$Order.Totals += ConvertTo-Amount -Amount $Matches['Total']
	}
	'\A\s*Btw bedr\.\s*\Z' {
		$CaptureAs = 'TaxAmounts'
	}
	'\A\s*BTW\s*\Z' {
		$CaptureAs = 'TaxPercents'
	}
	'\A\s*Tot\. incl\.BTW\s*\Z' {
		$CaptureAs = 'TotalInclTax'
	}
	'\A\s*(?<Amount>[0-9.]+,\d\d)\s*\Z' {
		If ($CaptureAs) {
			$Order.$CaptureAs += ConvertTo-Amount -Amount $Matches['Amount']
		} Else {
			Write-Verbose "SKIPPED: $($_)"
		}
	}
	'\A\s*(?<Percent>\d\d?)\s*%\s*\Z' {
		If ($CaptureAs) {
			$Order.$CaptureAs += [int]$Matches['Percent']
		} Else {
			Write-Verbose "SKIPPED: $($_)"
		}
	}
	default {
		$CaptureAs = $Null
		Write-Verbose "SKIPPED: $($_)"
	}
}
$Order

Open in new window

Output example for your file:
Number       : VOR16085519
OrderDate    : 11/04/2016 00:00:00
DeliveryDate : 11/04/2016 00:00:00
Items        : {@{Number=21051; Name=Coca Cola Krat 24x20 cl; Quantity=70; Price=780; PackTax=0; Total=54600; NotInStock=False}, @{Number=60547;
               Name=Emballage Euro 5,00; Quantity=70; Price=500; PackTax=0; Total=35000; NotInStock=False}, @{Number=22005; Name=Coca Cola Light Krat 24x20
               cl; Quantity=10; Price=820; PackTax=0; Total=8200; NotInStock=False}, @{Number=60547; Name=Emballage Euro 5,00; Quantity=10; Price=500;
               PackTax=0; Total=5000; NotInStock=False}...}
Totals       : {183039, 179255, 183580}
TaxPercents  : {21, 6, 0}
TaxAmounts   : {38438, 10755}
TotalInclTax : 595067

Open in new window

The last few lines of the Items property, formatted for screen output as described in the examples:
16008  Budweiser Doos 24x33 cl 5%                     10  14,50    2,80 147,80      False
21144  Pepsi Cola Krat 28x20 cl                        4   9,67    0,00  38,68      False
60547  Emballage Euro 5,00                             4   5,00    0,00  20,00      False
13538  Amstel Radler 0% Krat 4x6x30 cl                 2  12,80    0,00  25,60      False
60543  Emballage Euro 3,90                             2   3,90    0,00   7,80      False
11257  Grimbergen Dubbel Fust 20 ltr 6,5%              0   0,00    0,00   0,00       True
60101  Emballage Euro 30,00                            0   0,00    0,00   0,00       True

Open in new window

0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900614
oBdA

Wat i am doing wrong here

 Directory: C:\temp


Mode                LastWriteTime         Length Name                                                                                                                    
----                -------------         ------ ----                                                                                                                    
-a----       22-11-2016     10:15           3436 EEexample.txt                                                                                                          
-a----       24-11-2016     15:55           4823 Import-OrderFile.ps1                                                                                                    



PS C:\temp> Get-Help .\Import-OrderFile.ps1
Get-Help : Get-Help could not find .\Import-OrderFile.ps1 in a help file in this session. To download updated help topics type: "Update-Help". To get help online, search
 for the help topic in the TechNet library at http://go.microsoft.com/fwlink/?LinkID=107116.
At line:1 char:1
+ Get-Help .\Import-OrderFile.ps1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (:) [Get-Help], HelpNotFoundException
    + FullyQualifiedErrorId : HelpNotFound,Microsoft.PowerShell.Commands.GetHelpCommand
 
Danny
0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900626
if i try

PS C:\temp> $Order = .\Import-OrderFile.ps1 -Path 'C:\Temp\EEexample.txt' -Verbose
At C:\temp\Import-OrderFile.ps1:83 char:85
+ ... vertTo-Amount -Amount $(If ($Matches.ContainsKey('PackTax') {$Matches ...
+                                                                 ~
Unexpected token '{' in expression or statement.
At C:\temp\Import-OrderFile.ps1:83 char:85
+ ... vertTo-Amount -Amount $(If ($Matches.ContainsKey('PackTax') {$Matches ...
+                                                                 ~
Missing closing ')' after expression in 'If' statement.
At C:\temp\Import-OrderFile.ps1:83 char:107
+ ... If ($Matches.ContainsKey('PackTax') {$Matches['PackTax']} Else {'0'})
+                                                               ~~~~
Unexpected token 'Else' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : UnexpectedToken
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 83

Expert Comment

by:oBdA
ID: 41900644
Sorry, missing closing bracket (which caused the Get-Help error as well).
Here's the corrected version, or you can just add a single closing round bracket after the ContainsKey('PackTax') in line 83.
<#
.SYNOPSIS
Imports an order file and returns a PSObject.
.DESCRIPTION
The script Import-OrderFile.ps1 imports an order file and returns a PSObject.
Note: all monetary amounts are returned as CENTS!
Use the -Verbose argument to list any lines that could not be parsed.
See examples on how to convert/export the items as monetary values.
.PARAMETER Path
The path to the file.
.OUTPUTS
Custom PSObject with the following properties:
  - Number <string> - the VOR order number
  - OrderDate <datetime>
  - DeliveryDate <datetime>
  - Items <PSObject[]>- an array of the items in the order with the following properties:
      - Number <string> - "Nr."
      - Name <string> - "Omschrijving"
      - Quantity <uint32> - "Aantal"
      - Price <long> - "Stukprijs"
      - PackTax <long> - "Verp.bel"
      - Total <long> - "Bedr.incl.verp."
      - NotInStock <bool> - $True if "Stukprijs" is "#DEEL/0!", $False otherwise
  - Totals <long[]> - an array with the "Totaal" amounts
  - TaxPercents <int[]> - an array with the "BTW" percentages
  - TaxAmounts <long[]> - an array with the "Btw bedr." tax amounts
  - TotalInclTax <long> - "Tot. incl.BTW"
.EXAMPLE
$Order = .\Import-OrderFile.ps1 -Path 'C:\Temp\EEexample.txt' -Verbose
Imports the order file 'C:\Temp\EEexample.txt' and saves it in the variable $Order.
.EXAMPLE
$Order.Totals
Returns the Total amounts AS CENTS found in the file.
.EXAMPLE
_
$Order.Items |
	Select-Object -Property Number, Name, Quantity, @{n='Price';e={'{0:N2}' -f ($_.Price/100)}}, @{n='PackTax';e={'{0:N2}' -f ($_.PackTax/100)}}, @{n='Total';e={'{0:N2}' -f ($_.Total/100)}}, NotInStock |
	Export-Csv -NoTypeInformation -Path 'C:\Temp\EEexample.csv'
	
Converts the Items property of the previously imported $Order into monetary format and exports to a csv.
.EXAMPLE
$Order.Items | Format-Table -Property Number, Name, Quantity, @{n='Price';e={'{0:N2}' -f ($_.Price/100)};a='right'}, @{n='PackTax';e={'{0:N2}' -f ($_.PackTax/100)};a='right'}, @{n='Total';e={'{0:N2}' -f ($_.Total/100)};a='right'}, NotInStock
	
Converts the Items property of the previously imported $Order into monetary format and displays it on the console host.
.LINK
Powershell to extract the important data and calculate if the amounts are correct
https://www.experts-exchange.com/questions/28984972/
#>
[CmdletBinding()]
Param(
	[string]$Path = 'C:\Temp\EEexample.txt'
)

Function ConvertTo-Amount([string]$Amount)  {
	Return [long]$Amount.Replace('.', '').Replace(',', '')
}

$Order = Select-Object -InputObject '' -Property Number, OrderDate, DeliveryDate, Items, Totals, TaxPercents, TaxAmounts, TotalInclTax
$Order.Items = @()
$Order.Totals = @()
$Order.TaxAmounts = @()
$Order.TaxPercents = @()
$Order.TotalInclTax = 0
$CaptureAs = $Null
Switch -regex (Get-Content -Path $Path) {
	'\A\s*\Z' {}
	'\A.*Order\.nr\.\s+(?<VOR>VOR\d+)\s*\Z' {
		$Order.Number = $Matches['VOR']
	}
	'\A.*\sOrderdatum\s+(?<OrderDate>\d\d?-\d\d?-\d{4})\s*\Z' {
		$Order.OrderDate = [datetime]::ParseExact($Matches['OrderDate'], 'd-M-yyyy', $Null)
	}
	'\A.*\sLeverdatum\s+(?<DeliveryDate>\d\d?-\d\d?-\d{4})\s*\Z' {
		$Order.DeliveryDate = [datetime]::ParseExact($Matches['DeliveryDate'], 'd-M-yyyy', $Null)
	}
	"\A\s*(?<Number>\d{5})\s+(?<Name>.+)\s+(?<Quantity>\d+)\s+(?<Price>([0-9.]+,\d\d|#DEEL/0!))\s+(?:(?<PackTax>[0-9.]+,\d\d)\s+)?(?<Total>[0-9.]+,\d\d)\s*\Z" {
		$Result = Select-Object -InputObject '' -Property 'Number', 'Name', 'Quantity', 'Price', 'PackTax', 'Total', 'NotInStock'
		$Result.NotInStock =	$Matches['Price'] -eq '#DEEL/0!'
		$Result.Number =		[string]$Matches['Number']
		$Result.Name =			[string]$Matches['Name']
		$Result.Quantity =		[uint32]$Matches['Quantity']
		$Result.Price =			ConvertTo-Amount -Amount $(If ($Result.NotInStock) {'0'} Else {$Matches['Price']})
		$Result.PackTax =		ConvertTo-Amount -Amount $(If ($Matches.ContainsKey('PackTax') {$Matches['PackTax']} Else {'0'})
		$Result.Total =			ConvertTo-Amount -Amount $Matches['Total']
		$Order.Items += $Result
	}
	'\A\s*Totaal\s*(?<Total>[0-9.]+,\d\d)\s*\Z' {
		$CaptureAs = 'Totals'
		$Order.Totals += ConvertTo-Amount -Amount $Matches['Total']
	}
	'\A\s*Btw bedr\.\s*\Z' {
		$CaptureAs = 'TaxAmounts'
	}
	'\A\s*BTW\s*\Z' {
		$CaptureAs = 'TaxPercents'
	}
	'\A\s*Tot\. incl\.BTW\s*\Z' {
		$CaptureAs = 'TotalInclTax'
	}
	'\A\s*(?<Amount>[0-9.]+,\d\d)\s*\Z' {
		If ($CaptureAs) {
			$Order.$CaptureAs += ConvertTo-Amount -Amount $Matches['Amount']
		} Else {
			Write-Verbose "SKIPPED: $($_)"
		}
	}
	'\A\s*(?<Percent>\d\d?)\s*%\s*\Z' {
		If ($CaptureAs) {
			$Order.$CaptureAs += [int]$Matches['Percent']
		} Else {
			Write-Verbose "SKIPPED: $($_)"
		}
	}
	default {
		$CaptureAs = $Null
		Write-Verbose "SKIPPED: $($_)"
	}
}
$Order

Open in new window

0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900656
I get the same error

PS C:\temp> Get-Help .\Import-OrderFile.ps1
Get-Help : Get-Help could not find .\Import-OrderFile.ps1 in a help file in this session. To download updated help topics type: "Update-Help". To get help online, search
 for the help topic in the TechNet library at http://go.microsoft.com/fwlink/?LinkID=107116.
At line:1 char:1
+ Get-Help .\Import-OrderFile.ps1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ResourceUnavailable: (:) [Get-Help], HelpNotFoundException
    + FullyQualifiedErrorId : HelpNotFound,Microsoft.PowerShell.Commands.GetHelpCommand
0
 
LVL 83

Accepted Solution

by:
oBdA earned 500 total points
ID: 41900661
Not your fault - that's what happens when you test the correction, then re-test the error, and then post without removing the error again.
Sorry for the confusion.
<#
.SYNOPSIS
Imports an order file and returns a PSObject.
.DESCRIPTION
The script Import-OrderFile.ps1 imports an order file and returns a PSObject.
Note: all monetary amounts are returned as CENTS!
Use the -Verbose argument to list any lines that could not be parsed.
See examples on how to convert/export the items as monetary values.
.PARAMETER Path
The path to the file.
.OUTPUTS
Custom PSObject with the following properties:
  - Number <string> - the VOR order number
  - OrderDate <datetime>
  - DeliveryDate <datetime>
  - Items <PSObject[]>- an array of the items in the order with the following properties:
      - Number <string> - "Nr."
      - Name <string> - "Omschrijving"
      - Quantity <uint32> - "Aantal"
      - Price <long> - "Stukprijs"
      - PackTax <long> - "Verp.bel"
      - Total <long> - "Bedr.incl.verp."
      - NotInStock <bool> - $True if "Stukprijs" is "#DEEL/0!", $False otherwise
  - Totals <long[]> - an array with the "Totaal" amounts
  - TaxPercents <int[]> - an array with the "BTW" percentages
  - TaxAmounts <long[]> - an array with the "Btw bedr." tax amounts
  - TotalInclTax <long> - "Tot. incl.BTW"
.EXAMPLE
$Order = .\Import-OrderFile.ps1 -Path 'C:\Temp\EEexample.txt' -Verbose
Imports the order file 'C:\Temp\EEexample.txt' and saves it in the variable $Order.
.EXAMPLE
$Order.Totals
Returns the Total amounts AS CENTS found in the file.
.EXAMPLE
_
$Order.Items |
	Select-Object -Property Number, Name, Quantity, @{n='Price';e={'{0:N2}' -f ($_.Price/100)}}, @{n='PackTax';e={'{0:N2}' -f ($_.PackTax/100)}}, @{n='Total';e={'{0:N2}' -f ($_.Total/100)}}, NotInStock |
	Export-Csv -NoTypeInformation -Path 'C:\Temp\EEexample.csv'
	
Converts the Items property of the previously imported $Order into monetary format and exports to a csv.
.EXAMPLE
$Order.Items | Format-Table -Property Number, Name, Quantity, @{n='Price';e={'{0:N2}' -f ($_.Price/100)};a='right'}, @{n='PackTax';e={'{0:N2}' -f ($_.PackTax/100)};a='right'}, @{n='Total';e={'{0:N2}' -f ($_.Total/100)};a='right'}, NotInStock
	
Converts the Items property of the previously imported $Order into monetary format and displays it on the console host.
.LINK
Powershell to extract the important data and calculate if the amounts are correct
https://www.experts-exchange.com/questions/28984972/
#>
[CmdletBinding()]
Param(
	[string]$Path = 'C:\Temp\EEexample.txt'
)

Function ConvertTo-Amount([string]$Amount)  {
	Return [long]$Amount.Replace('.', '').Replace(',', '')
}

$Order = Select-Object -InputObject '' -Property Number, OrderDate, DeliveryDate, Items, Totals, TaxPercents, TaxAmounts, TotalInclTax
$Order.Items = @()
$Order.Totals = @()
$Order.TaxAmounts = @()
$Order.TaxPercents = @()
$Order.TotalInclTax = 0
$CaptureAs = $Null
Switch -regex (Get-Content -Path $Path) {
	'\A\s*\Z' {}
	'\A.*Order\.nr\.\s+(?<VOR>VOR\d+)\s*\Z' {
		$Order.Number = $Matches['VOR']
	}
	'\A.*\sOrderdatum\s+(?<OrderDate>\d\d?-\d\d?-\d{4})\s*\Z' {
		$Order.OrderDate = [datetime]::ParseExact($Matches['OrderDate'], 'd-M-yyyy', $Null)
	}
	'\A.*\sLeverdatum\s+(?<DeliveryDate>\d\d?-\d\d?-\d{4})\s*\Z' {
		$Order.DeliveryDate = [datetime]::ParseExact($Matches['DeliveryDate'], 'd-M-yyyy', $Null)
	}
	"\A\s*(?<Number>\d{5})\s+(?<Name>.+)\s+(?<Quantity>\d+)\s+(?<Price>([0-9.]+,\d\d|#DEEL/0!))\s+(?:(?<PackTax>[0-9.]+,\d\d)\s+)?(?<Total>[0-9.]+,\d\d)\s*\Z" {
		$Result = Select-Object -InputObject '' -Property 'Number', 'Name', 'Quantity', 'Price', 'PackTax', 'Total', 'NotInStock'
		$Result.NotInStock =	$Matches['Price'] -eq '#DEEL/0!'
		$Result.Number =		[string]$Matches['Number']
		$Result.Name =			[string]$Matches['Name']
		$Result.Quantity =		[uint32]$Matches['Quantity']
		$Result.Price =			ConvertTo-Amount -Amount $(If ($Result.NotInStock) {'0'} Else {$Matches['Price']})
		$Result.PackTax =		ConvertTo-Amount -Amount $(If ($Matches.ContainsKey('PackTax')) {$Matches['PackTax']} Else {'0'})
		$Result.Total =			ConvertTo-Amount -Amount $Matches['Total']
		$Order.Items += $Result
	}
	'\A\s*Totaal\s*(?<Total>[0-9.]+,\d\d)\s*\Z' {
		$CaptureAs = 'Totals'
		$Order.Totals += ConvertTo-Amount -Amount $Matches['Total']
	}
	'\A\s*Btw bedr\.\s*\Z' {
		$CaptureAs = 'TaxAmounts'
	}
	'\A\s*BTW\s*\Z' {
		$CaptureAs = 'TaxPercents'
	}
	'\A\s*Tot\. incl\.BTW\s*\Z' {
		$CaptureAs = 'TotalInclTax'
	}
	'\A\s*(?<Amount>[0-9.]+,\d\d)\s*\Z' {
		If ($CaptureAs) {
			$Order.$CaptureAs += ConvertTo-Amount -Amount $Matches['Amount']
		} Else {
			Write-Verbose "SKIPPED: $($_)"
		}
	}
	'\A\s*(?<Percent>\d\d?)\s*%\s*\Z' {
		If ($CaptureAs) {
			$Order.$CaptureAs += [int]$Matches['Percent']
		} Else {
			Write-Verbose "SKIPPED: $($_)"
		}
	}
	default {
		$CaptureAs = $Null
		Write-Verbose "SKIPPED: $($_)"
	}
}
$Order

Open in new window

0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900672
oBdA

This is working perfect now but wat i do not understand is how to get

I need the last amount 546+350+82+50+82 etc etc
Then at the buttom you see the word and 3 numbers  
Totaal1.830,39
1.792,55
1.835,80
The total of all the last amounts have to be the same as the totals of the 3 numbers
(546+350+82+50+82 etc etc) == 1830,39+1792.55+1835.80
Then underneath Btw bedr. there are 2 amounts  384,38, 107,55
if the above totals are correct i need to add up the 5 amounts to check against the grand total (is in Dutch Tot. incl.BTW 5.950,67
so 1830,39+1792.55+1835.80 + 384,38+107,55 == 5.950,67
If above is correct or incorrect i only need that the name of the file is changed so the file doesn't have to be changed
if correct the name of the file is VOR16085519.txt
if not correct name of the file is VOR16085519notcorrect.txt

Thanks Danny
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41900697
You have an object with all amounts prepared. All you need to do is sum them up and compare them.
Example:
[CmdletBinding()]
Param(
	[string]$Path = 'C:\Temp\EEexample.txt'
)
$OrderItem = Get-Item -Path $Path
$Order = & "C:\Temp\Import-OrderFile.ps1" -Path $OrderItem.FullName -Verbose
$SumTotalItems = $Order.Items | Measure-Object -Property Total -Sum | Select-Object -ExpandProperty Sum
$SumTotalOrder = $Order.Totals | Measure-Object -Sum | Select-Object -ExpandProperty Sum
$SumTotalOrderInclTax = $SumTotalOrder + ($Order.TaxAmounts | Measure-Object -Sum | Select-Object -ExpandProperty Sum)

"Calculated items sum ($($Order.Items.Count) items): $($SumTotalItems) cents" | Write-Host
"Calculated total sums from order form ($($Order.Totals -join ' + ')): $($SumTotalOrder) cents" | Write-Host
"Calculated total sum including tax ($($SumTotalOrder) + $($Order.TaxAmounts -join ' + ')): $($SumTotalOrderInclTax) cents" | Write-Host
"Total including tax from order form: $($Order.TotalInclTax) cents" | Write-Host

If (($SumTotalItems -eq $SumTotalOrder) -and ($SumTotalOrderInclTax -eq $Order.TotalInclTax)) {
	"Everything fine." | Write-Host -ForegroundColor Green
} Else {
	"Order incorrect." | Write-Host -ForegroundColor Red
	Rename-Item -Path $OrderItem.FullName -NewName ($OrderItem.BaseName + '_NotCorrect' + $OrderItem.Extension)
}

Open in new window

0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41900817
If i want to test this on other orders and the order number doesnt start with VOR but ORDER can i change the
'\A.*Order\.nr\.\s+(?<ORDER>ORDER\d+)\s*\Z' {
            $Order.Number = $Matches['ORDER']
 Or does it not work in this way

Thanks Danny
0
 
LVL 6

Author Closing Comment

by:Danny Kon
ID: 41900819
I wish my English was a little better to explain how i think about for me your unbelievable skills

You helped me a lot

1000 times thanks
0
 
LVL 83

Expert Comment

by:oBdA
ID: 41900903
Just replace the second "VOR"; you can leave the one in angle brackets, that's the name of the capture group.
'\A.*Order\.nr\.\s+(?<VOR>ORDER\d+)\s*\Z' {
Or to support both VORxxx and ORDERxxx:
'\A.*Order\.nr\.\s+(?<VOR>(?:VOR|ORDER)\d+)\s*\Z' {
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now