Web Development

Ask something
Discussion Locked

How to sumarize data from a csv

0 helpful
7 replies

by cornejo.alvaro 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi

I need to extract data from a csv file. The file contains 8 csv columns. I'm interested in getting info from only 3 columns: the one that has a name the one that has the product ID and the third that has the quantities sold.

I'll need get a summary of it, for each sales guy how much does he sold for each product.

I've messing around an script but being unable to have something that work. I've been able to filter the 3 columns but have been unable to have the sum of quantities of each product per salesguy.

Any ideas?

7 replies

by miss kitty

How are you working with the file? The easiest way to work with a csv file is to import it into Access and then filter your data with a query. Then you can pull it into excel or word to get the summary.
Cathy

by cornejo.alvaro

Hi

as the file is quite small <100 lines, I open it with the ADO and get the data. Also I need to do this once every month.

by thumbknuckle

Why not import it directly to Excel, and have the columns with irrelevant data hidden? No scripting or filtering needed.

by hain

You should import the csv-file into Excel an use the "Pivot-Table"-Function. This will give you the needed summary instantaneously.

Hain

by tony hopkinson

If you can get it in to a DB,(ODBC Text driver ?) so SQL is available then the query would be something like
Select SalesMan,Product, Sum(Value) as "Total Sales"
From Sales
Group By Salesman,Product
If you are operating on the raw file in a scripting language, then you've got a fair bit of code to write to reinvent this particular wheel.
The OO way would be to create a collection or list class with an add method that took the three values
If Salesman/Product didn't exits add a new item and set the value otherwise find the exitsing and add on the value.
then sort and then display however you are going to do it. As others have said access or excel would do this with a macro and the csv file as parameter and it would save you reiventing this particular wheel.

This discussion is closed to new replies.