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

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
LVL 6
###### Who is Participating?

CIOCommented:
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

OwnerCommented:
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

CIOCommented:
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

Author Commented:
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

CIOCommented:
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

Author Commented:
Gustav,

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

Thanks Danny
0

CIOCommented:
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

Author Commented:
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

CIOCommented:
Indeed, except that the separator is space, thus:

arrFields = Split(strLine, “ ”)

/gustav
0

Author Commented:
Do you think this question is easier to solve in powershell ?

Thanks Danny
0

Author Commented:
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

CIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.