Solved

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

Posted on 2016-11-22
12
43 Views
Last Modified: 2016-11-23
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
Comment
Question by:Danny Kon
  • 6
  • 5
12 Comments
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
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 49

Expert Comment

by:Gustav Brock
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

by:Danny Kon
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))
see also my answer to Gustav

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 49

Expert Comment

by:Gustav Brock
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
    strLine = objFile.ReadLine
    ' do stuff, find price, etc. in strLine.
    objOutFile.Write(strLine)
Loop
objOutFile.Close
objFile.Close
objFS.DeleteFile(strFile)
objFS.MoveFile strTemp,strFile 

Open in new window

/gustav
0
 
LVL 6

Author Comment

by:Danny Kon
ID: 41898767
Gustav,

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

Thanks Danny
0
 
LVL 49

Expert Comment

by:Gustav Brock
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 6

Author Comment

by:Danny Kon
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?
strLine = objFile.ReadLine
arrFields = Split(strLine, “,”)
and then to use the if statement like
If InStr(arrFields(1), “Emballage ”)
   
Thanks danny
0
 
LVL 49

Expert Comment

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

    arrFields = Split(strLine, “ ”)

/gustav
0
 
LVL 6

Author Comment

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

Thanks Danny
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 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

by:Danny Kon
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 49

Expert Comment

by:Gustav Brock
ID: 41899379
You are welcome!

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It is becoming increasingly popular to have a front-page slider on a web site. Nearly every TV website,  magazine or online news has one on their site, and even some e-commerce sites have one. Today you can use sliders with Joomla, WordPress or …
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

743 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

12 Experts available now in Live!

Get 1:1 Help Now