Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# A script to extract the important data and calculate if the amounts are correct

Posted on 2016-11-22
Medium Priority
92 Views
I know this is not a simple 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 :) 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
Question by:Danny Kon
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 5

LVL 83

Expert Comment

ID: 41898277
21051 Coca Cola Krat 24x20 cl 70 7,80 546,00

21051 is the item #
Coca Cola Krat is the item name
24x20 (24 cans 24 cases)
what are the others
0

LVL 51

Expert Comment

ID: 41898619
It would be a tough job as there is no uniform syntax of the order lines. The field separator is "space" but the name also can contain spaces, so you would have read the lines one by one from the back. Doable, but a decent job to get straight.

Could best be done in Access or C# dependent on the requested output which you don't mention.

/gustav
0

LVL 6

Author Comment

ID: 41898724
David,
Coca Cola Krat 24x20 cl (is the desciption)
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))

Gustav
as you mention I was hoping that its possible to read from right to left
But the problem is the The Packaging TAX so sometimes there are 4 amounts
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

The output has be something like
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
etc...
1.830,39
1.792,55
1.835,80
384,38
107,55
5.950,67
And if possible the name of the file the vor nr
VOR16085519.txt if the file is not correct it can be an empty file and the filename VOR16085519notcorrect.txt

As you mention its better to do this in access or C#
C# = I think it is to hard for me to understand
Access can be an good alternative but i really like to learn from this and VBS has my preference

Again help is much appreciated

Thanks Danny
0

LVL 51

Expert Comment

ID: 41898753
Well, that would something along this skeleton:
``````Set objFS = CreateObject("Scripting.FileSystemObject")
strFile = "c:\test\order.txt"
strTemp = "c:\test\output.txt"
Set objFile = objFS.OpenTextFile(strFile)
Set objOutFile = objFS.CreateTextFile(strTemp,True)
Do Until objFile.AtEndOfStream
' do stuff, find price, etc. in strLine.
objOutFile.Write(strLine)
Loop
objOutFile.Close
objFile.Close
objFS.DeleteFile(strFile)
objFS.MoveFile strTemp,strFile
``````
/gustav
0

LVL 6

Author Comment

ID: 41898767
Gustav,

That would be perfect you think you can help to work this out (specially the do stuff:)

Thanks Danny
0

LVL 51

Expert Comment

ID: 41898774
This expert suggested creating a Gigs project.
Sorry, real work blocks for that. It's much more than a 15 minutes task.
I have ticked the "Recommend creating a project in Gigs" ...

/gustav
0

LVL 6

Author Comment

ID: 41898942
Gustav,

Because money is very limited i need to do this in this way and maybe split the above question
Would you be so kind to give me some directions

Is this the way to go?
arrFields = Split(strLine, “,”)
and then to use the if statement like
If InStr(arrFields(1), “Emballage ”)

Thanks danny
0

LVL 51

Expert Comment

ID: 41898968
Indeed, except that the separator is space, thus:

arrFields = Split(strLine, “ ”)

/gustav
0

LVL 6

Author Comment

ID: 41899100
Do you think this question is easier to solve in powershell ?

Thanks Danny
0

LVL 51

Accepted Solution

Gustav Brock earned 2000 total points
ID: 41899216
No, because that is C# syntax which you are not so fond of.

I would suggest Access as you probably wish to save the transformed data for some kind of further processing.

/gustav
0

LVL 6

Author Closing Comment

ID: 41899376
Gustav,

That is funny i did not now this and because i really need to learn Powershell anyway I will try this way because there is also no further processing needed

Help so far is much appreciated

Thanks Danny
0

LVL 51

Expert Comment

ID: 41899379
You are welcome!

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
###### Suggested Courses
Course of the Month4 days, 23 hours left to enroll