How to summed up and count numeric column data in text file if file is having delimiter ',' comma

[jainprince810]

I want to take sum of 'salary' column and count the total no of rows in Emp.txt file.

Note: 1. delimiter is ',' comma


Can you please help me on this. I need code in VBA macro only. Please find attached sample file.

 

[vletm]

jainprince810
Click somewhere ...

 

[p45cal]

In the attached, 2 offerings:
1. A button which runs a macro blah which you'll need to tweak in 2 places to refer to the right text file - see notes in the code.
2. A Power Query solution that also needs tweaking to identify the text file; this picture is an attempt to show you what you need to do. Once that one-off process is done, just right click in that small green table and choose Refresh.

 

[jainprince810]

Thanks p45cal

I am using your 1st solution.

Can you please let me know if this power query will work if CSV file is having 5 millions of rows ?

 

[p45cal]

Can you please let me know if this power query will work if CSV file is having 5 millions of rows ?

I can't imagine why not.
Why don't you try it?
All solutions suggested should work on such a file.

 

[jainprince810]

Is there any alternative for "Provider=Microsoft.Jet.OLEDB.4.0". Since this is not working on my client provided machine. Already added required 'References'.
Below error is coming

Run-Time error '3706'
Application - defined or object - defined error

 

[p45cal]

Try changing to:
"Provider=Microsoft.Ace.OLEDB.12.0;Data Source="
or:
"Provider=Microsoft.Ace.OLEDB.15.0;Data Source="
You shouldn't need to add any references.
Otherwise, why not stick to Power Query?