Summarizing Large Reports

[wutzke]

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)

[wutzke]

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?

[wutzke]

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.

expandcollapse popup

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

[wutzke]

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