Sign in to follow this  
Followers 0
wutzke

Summarizing Large Reports

4 posts in this topic

Thanks to everyone who has added code snippets to my project.

Once again to summarize what we are trying to do is...

Starting with a PDF file that contains sales data

(example) Sales Report in PDF

I can use an Online PDF to plain text converter to turn the PDF into

a text file Excel can read

The next step is to reformat that text into an

Excel File with Sales Data

I'm hoping that someone can help roll this all together, from PDF to Excel. Many if you have contributed GREAT advise

(Review my Posts)

Share this post


Link to post
Share on other sites



I have a 1200 page report to summarize.

Would it be acceptable if I mention I will PayPal someone that creates the solution we are looking for?

Share this post


Link to post
Share on other sites

After opening a file

#include<Array.au3>
#include<File.au3>
$message = "Choose a file."
$inFile = FileOpenDialog($message, "@AppDataDir", "DATA (*.csv;*.txt)", 1 + 4 )

If @error Then
    MsgBox(4096,"","No File(s) chosen")
EndIf

$file = FileOpen($inFile, 0)

; Check if file opened for reading OK
If $file = -1 Then
    MsgBox(0, "Error", "Unable to open file.")
    Exit
EndIf

I read in the Report Header - I'll save that to work on later

Dim $mwArray[1]
$mwArray[0] = "test.txt"

;$file = FileOpen("test.txt", 0)


; 1st 33 lines
$x = 0
While $x < 33
    $x = $x + 1
    $line = FileReadLine($file,$x)
    _ArrayAdd( $mwArray, $line)
WEnd
$mwarray = _ArrayTrim( $mwArray, 6, 1)
_ArrayDisplay( $mwArray, "Updated Array" )
;

Now I read in the Product information. I probably make this a function and load this data into an array

$x = $x + 5
$mytext = FileReadLine($file,$x)
$myDept=StringMid($mytext,1,4)
$myLc=StringMid($mytext,5,3)
$myItemNum=StringMid($mytext,8,18)
$myItemDes=StringMid($mytext,26,30)
$myUnit=StringMid($mytext,60,5)
$myOnhand=StringMid($mytext,67,9)
$myAval=StringMid($mytext,77,9)
$myMin=StringMid($mytext,87,9)
$myMax=StringMid($mytext,97,9)
$myOnorder=StringMid($mytext,107,9)
$myOnhold=StringMid($mytext,117,9)
$myCost=StringMid($mytext,127,9)
$myRetail=StringMid($mytext,137,6)
$x = $x + 1
$mytext = FileReadLine($file,$x)
$myCents=StringLeft($mytext,2)
$myRetail = $myRetail&$myCents
MsgBox(0,"",$myDept &@TAB& $myLc &@TAB& $myItemNum &@TAB& $myItemDes &@tab& $myUnit)
MsgBox(0,"",$myOnHand &@TAB& $myAval &@TAB& $myMin &@TAB& $myMax &@tab& $myOnorder &@TAB& $myOnhold &@TAB& $myCost &@TAB& $myRetail )

Next look at the sales data for that product. I want to strip out just the Months and Unit per month

the Do..Until loop with $i is an abartary number until I figure the right itenaration.

Dim $myMonths[40]
Dim $mySales[40]
$i = 0
$mm = 0
Do
  $i = $i + 1
  $x = $x + 1
  $mytext = FileReadLine($file,$x)
 ;find end of month data chunk
 ;could be "Month stock average"
 ;could be month and time and page printed
 ;could be new line with location = 04
  $result = StringInStr($mytext, "Month stock average")
  if $result  > 1 then
;   MsgBox(0, "Search result:", $result)
  Else
    $myArray = StringSplit($mytext, '    ', 1)
;   _ArrayDisplay( $myArray, "_ArrayDisplay() Test" )
;   MsgBox(0, "result:", $myArray[0])
    $ii = $myArray[0]
    $xx=2
    Do
        $mm = $mm + 1
        $myMonths[$mm] = $myArray[$xx]
        $xx=$xx+1
        $mySales[$mm] = $myArray[$xx] 
        $xx=$xx+1
;   MsgBox(0, $mm, $myMonths[$mm] & $xx)        
    Until $xx=10    
;   Until $xx=$ii
;             _ArrayDisplay( $myMonths, "Array created" )
  EndIf


Until $i = 10

              _ArrayDisplay( $myMonths, "Array created" )
              _ArrayDisplay( $mySales, "Array created" )

The following step would be to load this chunk into Excel, and start again on the next Product info

Share this post


Link to post
Share on other sites

As to the PDF to TXT converter, all I can do for now is

#include <GUIConstants.au3>


$oIE = ObjCreate("Shell.Explorer.2")

; Create a simple GUI for our output
GUICreate ( "Embedded Web control Test", 640, 580,(@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)
$GUIActiveX      = GUICtrlCreateObj   ( $oIE,     10, 40 , 600 , 360 )
$GUI_Button_Stop    = GuiCtrlCreateButton   ("Stop",    330, 420, 100,  30)

GUISetState ()    ;Show GUI

$oIE.navigate("http://www.fileformat.info/convert/doc/pdf2txt.htm")

; Waiting for user to close the window
While 1
    $msg = GUIGetMsg()
   
    Select
        Case $msg = $GUI_Button_Stop
            $oIE.Stop
    EndSelect
   
Wend

GUIDelete ()

Exit

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0