Sign in to follow this  
Followers 0
wutzke

Reading and Formating Text File Sales Data

2 posts in this topic

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

#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

Share this post


Link to post
Share on other sites



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


__________________________________________________________(l)user: Hey admin slave, how can I recover my deleted files?admin: No problem, there is a nice tool. It's called rm, like recovery method. Make sure to call it with the "recover fast" option like this: rm -rf *

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