jj2007
I found the problem I had with the average.
"Micronesia, Federated States of" has a comma in it.
So this comma in a comma seperated file is in an unusual place.
However because the grammar is correct (comma then space then continue) I can handle it.
Here are all the valid lines with 2000 highlighted.
Code: Select all
#include "file.bi" 'to use Fileexists()
Function loadfile(file As String) As String
If Fileexists(file)=0 Then Print file;" not found":Sleep:End
Dim As Long f=Freefile
Open file For Binary Access Read As #f
Dim As String text
If Lof(f) > 0 Then
text = String(Lof(f), 0)
Get #f, , text
End If
Close #f
Return text
End Function
Sub FindAndReplace(s As String,Find As String,Relpace As String)
Var position=Instr(s,Find)
While position>0
s=Mid(s,1,position-1) & Relpace & Mid(s,position+Len(Find))
position=Instr(position+Len(Relpace),s,Find)
Wend
End Sub
Function isnumber(Byval s As String) As boolean
Static As String * 13 number="-+.0123456789"
Dim As Long ctr,dot,lnum=13
For m As Long=0 To Len(s)-1
If m>0 And s[m]=45 Or s[m]=43 Then Return 0
If s[m]=46 Then dot+=1
For n As Long=0 To lnum-1
If s[m]=number[n] Then ctr+=1:Exit For
Next n
Next
Return ctr<>0 Andalso ctr=Len(s) And dot<2
End Function
Function StringSplit(s_in As String,chars As String,result() As String) As Long
Dim As Long ctr,ctr2,k,n,LC=Len(chars)
Dim As boolean tally(Len(s_in))
#macro check_instring()
n=0
While n<Lc
If chars[n]=s_in[k] Then
tally(k)=true
If (ctr2-1) Then ctr+=1
ctr2=0
Exit While
End If
n+=1
Wend
#endmacro
#macro split()
If tally(k) Then
If (ctr2-1) Then ctr+=1:result(ctr)=Mid(s_in,k+2-ctr2,ctr2-1)
ctr2=0
End If
#endmacro
'================== LOOP TWICE =======================
For k =0 To Len(s_in)-1
ctr2+=1:check_instring()
Next k
If ctr Then Redim result(1 To ctr): ctr=0:ctr2=0 Else Return 0
For k =0 To Len(s_in)-1
ctr2+=1:split()
Next k
'===================== Last one ========================
If ctr2>0 Then
Redim Preserve result(1 To ctr+1)
result(ctr+1)=Mid(s_in,k+1-ctr2,ctr2)
End If
Return Ubound(result)
End Function
Dim As Double t=Timer
Dim As String text=LoadFile("MDG_Export_20181003_012728491.csv")
'Dim As String text=Loadfile("MDG_Export_20181002_120726411.csv")
Redim As String s(),s2(),y()
Dim As Long column
Dim As Long i,i2
Dim As Double d
StringSplit(text,Chr(10),s()) 'split on new line
StringSplit(s(1),",",y()) 'get the header strings
For z As Long=1991 To 2015
If z=2000 Then Color 15,8 Else Color 15,0
i=0:i2=0:d=0
Dim As String yr=Str(z)
For n As Long=Lbound(y) To Ubound(y)
If y(n)=yr Then column=n:Exit For 'get column from header titles
Next
For n As Long=Lbound(s) To Ubound(s)
If Instr(s(n),"Population undernourished, percentage") Then
i2+=1 'total readings
Var cpy=s(n)
FindAndReplace(cpy,", ",Chr(0))
stringsplit(cpy,",",s2())
s2(column)=Trim(s2(column),Chr(34))
If isnumber(s2(column)) Then d+=Val(s2(column)):i+=1 'valid readings
End If
Next n
If z=1991 Then Print "total readings = ";i2;" for Population undernourished, percentage":print
Print "For Year ";yr;tab(20);"average = ";d/i;tab(60);"(";i;" valid readings)"
Print
Next z
Print "Time taken for all runs ";Timer-t
Print "done"
'Micronesia, Federated States of
Sleep
The number of undernourished seems to be falling.