1

My goal

  • Automated aggregation of data so that I can use a tool (Microsoft Power BI or equivalent) to automatically update dashboards and or reports with the new data.

The situation

I work with an LMS that only allows two options for pulling data from it's database:

  • Manually downloading reports

or

  • scheduling these reports to be emailed.

We want to be able to look at a specific set of data but really don't have the time to manually clean up and aggregate all of this data every time we need it.

I want to understand if I can either email the csv files to a database and have it auto-import (I don't believe this is possible) or have a database auto-import new files in a folder.

What I have

  • Reports built with the data we need
    • Just not in the format we want

What I don't have

  • Backend access to the LMS's database

It is likely that this will be a SQL Server that would be used, it it's possible.

  • Find a dedicated VM or workstation, etc. you can dedicate to the automation of the manual download and use AutoIT to build a script to run through the motion from the web browser, etc. when someone does that manually by emulating the correlated mouse clicks and movements, and the key strokes on the keyboard. You then standardize and automate the copying of these files and then import with BCP or TSQL BULK INSERT statements perhaps. With a little trial and error, research, etc. you could figure it out... just a quick idea of an approach I would consider if absolutely necessary. – Pimp Juice IT May 12 '17 at 22:28
  • What LMS ? I do the same but in both directions - CSV files from Crystal Reports into a local mysql database, generate CSV files and import those to the LMS. Or use the LMSs API to send/receive some data to generate custom reports, etc. I also don't use Excel to do it - I use PHP as a quick-n-dirty text processing/db querying language, very easy to do. May be a good excuse to learn PHP, Python, etc... – ivanivan May 13 '17 at 1:04
  • @ivanivan The LMS is IBM Kenexa (not Brassring). I'd love to be able to use the API, however it's rather limited and only allows access to member information and organizations. If I could use the API, I'd be overjoyed :) – Odinson May 14 '17 at 5:26
  • @Spittin'IT Interesting option and I for sure don't want to say "no" without looking more into it. While that's not quite what I'm looking for, I agree it's an option. Thanks! :) – Odinson May 14 '17 at 5:28
  • What type of email server are you working with and are you the domain email administrator or are you able to talk with him about option on having the email server use an external script or built in functionality, etc. and put some rules around if email if from x and sent to x and has an attachment of .csv, then copy this .csv to this UNC path, and from there you can have something check periodically and if a file is found, bulk import, etc. Maybe worth a talk with your email admin or some reading up on your SMTP server functionality for such options. – Pimp Juice IT May 14 '17 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.