# Excel solution to reporting from CSV file data???



## Slammer (Mar 4, 2006)

Hi 
I've been given the task of looking at the options of reporting on some data presented in CSV files. Basically I will be receiving CSV files, each one containing the results of an assessment of someone. This will include the individuals data such as name, title and work area, the assessors data such as name, working relationship and finally the results of the assessment questions (which are answered by scoring on a rating scale 1-5). 
In an ideal world, I'd like to be able to use these files to create reports, including using charts, to show individual results and results across work area and organisation etc. As much as possible, I would like this to be automated. So, maybe manual importing of the files but the majority of report creating to be automatic. 
With no budget to buy in expert software or consultancy, I'm wondering whether this can be done using Excel. I'm competent with Excel but no expert. I do have access to some expertise though. 
I'm not looking for detailed step by step process at this stage but if it is possible with Excel, the basic functions/processes to be used and any limitations etc. 
Also, are there any alternative solutions which do not require specialist expertise or low cost software options which might give me a good case to plead for funds? 
Many thanks 
Mike


----------



## RSpecianjr (Jan 20, 2010)

Hey Mike,

Short answer is yes it can be done in Excel. Excel is amazing at calculations and good at housing data, but it is not a database. I've used it as such in the past (lack of time spent with Access), and the more infomration the slowing Excel will be. If it is going to be a calculation intensive workbook using wild formulas with more than say 200,000 rows, I would suggest using an actual database program (Access is a good one). If you are looking for quick reports and the workbook will remain fairly small, then Excel will do what you want.

Macro's are probably what you are going to end up using, at least for some of it. What I am thinking is something like this:

1. Paste CSV information into column A of a dump sheet.
2. Run a macro that will separate and organize the data the way you want.
3. Have a worksheet with lookup formulas based on a cell value (this value will be for your "individuals"). Graphs and "individuals" data will autopopulate based on that value.
4. Have a worksheet with lookup formulas based on cell value (this value will be for your "work area"). Graphs and "individuals" data will autopopulate based on that value.

If you want the report to be able to build, you will have to have a date system as well. That way you can reference time frames for data that was entered. If you design the layout of how you want the data presented and some raw data to work withwe can fill in the blanks. Design is by far my weakest point ^_^.

I'll repeat one time just to be thorough. Databases are best in programs made for databases, such as Access. I personally cannot help you with Access very much.

Ciao,

Robert D. Specian Jr.


----------



## Slammer (Mar 4, 2006)

Hey Robert

Thanks for your thorough reply. It gives me exactly what I need to know. I don’t expect the data to be excessive, certainly nowhere near 200,000 rows. Given that and the lack of Access skills available, I think Excel will do the job.

I don't have all the skills to do this all be myself but with some help from colleagues I think I can give some direction and design assistance to produce something that is satisfactory.

Your help is very much appreciated.

Mike


----------



## djaburg (May 15, 2008)

Access isn't really that hard to work with. The nice thing about access is that if the data you're importing is the same each time (formatted that is) then you only have to import it once to create a "template" of how to handle the data and you can import the data repeatedly with minimal effort. Once it's in, you can easily run reports (wizards do quite a bit) if needed, including exporting data to Excel if needed. You'll find that having a db allows you to analyze the data easier than Excel.


----------



## Slammer (Mar 4, 2006)

Thanks for your input djaburg. I'll certainly look at Access. The data will be in the same format so a once only template is appealing. I need to understand the reporting as this is required to be primarily chart based. Once I understand the pro's and con's of both Access and Excel and assess the skill levels required/available I'll fine tune the detail and hopefully create the reports. I may be back for help!

Thanks again

Mike


----------



## OleRasmusen (Dec 21, 2010)

Mike
If you are using Excel 2007 - then post like minimu 2 CSV files you want to import - fonny info - but correct structure. I will write a small VBA program that allows you use the Windows API for file import - I will allow you to multiselect files. Then import them into excel. In terms of how to chart data. I have today posted a question regarding charts - how to unjoin data points in a series of data. I uploaded code that generate a chart - so take a look at that in terms of how to add a chart - reading data in and so fourth. Not sure how much VBA experience you have - I am learning - the hard way.

If you have like 200K (Excel 2003 is limited to 2^16 rows - so 2007 is a minimum) rows then make sure you set the sheet as an object - that way you can do all the work on the object and then release the memory in one go when you are done. Anyway - Not sure if you ready to get into coding - or simply was looking at options.
Cheers:wink:

PS My sheets have up to 100K rows - you really gotta manage your resources. Especially if you are making charts - I think the max number of data points in a single series is 32K


----------



## Slammer (Mar 4, 2006)

Hi Ole

Thanks for your input. The chart stuff is very helpful. VBA is something I've not got to grips with yet but will be able to get some help with this. I think I've pretty much got my head round what can be done. Just need to pull it all together now.

Thanks again

Mike


----------

