Catch up on stories from the past week (and beyond) at the Slashdot story archive

 


Forgot your password?
Close
typodupeerror
×

Generating Reports from Access and Excel Files? 64

Posted by Cliff from the process-streamlining dept.
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."
This discussion has been archived. No new comments can be posted.

Generating Reports from Access and Excel Files? More

Generating Reports from Access and Excel Files?

Comments Filter:
  • Perl or Python would be best.
    • On Windows, accessing Excel and Access files?

      Ok I'm typing this using Firefox, Fedora with FreeRIDE running on another desktop and I think VBA is probably the tool for the job.

       
      • I would be inclined to agree with you except for the high mortality rate of programmers using VBA.
        Gah!
      • Yes, I know... but the laptops use standard images, burned by a standard method.. and so it goes.

        • 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 th

      • I'm a Linux zealot through and through, and I think you're right also. Maybe a VB.Net/C# app if you want to program in a "real language". Use the right tool for the job. Using .Net, you can open Access files just a like a regular database, and I think (am almost certain) the same goes for Excel files. I think the hardest part about writing this app would be to get it to make any sense of the data. If all these excel/access files are made by individuals, I find it hard to think that there's any standard
      • I've been doing scripts at work lately in Perl to pull data from Access and Excel files. There's ActivePerl for Windows, DBI to get at the Access data, and other modules for Excel. It all works quite well, and the text processing of Perl is handy for those reports (more so than, say, VBA). Not sure how you'd get at the images though. (Note: I am not a Perl fanboy in general, but if it works...)

  • dayjobmode (Score:5, Insightful)

    by LiquidCoooled ( 634315 ) on Friday June 30, 2006 @08:58PM (#15640786) Homepage Journal
    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 (Score:5, Informative)

      by CaymanIslandCarpedie ( 868408 ) on Friday June 30, 2006 @09:21PM (#15640864) Journal
      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.
      • The above said, I really wonder if maybe a bit more of an "enterprise" solution might be nice (I alway like to over complicate things ;-). Would be you need to be able to generate this reports historically? If so the above solution will really only work with what data is currently in the Access, Excel, etc files (obviously). It may be worth looking into something that retains a full history of this so they can pull reports of any past jobs as well.

        For that you could do much the same as above, but jus
      • SQL Server Express ships with Reporting Services.
        No other Express edition ships with any sort of reporting.

        Visual Studio Professional ships with Crystal Reports; it might contain reporting services as well.

  • generate from database (Score:4, Insightful)

    by Foofoobar ( 318279 ) on Friday June 30, 2006 @09:00PM (#15640793)
    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.
    • Wholeheartedly concur.

      While not exactly ANSI-compliant, and certainly choking on a lot of more sophisticated queries, the MS Jet engine does pack significant heat, especially if it can be used in a stand-alone mode.

      Access's report object is also a good 80% solution for laying stuff out on paper.

      Before upping the complexity of your solution, I would make very sure that you know exactly what the requirements are, and what is not already attainable with tools on hand.

  • Keep in Mind... (Score:4, Insightful)

    by rmjohnso ( 891555 ) on Friday June 30, 2006 @09:00PM (#15640797)
    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 (Score:3, Interesting)

    by cyranoVR ( 518628 ) * <cyranoVR&gmail,com> on Friday June 30, 2006 @09:02PM (#15640800) Homepage Journal
    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.
  • How much time do you got? ;)

    MS .net VSTO.

    You can connet to pretty much any datasource with .net.
    The VSTO add one allows you to create managed code(not VBA) in dot net and fully integrate with excel.

    I have done what you need to do many, many times. So I feel your pain.

    I am availadble for consulting(not contracting) for a reasonable fee.

  • I don't have Access on this computer, so I can't test it, but it seems like Access should be able to import images from Excel files somehow. If not, I'm sure you can whip up a separate app to run the import and get the images in somehow.

    After that, why not do all the reporting with Access? Attach a few VBScripts to some buttons and zoom.

    • Re:Access? (Score:3, Informative)

      by snuf23 ( 182335 )
      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.
      • At my office, we get around this by using some homegrown VB apps to do the import instead of Access's built-in import functionality - which is admittedly crappy.

  • ODBC Linked Tables/MS Query (Score:3, Interesting)

    by hey! ( 33014 ) on Friday June 30, 2006 @09:10PM (#15640826) Homepage Journal
    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 (Score:4, Interesting)

    by Helmholtz Coil ( 581131 ) on Friday June 30, 2006 @09:22PM (#15640868) Journal
    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 [realsoftware.com] feature to handle Excel, Word, etc. Might be worth a look.
    • What demise? VB6 lives on in VBA aka "Excel Basic" and "Access Basic" (funny how they've come full circle with those names, huh?).

    • Re:Use REALbasic Standard (Score:1, Interesting)

      by Anonymous Coward
      let's see:

      (use search.cpan.org to find this items)

      DBD::Excel - treats excel data as db data
      DBD::ODBC - odbc access to databases that support odbc (like access)

      Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file.

      looks like a solution to me, that is, if you use perl...

  • SharePoint (Score:2, Interesting)

    by baldass_newbie ( 136609 )
    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.
    • If you have a 2003 server, just flip on Windows SharePoint Services.

      What???

      Either I totally misunderstood the question, or your answer has nothing to do with it.

      Additionally, the FP clearly states "it cannot be a web-based application".


      So - Chance to redeem yourself here - Whatchoo talkin' bout, Willis?
  • 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 [cpan.org]. Using the method produces code very similar VBA macros but with all the advantages of perl syntax and function
    • I would recommend against perl. Linking to the Office DLLs isn't really all that good an idea, as you have to have office installed on the machine doing the processing. This may require extra licenses. Instead I would use C# or VB.Net with ADO.Net to connect to the Access/Excel files as datasources. This way you can use SQL queries on them. It's much easier to work in this manner, then trying to communicate with the app via the Office DLLs.
  • I find it abhorrent to copy/paste something more then once. Retyping something is something I just dont do. Both Excel and Access can be (*cough*) accessed programaticly, with just about any Windows tools worth mentioning (including each other). You've got yourself a stupid system, but it should be possible to work within that stupid system better then copy/paste. Activestate perl can do this, but if your using (complex) excell and Access, assumably you have some local experience with VBA or, at worst, som
  • Just use vlookup
  • DTS that is included with MS-SQL Server 2000 is a good option. I have done a lot of this type of thing, and it is quick and easy with an ETL (Extraction Translation tool) like DTS.

    I wish I could find and Open Source replacement for this tool.

    This book has been very helpful
    http://www.microsoft.com/MSPress/books/6525.asp [microsoft.com]
    • Call me a nitpicking bastard, but ETL stands for Extract, Transform, Load.
      Definition here. [webopedia.com]

      Other than that, I wholeheartedly agree. DTS would do this quite handily.
      Probably not the cheapest option if there's no in-house MS-SQL install, though.
  • Your solution to your problem is the domain of a System Analyst, someone who figured out what output was required and figured out a path to get there from the data available. You are not looking for a tool, you are looking for a method.

    The mainframe world was lousy with system analysts, who told programmers what to do. Do they still exist?
    • However, most of my colleagues would not be able to do this. They have lived in the mainframe world (think COBOL, DB2, OS/390, TSO, REXX, JCL etc) for years. They'd probably be able to make excellent suggestions along the lines of processing the data and reporting with SAS or COBOL.. but that's the mainframe.

      So, to answer your question: yes, we do exist. Most of 'us' however, are barely able to use Excel.. let along something as 'complicated' as pulling data from several sources and creating a report (even
  • Plenty of languages can use Excel to manipulate spreadsheets (i.e. PERL, VB, .NET, etc). This is okay for an application that runs off the user's computer.

    You can also use Excel as a data source using the Jet OLEDB driver. I've had some problems w/ it (i.e. if you have a columns that contains entries like "bob", "jane", and "23" -- it will choke on the 23 because it is expecting text isntead of a number -- perhaps there is a workaround for that.

    Access is easy to work with as a data source....
  • Business Objects enterprise.

    Even better, a pile of perl scripts.

  • Don't fart about........ Do it properly (Score:3, Informative)

    by SomethingOrOther ( 521702 ) on Saturday July 01, 2006 @07:19AM (#15642118) Homepage

    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 :-)

    • Woah, woah, woah! Any shop using an ad-hoc collection of Access DBs and Excel spreadsheets is probably a small business that can't afford Oracle. They're comfortable with their current inefficient system, and the guy proposing this is planning on doing it with no funding and probably little to no allocated work time. He needs a free solution because he has no budget.

      Proposing a multi-thousand dollar system is going to go over like a lead balloon in a workplace like this.
      • > Woah, woah, woah! Any shop using an ad-hoc collection of Access DBs and Excel spreadsheets is probably a small business that can't afford Oracle.

        Not necessarily - since oracle for a small database ( 4gbytes of data I think) is free now anyway. But *oracle* doesn't matter - use of any database, even mysql, would be a drastic improvement.

        What's probably more important is:
        1. there's no network for a centralized solution, they use client software instead
        2. there may be no funding to do this right
        3. man
    • What you forgot to mention is that you're in for a world of hurt when users start saying things like "but in the old system I used to do it like THIS and now it won't?? What do you mean, I can't open the tables anymore? But I'm used to scrolling through all the data, I don't like using forms!". I don't envy anyone jobs like this.
  • I work in a company that uses Excel to track many aspects of the business. We face a similar problem trying to generate reports from multiple Excel documents. Recently, I've started moving each business owner to an RDBMS solution. Front ending to a web site, users can login to a simple interface to interact with the data. Using web servers, users can use Excel (remember, Excel can use XML streams as a data source) to manipulate data for any custom reports.

    There are many choices for a "free database". In add
  • If I was doing something like this I'd be looking to build a good domain model of what I was doing and then a ORM tool to map that to the data. I'd also be looking for a dynamically typed language to write in, one that is supported by .Net for its native access to the data artifacts that you have.

  • This software has some pretty amazing capabilites and can connect to spreadsheets, word files, multiple databases, HTML, XML, CSV, well you get the picture, all in one contiguous stream of data for pubilishing. I would recommend checking it out for your needs. www.patternstream.com
  • I may have missed something, but if you are happy using Excel/Access why do you then complain about Crystal Reports not being OS?
  • http://monarch.datawatch.com/monarch-pro.asp [datawatch.com]
    Lets you configure models to pull data from excel, access, text files of all sorts.
    Scriptable with COM as well!
    -Rob

Slashdot Top Deals

Remember to say hello to your bank teller.

Close