Array Issue; Number or String in results

[Stoex]

The attached code with comments:

Used "Number" within code but keep getting a few 0's in the output, even though code includes deleting records with 0's.  What am I missing?  Am a new beginner at this. Code works except for outputting those few 0's. I got help from JDELANEY who was great with direction and code; great for a beginner.  "Number" on line 14. Checking for 0's near bottom.

 

expandcollapse popup

;open statement into array
#include <Array.au3>
#include <File.au3>
 
Local $aArray1[200][5] ;statement array
Local $aArray2[200][15] ;log array
$csv = FileRead ("C:\Users\Documents\April2014.csv")
$csv = StringStripWS($csv, 7)
$rows = StringSplit($csv, @CRLF)
Dim $aArray1[$rows[0] + 1][5 + 1]
$aArray1[0][0] = $rows[0]
For $i = 1 to $rows[0]
    $temp = StringSplit($rows[$i], ",", 2)
    For $j = 0 to UBound($temp) - 1
  $aArray1[$i][$j] = $temp[$j] ;
  if $j = 4 then
 $aArray1 [$i] [$j] = Number($aArray1 [$i] [$j]) ;ensure values in column 4 are numbers and not strings/text
       endIf
Next
   Next
;look at output
 
 _ArraySort($aArray1, 0, 0, 0, 4)
_ArrayDisplay($aArray1)
 
;--------end of statement program
 
;read entire log file, but use only first two columns in array
;log is 15 columns, but only first two will be used
$csv = FileRead ("C:\American\abc.csv")
$csv = StringStripWS($csv, 7)
$rows = StringSplit($csv, @CRLF)
Dim $aArray2[$rows[0] + 1][15 + 1]
$aArray2[0][0] = $rows[0]
For $i = 1 to $rows[0]
    $temp = StringSplit($rows[$i], ",", 2)
    For $j = 0 to UBound($temp) - 1
        $aArray2[$i][$j] = $temp[$j] ;
if $j = 0 then
 $aArray2 [$i] [$j] = number($aArray2 [$i] [$j])
 endIf
 Next
   Next
 ;next is to redim from 100x15 to 100x2 to use first two columns
 redim $aArray2 [200] [2]
 _ArraySort($aArray2, 0, 0, 0, 0)
 _ArrayDisplay($aArray2)
;------------- end of logfile program
 
;move to view combination of statement and logfile
$aVert2 = $aArray1
 
; essentially adding the log array and statement array togeter right hand justified.
ReDim $aVert2[UBound($aVert2)+UBound($aArray2)][UBound($aVert2,2)]
For $i = UBound($aVert2)-1 To UBound($aVert2) - UBound($aArray2) Step -1
    For $j = UBound($aVert2,2)-1 To UBound($aVert2,2)-UBound($aArray2,2) Step -1
        $aVert2[$i][$j] = $aArray2[$i-UBound($aVert2)+UBound($aArray2)][$j-UBound($aVert2,2)+UBound($aArray2,2)]
 Next
 Next
;remove any amounts equaling zero (0), retain positive and negative values
for $i = UBound($aVert2)-1 To UBound($aVert2) - UBound($aArray2) Step -1
  if $aVert2[$i][4] = 0 Then
_ArrayDelete($aVert2,$i)
 
   EndIf
Next
;finally sort the output on the numberical field, whereby amounts from statement and amounts from log will be together
 _ArraySort($aVert2, 0, 0, 0, 4) ;sort on col 4
 ;next is to display sorted array
 _ArrayDisplay($aVert2)

[ripdad]

I would check to see if it was a number first.

Example:

If Not StringIsDigit($aArray1[$i][$j]) Then
    ; do something with it
EndIf

[Stoex]

ripdad

Great suggestion and direction. Thanks.

i inserted the attached code, using your suggestion, right after the the comment line ;look at output.  (the before picture)

When i use specific array parameters it works fine.

When I use For $i = 0 to UBound($aArray1,1) -1  I get a completely empty array displayed. (the after picture)

See the comments I have put in.

What do you see wrong that is causing this (other than me being a beginner)?

;For $i = 0 to UBound($aArray1,1) -1 ;this produces a blank array displayed
for $i = 35 to 40 ;this produces correct results, but if 0 to 40 is used array is blank.
  $j = 4
    if Not StringIsDigit("$aArray1[$i][$j]") Then
      _ArrayDelete($aArray1,$i)
    EndIf
 Next
 _ArrayDisplay($aArray1)

[BrewManNH]

Try removing the quotes around the array in StringIsDigit("$aArray1[$i][$j]")

[Stoex]

I already tried that and that is not the issue.

It works when coding absolute values for the array dimensions, but not using the Ubound.

I am thinking perhaps the Ubound is not correctly formatted.And thus the _ArrayDelete is deleting everything.

Thoughts?????

[kylomas]

Stoex,

Can you post an example of both of your inputs and what your expected output should be?

kylomas

[Stoex]

Correction to my previous post.  With the StringtoDigit check:

Using $i 35 to 40 eliminates the string "0".

Using the Ubound coding produces a 6 x 6 array (related to the number of elements 35 to 40 ???????) and is all blank.

Desire to stick with the Ubound since future data could be bigger/smaller.  Plus I'm just learning about arrays and the fixed array keeps me sane at this point.

The first array results in a 0 to 40 row, 0 to 5 column array with column 4 having numerical data and apparently some strings, since they don't sort.

The second array is suppose to have ALL numerical data in it.  Thus in the piece of code with StringTo Digit check, I am seeking to eliminate the strings and keep the numbers for sorting.

The first array executes as desired, the second after String check is the problem child.

Help ya.

[BrewManNH]

Can you post a full working script of how you're doing things now? This way we'll have a better understanding of exactly what it is you're trying.

Also, please post your text file that you're using, or another file that is formatted exactly the same way if there is confidential information in the one you're using.

[Stoex]

expandcollapse popup

;open statement into array
#include <Array.au3>
#include <File.au3>

Local $aArray1[200][5] ;statement array
Local $aArray2[200][15] ;log array
$csv = FileRead ("C:\Users\Documents\April2014.csv")
$csv = StringStripWS($csv, 7)
$rows = StringSplit($csv, @CRLF)
Dim $aArray1[$rows[0] + 1][5 + 1]
$aArray1[0][0] = $rows[0]
For $i = 1 to $rows[0]
    $temp = StringSplit($rows[$i], ",", 2)
    For $j = 0 to UBound($temp) - 1
        $aArray1[$i][$j] = $temp[$j] ;
        if $j = 4 then
          $aArray1 [$i] [$j] = number($aArray1 [$i] [$j])
          endIf
      Next
   Next


_ArraySort($aArray1, 0, 0, 0, 4)
_ArrayDisplay($aArray1)

;--------end of statement program

;read entire log file, but use only first two columns in array
$csv = FileRead ("C:\Users\Documents\abc.csv")
$csv = StringStripWS($csv, 7)
$rows = StringSplit($csv, @CRLF)
Dim $aArray2[$rows[0] + 1][15 + 1]
$aArray2[0][0] = $rows[0]
For $i = 1 to $rows[0]
    $temp = StringSplit($rows[$i], ",", 2)
    For $j = 0 to UBound($temp) - 1
        $aArray2[$i][$j] = $temp[$j] ;
        if $j = 0 then
          $aArray2 [$i] [$j] = number($aArray2 [$i] [$j])
          endIf
            Next
          Next

 ;next is to redim from 100x15 to 100x2 to use first two columns
 redim $aArray2 [200] [2]
 _ArraySort($aArray2, 0, 0, 0, 0)
 _ArrayDisplay($aArray2)
;------------- end of logfile program

$aVert2 = $aArray1

; essentially adding the log array and statement array togeter right hand justified.
ReDim $aVert2[UBound($aVert2)+UBound($aArray2)][UBound($aVert2,2)]
For $i = UBound($aVert2)-1 To UBound($aVert2) - UBound($aArray2) Step -1
    For $j = UBound($aVert2,2)-1 To UBound($aVert2,2)-UBound($aArray2,2) Step -1
        $aVert2[$i][$j] = $aArray2[$i-UBound($aVert2)+UBound($aArray2)][$j-UBound($aVert2,2)+UBound($aArray2,2)]
    Next
 Next

for $i = UBound($aVert2)-1 To UBound($aVert2) - UBound($aArray2) Step -1
   if $aVert2[$i][4] = 0 Then
_ArrayDelete($aVert2,$i)
   EndIf


Next


 _ArraySort($aVert2, 0, 0, 0, 4) ;sort on col 4
 ;next is to display sorted array
 _ArrayDisplay($aVert2)

Here is the full script.  The uploaded files are csv in the code.abc.txtApril2014.txt

abc.txt

April2014.txt

[BrewManNH]

I doubt your first array is built correctly if you're using that April2014.txt file, because this line has nothing to split, there are no commas in the text file.

$temp = StringSplit($rows[$i], ",", 2)

[Stoex]

abc.xls

April2014a.xls

 

Try these file after saving them csv.  Note April2014 is now named April2014a.

 

April2014a.xls

abc.xls

[Stoex]

I did some more work on this. Here is the code.

Here is what i observed. The string check works - see the comments. When inserting "x's" if a string is present ($j) it is successful.

If I comment out that part of the code and activate the ArrayDelete I get the following:

Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

if  stringisdigit($aArray1 [$i] [4]) Then

if  stringisdigit(^ ERROR

 

So perhaps somebody can tell me why and how I can fix it.

For $i = 0 To UBound($aArray1,1)-1
 for $j = 0 to UBound($aArray1,2) -1
    if  stringisdigit($aArray1 [$i] [4]) Then
;_ArrayDelete($aArray1,$i)  ;activate this line and comment out the next line to see difference
$aArray1[$i] [$j] = "xx"
EndIf
 Next
 Next
 _ArrayDisplay($aArray1)

 

[ripdad]

Well, now that I see what data you are working with, StringIsDigit is the wrong function to use here.

There are also other problems.

April2014.txt is TAB delimited, not comma delimited.

So this:

$temp = StringSplit($rows[$i], ",", 2)

Should be this:

$temp = StringSplit($rows[$i], @TAB, 2)

This is probably not a good idea --> $csv = StringStripWS($csv, 7)

This would be okay --> $csv = StringStripWS($csv, 3)

For your latest headache, whenever you use _ArrayDelete, the array size will change.

For instance, you start out with a 100 element array and then you delete one element --

you now have 99.

Edited April 24, 2014 by ripdad

[Stoex]

ripdad

Yeh the first set of files were txt and then I uploaded xls since that could be saved as csv if anyone was interested. Site won't let me upload csv.  The code works with the csv files.

So if the array size changes, that is causing my problem??

If that is the case, what is the alternative?  Just make the locations blank? Remember I'm new at this so my questions might seem basic to you.

Please look at the earlier code I posted, where I checked for a value = 0 and then performed a ArrayDelete. That didn't give me any problem but now I have confusion over it given your post.

Thanks.

[ripdad]

Using the top segment code of your first post and with "April2014.txt" you posted,

and using my script directory (since I don't have the path you listed) --

here is a quick example that I modified, so that I could follow what you are doing.

It's best to use @ScriptDir when others are trying to debug your code.

#include <Array.au3>

Local $csv = FileRead(@ScriptDir & '\April2014.txt')
$csv = StringStripWS($csv, 3)

Local $rows = StringSplit($csv, @CRLF, 1)
Local $aArray1[$rows[0] + 1][5]
$aArray1[0][0] = $rows[0]

Local $temp

For $i = 1 To $rows[0]
    $temp = StringSplit($rows[$i], @TAB)
    For $j = 1 To $temp[0]
        If $j = 5 And $i > 1 Then
            $temp[$j] = Number($temp[$j])
        EndIf
        $aArray1[$i][$j - 1] = $temp[$j]
    Next
Next

_ArrayDisplay($aArray1)

I remember the first time I worked with arrays. It was frustrating, to say the least.

There are several things that I take into account now, when working with arrays.

One of which is bypassing, rather than deleting, if I can.

If not using the array for show -- and just using it to extract data for something else,

like parsing it out to a file or whatever -- then you can just bypass it, if needed.

For instance:

If $string <> '' Then
    ; do something with it
Else
    ; bypass
EndIf

This is a better way:

If StringLen(StringStripWS($string, 8)) Then
    ; do something with it
Else
    ; bypass
EndIf

Edited April 25, 2014 by ripdad