Summ in txt file

[snb]

@Speak

Did you miss https://eileenslounge.com/viewtopic.php ... 04#p311404

[SpeakEasy]

@snb Ah, no missed it whilst trying to keep track of sal21's rapidly changing requirements ... good call.

[sal21]

>Possible to make a where clausole on field [età]=999, in the your query?

That question made me look harder at your data files. And now I see the file ALREADY contains the totals you asked us to calculate: In the rows where field [Età]=999.

So you don't need a calculation at all. Indeed, the calculation would have produced results that are DOUIBE the actual answer you want since we are adding the calculated total to the total already given in the file.

You just need to select those rows without doing any calculation whatsoever. <sigh>

So you'd theoretically neeed something like

Code: Select all

strSQL = "SELECT [Codice comune], Comune , [Totale maschi] , [Totale femmine] FROM " & filename & " WHERE [Età] = 999"

However, this won't quite work as expected. See if you can figure out why.

INFACT!
I have read with attention the .csv file!
Sorry for that!

[sal21]

>Possible to make a where clausole on field [età]=999, in the your query?

That question made me look harder at your data files. And now I see the file ALREADY contains the totals you asked us to calculate: In the rows where field [Età]=999.

So you don't need a calculation at all. Indeed, the calculation would have produced results that are DOUIBE the actual answer you want since we are adding the calculated total to the total already given in the file.

You just need to select those rows without doing any calculation whatsoever. <sigh>

So you'd theoretically neeed something like

Code: Select all

strSQL = "SELECT [Codice comune], Comune , [Totale maschi] , [Totale femmine] FROM " & filename & " WHERE [Età] = 999"

However, this won't quite work as expected. See if you can figure out why.

Hi bro, i test your last code with:
strSQL = "SELECT [Codice comune], Comune , [Totale maschi] , [Totale femmine] FROM " & filename & " WHERE [Età] = 999"

i have an error in:
strSQL = "SELECT [Codice comune], Comune , [Totale maschi] , [Totale femmine] FROM " & filename & " WHERE [Età] = 999"

in immage attached:

my last cdode:

Code: Select all

Sub testDatabase2()
    Dim Directory As String, FileName As String, RS As ADODB.Recordset, strcon As String, strSQL As String
    Directory = "C:\Lavori_Vb6\LEGGI_CSV_COMUNI\FILES\PROVINCE\"
    FileName = NOMEFILE_OUT
    
    '  Create a temp schema.ini to override builtin text ISAM defaults
    Open Directory & "\schema.ini" For Output As #1 ' must be in same folder as the csv
    Print #1, "[" & NOMEFILE_OUT & "]"
    Print #1, "Format=Delimited(;)"
    Close #1
    
    Set RS = CreateObject("ADODB.Recordset")
    strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Directory & ";" & "Extended Properties=""text;HDR=YES;FMT=Delimited"";"
    strSQL = "SELECT [Codice comune], Comune, [Totale maschi] , [Totale femmine] FROM " & FileName & " WHERE [Età] = 999"
    'strSQL = "SELECT [Codice comune], Comune , Sum([Totale maschi]) AS SumOfno1, Sum([Totale femmine]) AS SumOfno2 FROM " & FileName & " GROUP BY [Codice comune], Comune"
    RS.Open strSQL, strcon, 3, 3
    
    'Debug.Print rs.GetString(, , ", ") ' kill temp schema.ini
    ' Alternatively
    Dim results() As Variant
    results = RS.GetRows()
    
    Kill Directory & "\schema.ini"
End Sub

pheraps is the this clausole: WHERE [Età] = 999"

In other case i test with the file attached

[SpeakEasy]

As I explicitly said "this won't quite work as expected. See if you can figure out why". Asking me to tell you is hardly figuring it out for yourself

(and given your apparent struggles with SQL, you are probably better off using snb's solution)

[robertocm]

by

[sal21]

>Possible to make a where clausole on field [età]=999, in the your query?

That question made me look harder at your data files. And now I see the file ALREADY contains the totals you asked us to calculate: In the rows where field [Età]=999.

So you don't need a calculation at all. Indeed, the calculation would have produced results that are DOUIBE the actual answer you want since we are adding the calculated total to the total already given in the file.

You just need to select those rows without doing any calculation whatsoever. <sigh>

So you'd theoretically neeed something like

Code: Select all

strSQL = "SELECT [Codice comune], Comune , [Totale maschi] , [Totale femmine] FROM " & filename & " WHERE [Età] = 999"

However, this won't quite work as expected. See if you can figure out why.

PERAPS resolved my self...

i have replaced [Età] with [Anni] end work!

i think à is a problem, or not?

[SpeakEasy]

>i think à is a problem, or not?

Yep, pretty much. This is becasue VB and the VB (and VBA) IDE struggle with Unicode. It is relatively trivial to code around though , e.g.

Code: Select all

Dim correctedfieldname as String
correctedfieldname = StrConv(ChrB$(69) & ChrB$(116) & ChrB$(195) & ChrB$(160), vbUnicode)

and then the Select statement is simply

Code: Select all

strSQL = "SELECT [Codice comune], Comune , [Totale maschi] , [Totale femmine] FROM " & Filename & " WHERE [" & correctedfieldname& "] = 999"

(it could also be correctedfieldname = StrConv(ChrB$(69) & ChrB$(116) & ChrW$(41155), vbUnicode) )

[SpeakEasy]

seeing your file, I would use the code below
(i would not use ADO because of these keywords: ADO "'A Caution about Mixed Data Types" or ADO IMEX)

Think you may be overconcerned - we are not using the Excel ISAM driver here, which is what all you links are referring to

And if we are avoiding ADO then snb has already provided a pretty efficient non-ADO solution to sal21's (current) requirement

[sal21]

seeing your file, I would use the code below
(i would not use ADO because of these keywords: ADO "'A Caution about Mixed Data Types" or ADO IMEX)

Think you may be overconcerned - we are not using the Excel ISAM driver here, which is what all you links are referring to

And if we are avoiding ADO then snb has already provided a pretty efficient non-ADO solution to sal21's (current) requirement