Summ in txt file

snb
4StarLounger
Posts: 542
Joined: 14 Nov 2012, 16:06

Re: Summ in txt file

Post by snb »


User avatar
SpeakEasy
4StarLounger
Posts: 512
Joined: 27 Jun 2021, 10:46

Re: Summ in txt file

Post by SpeakEasy »

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

User avatar
sal21
PlatinumLounger
Posts: 4332
Joined: 26 Apr 2010, 17:36

Re: Summ in txt file

Post by sal21 »

SpeakEasy wrote:
11 Oct 2023, 12:36
>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!

User avatar
sal21
PlatinumLounger
Posts: 4332
Joined: 26 Apr 2010, 17:36

Re: Summ in txt file

Post by sal21 »

SpeakEasy wrote:
11 Oct 2023, 12:36
>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
You do not have the required permissions to view the files attached to this post.

User avatar
SpeakEasy
4StarLounger
Posts: 512
Joined: 27 Jun 2021, 10:46

Re: Summ in txt file

Post by 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
Lounger
Posts: 43
Joined: 07 Jun 2023, 15:34

Re: Summ in txt file

Post by robertocm »

by
Last edited by robertocm on 16 Oct 2023, 15:24, edited 2 times in total.

User avatar
sal21
PlatinumLounger
Posts: 4332
Joined: 26 Apr 2010, 17:36

Re: Summ in txt file

Post by sal21 »

SpeakEasy wrote:
11 Oct 2023, 12:36
>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?

User avatar
SpeakEasy
4StarLounger
Posts: 512
Joined: 27 Jun 2021, 10:46

Re: Summ in txt file

Post by 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) )

User avatar
SpeakEasy
4StarLounger
Posts: 512
Joined: 27 Jun 2021, 10:46

Re: Summ in txt file

Post by SpeakEasy »

robertocm wrote:
13 Oct 2023, 15:00
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

User avatar
sal21
PlatinumLounger
Posts: 4332
Joined: 26 Apr 2010, 17:36

Re: Summ in txt file

Post by sal21 »

SpeakEasy wrote:
14 Oct 2023, 15:22
robertocm wrote:
13 Oct 2023, 15:00
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
:clapping: