Reading and Formating Text File Sales Data

[wutzke]

This script I'm working on takes a text file with sales data and tried to load that data into an array--ready to dump into Excel

1st it read the text file...ok

Formats an array to hold data...ok

load the sales data into an array...ok

sort that array...ok

But there are some months that won't have sales data and the array is formatted to back before the report has current data

I can't seem to populated the array with dates for the month with no sales data

The script is pretty sloppy with loads of msgboxes for degugging

expandcollapse popup

#include <Array.au3>
#include <Date.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

;

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



;get 5th line for date
$myRptDate=StringMid($mwArray[5],69,10)
    MsgBox(64, "report date:", $myRptDate)

;get 19th line for number of months
$myRptRun=StringMid($mwArray[19],34,4)
;   MsgBox(64, "report run:", $myRptRun)



Dim $ItemDetail[20]
_getItemDetail()
;             _ArrayDisplay( $ItemDetail, "Array created" )

; format and fill into months
$start_Date = "Sep '06"
$I_Date1 = $start_Date
$I_Date = $I_Date1


    MsgBox(64, "report run:", $I_Date)

Dim $myMonths[$myRptRun+6]
Dim $mySales[$myRptRun+6]
Dim $mySalesData[$myRptRun+6]
Dim $awArray[$myRptRun+7]
Dim $avArray[$myRptRun+7]
_fillinEmptyMonths($I_Date)


for $this = 0 to UBound($awArray)-1
    $avArray[$this] = $awArray[$this] 
;   MsgBox(64,$this,$avArray[$this])
Next    
_ArrayDisplay( $avArray, "avArray  Unsorted" )
_ArrayDisplay( $awArray, "awArray  Unsorted" )


$i = 0
$mm = 0
Do
  $i = $i + 1
  $x = $x + 1
  $mytext = FileReadLine($file,$x)
;     MsgBox(0, "Search result found:", $mytext)
 ;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 found:", "Month stock average")
  Else
    $myArray = StringSplit($mytext, '    ', 1)
;   _ArrayDisplay( $myArray, "_ArrayDisplay() Test" )
;   MsgBox(0, "result:", $myArray[0])
    $ii = $myArray[0]
    $xx=2
    Do
        $mm = $mm + 1
;        MsgBox(64, $mm, "*" & $mm &"*")    
        $myMonths[$mm] = $myArray[$xx]
        $I_Date=$myMonths[$mm]
;  MsgBox(64, $mm, "*" & $myArray[$mm] &"*")    
        $xx=$xx+1
        $mySales[$mm] = stringleft($myArray[$xx],6)
        $xx=$xx+1
        if StringStripWS($myMonths[$mm], 8) <> "" then
;           MsgBox(0,"", "String only contained whitespace characters.")
                        _toDate($I_Date)
            $mySalesData[$mm]= $I_Date & "|" & $mySales[$mm]
        Else
;           _toDate($I_Date)
;           $mySalesData[$mm]= $I_Date & "|" & $mySales[$mm]
        EndIf   
    
;  MsgBox(0, $mm, $mySalesData[$mm])        
    Until $xx=10    
;   Until $xx=$ii
;             _ArrayDisplay( $myMonths, "Array created" )
  EndIf


Until $i = 10



_ArrayDisplay( $avArray, "avArray  Unsorted" )
_ArrayDisplay( $awArray, "awArray  Unsorted" )

_replaceSalesMonths()

_ArrayDisplay( $avArray, "avArray  Unsorted" )
_ArraySort( $mySalesData,1)
_ArrayDisplay( $mySalesData, "mySalesData Sorted" )



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





FileClose($file)





Func _getItemDetail()
$x = $x + 5
$mytext = FileReadLine($file,$x)
$ItemDetail[1]=StringMid($mytext,1,4)
$ItemDetail[2]=StringMid($mytext,5,3)
$ItemDetail[3]=StringMid($mytext,8,18)
$ItemDetail[4]=StringMid($mytext,26,30)
$ItemDetail[5]=StringMid($mytext,60,5)
$ItemDetail[6]=StringMid($mytext,67,9)
$ItemDetail[7]=StringMid($mytext,77,9)
$ItemDetail[8]=StringMid($mytext,87,9)
$ItemDetail[9]=StringMid($mytext,97,9)
$ItemDetail[10]=StringMid($mytext,107,9)
$ItemDetail[11]=StringMid($mytext,117,9)
$ItemDetail[12]=StringMid($mytext,127,9)
$ItemDetail[13]=StringMid($mytext,137,6)
$x = $x + 1
$ItemDetail[14]= FileReadLine($file,$x)
$ItemDetail[15]=StringLeft($mytext,2)
$ItemDetail[16]=stringleft($ItemDetail[12],6) & stringleft($ItemDetail[14],2)
EndFunc

func _replaceSalesMonths()
      $as=UBound($avArray)-1
for $yy = 0 to $as
    $Ldate=StringLeft($avArray[$yy],7)
;   _toDate($Ldate)
    $avArray[$yy]=StringReplace ( $avArray[$yy],StringLeft($avArray[$yy],7), $Ldate) 
next    
_ArrayDisplay( $avArray, "avarry" )
for $zz = 1 to $myRptRun
    for $yy = 0 to 4
;       MsgBox(1,"compare",$avArray[$yy] & " to " & $mySalesData[$zz])
        if $awArray[$zz] = stringleft($avArray[$yy],10) then
            $awArray[$zz] = $avArray[$yy]
        endif
    next
next    
_ArrayDisplay( $awArray, "populated " )
EndFunc


Func _fillinEmptyMonths($U_Date)
    _toDate($U_Date)
    MsgBox(64,"Udate",$U_Date)
$I_months = $myRptRun+6
;Dim $awArray[$I_months+1]
$awArray[0]=$U_Date
for $z = 1 to $I_months
    $sNewDate = _DateAdd( 'M',-1, $U_Date)
    $U_Date = $sNewDate
$awArray[$z]=$sNewDate  
Next
_ArrayDisplay( $awArray, "empty months" )
EndFunc

Func _toDate(ByRef $sDate)
    Global $A_Mon[13] = ["", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
    $X_Date = $sDate
    $a_Date = StringSplit($X_Date, " '")
    $Mon = 0
    For $x = 1 To 12
        If $a_Date[1] = $A_Mon[$x] Then
            $Mon = StringRight("0" & $x, 2)
            ExitLoop
        EndIf
    Next
    $sDate = "20"  & $a_Date[3] & "/" & $Mon & "/01"
    Return
EndFunc  ;==>_toDate

test.txt

[/dev/null]

This script I'm working on takes a text file with sales data and tried to load that data into an array--ready to dump into Excel

1st it read the text file...ok

Formats an array to hold data...ok

load the sales data into an array...ok

sort that array...ok

But there are some months that won't have sales data and the array is formatted to back before the report has current data

I can't seem to populated the array with dates for the month with no sales data

The script is pretty sloppy with loads of msgboxes for degugging

the script is O.K., the error description makes me wonder!! I don't understand the problem. Maybe you should tell us where in the code (or input date) you see a problem...

Cheers

Kurt