Generating Reports from Access and Excel Files?

casals asks: "I'm a computer engineer working at a non-IT company, and there's this thing bothering me: by the end of each job, we have to generate a huge report that's actually a composite of lots of minor reports, each one of them made using a different software. Since the softwares used don't interact at all, we have to input the same information five or six times - not too smart, I guess. The outputs are either Access databases or Excel spreadsheets (some of these reports are just Excel spreadsheets that must be filled with data); so, I was thinking about making an application that could aggregate all the input models and generate all the outputs I need, at once. Any suggestions?" "Here's the thing: it cannot be a web-based application (connectivity is a luxury at the rig), it has to run in a laptop (each employee should have it installed, stand-alone) and it must be able to import images from Excel worksheets. Crystal Reports uses spreadsheets as data sources, but it's not Open Source; I was thinking about using BIRT or JasperReports + POI, but that looks to me like inventing the wheel itself, so I decided to ask before digging into it."

Comments

Text Processing is what you want!

[peripatetic_bum]

Perl or Python would be best.

dayjobmode

[LiquidCoooled]

This is the sort of job visual basic (classic) can be good at.
Interaction with MS objects is simple in this environment and theres plenty of help in the IDE.
It lives on a machine quite nicely and is certainly quick enough since most operations will be at the speed of the apps, excel word or ado for the data.

All very "enterprisey"(-4 years) and works on everything from 95 to now with minimal effort just install office and your app and everything is there.

Write a new extractor for each report required and let it grab data as required and push it into the outputs.

Its macro-macros.

ot: does the .net experience appeal? theres gotta be some VB devs hanging around here - where have you lot started moving to since v6.0 'closed' its doors?

Re:dayjobmode

[CaymanIslandCarpedie]

Yes, .NET could do this well. ADO.NET can accept Excel and Access files as data sources. 2005 versions ship with a nice little reporting component (based on same technology as SQL Reporting Services). All that can be free with the Express verions (I'm pretty sure the Express versions ship with the reporting components, but not sure you'd have to verify that).

Though I never really use them (do more big projects where I prefer custom business objects for the most part) this sounds like a great little app for using .NET datasets. Use ADO.NET to populate the datasets (just a few lines of code) then build the relationships between your datasets and BAM you've basically got a little in-memory relational database consisting of data from your multiple misc data sources (be they Excel, Access, or whatever). You can then use this data to get what you need and display via databinding to grids, feeding the reporting component, or whatever makes the most sense to you.

generate from database

[Foofoobar]

Those reports are based on data pulled from a database. Do reporting on data in DB and not the output from singular queries. Save yourself some headache and time.

Keep in Mind...

[rmjohnso]

From your question, I'm not exactly sure what these reports are and what they are ultimately used for. However, if they are used for financial reporting purposes, this is an area that IT and financial auditors are looking at even more closely. Just make sure if you implement a pre-written package (OSS or not) or if you write something yourself, make it easy for an auditor to come in and get comfortable on the realiability of the application and they data flowing through it.

Pretty standard Access job

[cyranoVR]

If your outputs are all Access and Excel, you should normalize all the data to one Access database and generate the "master report" from there. You should use good ol' VBA (or .NET using the Office interop libraries), not Jasper reports or whatever.

A lot of people dismiss MS Access, but actually it has a lot of powerful functions for importing and exporting data of various formats. This is exactly the sort of job it was built for. You should really consider it.

ODBC Linked Tables/MS Query

[hey!]

Put it in a relational database and use a linked ODBC table in access or MS Query in excel to generate everything.

It's easy to do, just beware that the Jet engine sometimes makes mistakes on linked tables (maybe better a pass through query).

Use REALbasic Standard

[Helmholtz Coil]

REALbasic is really shaping up to fill the niche left by the demise of Visual Basic 6. I haven't played with this feature extensively yet but it does have an office automation feature to handle Excel, Word, etc. Might be worth a look.

SharePoint

[baldass_newbie]

If you have a 2003 server, just flip on Windows SharePoint Services.
Each project gets their own site.
You can store all your data on the site and then muck with it as you like.
Create your own lists/web parts/whatever.

Have fun.

Perl or VB would make great glue for this

[onlysolution]

My current job invovles alot of work similar to what you have to do. Both Visual Basic and Perl can do anything within office that Visual Basic for Applications can do. VB accomplishes the connection by specifying the respective office compenent's DLLs in the VBs references dialog box. Perl can talk to Office via Win32::OLE pm, see http://search.cpan.org/~jdb/libwin32-0.26/OLE/lib/ Win32/OLE.pm. Using the method produces code very similar VBA macros but with all the advantages of perl syntax and functionailty. In your situation I would use Perl for its excelent Office interaction and ease of text processing. There are nice free tools out there for packaging perl scripts as transparent binaries as well, so you don't have to worry about people not having Perl, etc on their laptops.
I'll check up this thread later if anyone wants to pick my brain about this stuff.

Re:Access?

[snuf23]

Access can import Excel documents as tables or just link to the Excel files. I have found that oddly enough the datatypes between the two do not always directly match. The biggest issue I've had is with data truncation moving Excel files to Access. Excel's text data type can contain more than 255 characters whereas Access cannot. You need to map text to the memo data type in Access.

Why not just Access, then?

[D4C5CE]

The outputs are either Access databases or Excel spreadsheets

Maybe I don't see an obvious obstacle at that time of night, but what exactly is it that Access could not do?
With built-in SQL, macros and BASIC, it can nicely import most things into extra tables, call external programs and if all else fails, interact with them by sending keystrokes - which should allow you to extract through the clipboard anything they wouldn't readily disclose otherwise.
And with a bit of SQL again, generating reports from there is something it can do really well (despite not being FOSS just yet ;-)).

Don't fart about........ Do it properly

[SomethingOrOther]

This prolly isn't what you want to hear but....
Sounds like a situation I was in. I needed to come up with a long term robust solution for my company for the type of situation you are describing

If it's worth doing then its worth doing properly. Dont fart about with hacks here and there. You need to get everything centralised on a SQL/Oracle etc server, getting rid of the shitty legacy Access databases etc written as a temp bodge by an intern 5years ago.... stuff that has now become mission critical. Get the suits to contract out the work if need be.

Before you complain this isn't what you are pitching for, lets talk monney (suits like the bottom line).
1) How much is it currently costing to type and process data six times (not taking into account the 'chinese whisper' effect and errors creaping in)
2) How can your auditors trace the current mess and find where the monney is going?
3) How future proof is your current setup? What will upgrading the current mess cost?
4) What accounting errors already exist in the current setup (no doubt written by non-profesionals)? 5) What backup/recovery policy do you have for your existing mess (none?) and how much will it cost when (not if) Freds hard drive dies?

Pitch the above points to your boss/suits and they will soon realise they need to do it properly and spend some monney. Of course, your situation may vary

FWIW, I completely moved our company away from the legacy ad-hoc crap and am processing everything with a centralisedd LAMP stack. But what else wold you expect to hear on slashdot :-)

Been there, done that, got the T-shirt and the blame :-)