csv summaries in excel

[hilz]

Hi all.
I have this situation and I hope someone would be able to give me some
advise about how to tackle it.

I have a program that produces reports in CSV format.
Users view the CSV files using Excel.
the data in the csv files is not exactly what the users like to see.
They want to see it in a different way such that rows are consolidated
(and sometimes summed up) into less number of rows according to some
complex rules.

I am not sure how to tackle this problem. What are my options.
I have Microsoft Visual Studio 2003 in addition to MS VS Tools for
Office 2003.

Any ideas are greatly appreciated.
I need to know the general picture for now. What do i need to develop?
An excel sheet that opens the CSV file and view it internally?
or an external program that opens the csv file and writes it out as an
excel sheet? or some other method ?

thanks

 

[Randy Harmelink]

How about creating a pivot table for them, using the CSV file as an
external data source? Or, let them drag and drop the fields into the
pivot table layout, any way they want?

 

[hilz]

How about creating a pivot table for them, using the CSV file as an
external data source? Or, let them drag and drop the fields into the
pivot table layout, any way they want?

I am afraid the structure of the csv file (specifically the headers)
does not allow the use of pivot tables. I am attaching a sample here for
your reference. I renamed it to .txt file just in case csv extensions
are not allowed.

In addition to the way the headers are structured, the rules for
combining rows is very complex and depends on many factors that need to
be handled by some sort of program.

any help is greatly appreciated.




,,,,,,,,,,,,,,,Payitem,"0001","0002","0001","0002",,
,,,,,,,,,,,,,,,Description,"item 1","item 2","item 1","item 2",,
,,,,,,,,,,,,,,,Unit,LF,LF,LF,LF,,
Chain,Minimum Absolute Station,Minimum Station,Minimum Region,Minimum Formatted Station,Maximum Absolute Station,Maximum Station,Maximum Region,Maximum Formatted Station,Minimum Offset,Maximum Offset,Run,Remarks,Description,Extended Description,Phase,phase 1,phase 1,phase 2,phase 2,,
FINAL,452.19,952.19,1,9+52.19 R 1,1282.40,1982.40,2,19+82.40 R 2,321.206,248.955,"alpha","","","",,416.50,,,,,
FINAL,2739.00,3439.00,2,34+39.00 R 2,3051.38,3751.38,2,37+51.38 R 2,-435.079,-284.486,"alpha","","","",,431.10,,,,,
FINAL,1770.32,2470.32,2,24+70.32 R 2,2071.59,2771.59,2,27+71.59 R 2,370.582,283.861,"alpha","","","",,,578.40,,,,
FINAL,0.00,500.00,1,5+00.00 R 1,3764.86,4464.86,2,44+64.86 R 2,55.0,88.0,"3","4","5","6",,,,90.00,,,
FINAL,5478.33,6078.32,3,60+78.33 R 3,6326.33,6826.33,4,68+26.33 R 4,-248.196,-226.438,"alpha","","","",,,,764.80,,,
FINAL,2372.59,3072.59,2,30+72.59 R 2,2944.48,3644.48,2,36+44.48 R 2,316.887,291.61,"alpha","","","",,,,,579.30,,

 

[Randy Harmelink]

I guess my question would be why the reports are created and viewed in
CSV format if they are not useful that way? That is, why even use
EXCEL or CSV files at all?

 

[hilz]

I guess my question would be why the reports are created and viewed in
CSV format if they are not useful that way? That is, why even use
EXCEL or CSV files at all?

Well, they are useful to the users because they take the end result (the
excel file) and use it in some other software.

So any suggestions?

thanks.

 

[Randy Harmelink]

The devil is in the details, but in general I would think you have two
primary directions to look at:

1. Converting the CSV file into a format the users want to see,
possibly even abandoning EXCEL from their usage of the output.
2. Converting the CSV file into a format that EXCEL can manipulate into
various ways that the users want to see the data.

Which direction you go would primarily depend on how much interaction
the users need to have with the converted data -- how many ways they
want to "slice and dice" it.

As long as combining rows of data is complex, I don't see much of a
choice other than to "massage" it for them ahead of time.