Solved

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

Posted on 2016-11-22
12
67 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 79

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
The viewer will learn how to dynamically set the form action using jQuery.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

809 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