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.
Odinson
Posted 2017-05-12T22:19:20.640
Reputation: 11
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 – 2017-05-12T22:28:16.450
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 – 2017-05-13T01:04:15.237
@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 – 2017-05-14T05:26:28.197
@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 – 2017-05-14T05:28:57.007
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 – 2017-05-14T05:47:03.783
I assume manually downloading reports is not via FTP and via an HTTP interface or something, correct? If you could download via FTP or SFTP, etc. you could automate the exchange portion for sure. – Pimp Juice IT – 2017-05-14T05:47:54.377
@Spittin'IT You're correct, when I manually download the csv, it's through the web interface as there is no other way offered aside from getting it emailed to me.
As for your idea of talking to my email administrator, that sounds to be a viable option. Thank you! I'll see what I can find out in the next few days. – Odinson – 2017-05-15T02:32:40.543
Another idea if the HTTP interface uses API calls and if so then perhaps you can pass argument in with the URL calls with parameters for login, what to download, etc. and then you could use PowerShell Invoke-WebRequest or something similar to automate the download via the URL call. I'm assuming the data is sensitive and this is an HTTPS interface using SSL encryption here and that you have to enter credentials to log into the web interface. So if the web interface uses backend logic that you can pass parameters to tell it what to do, that could work as well. Let me know what you determine tho. – Pimp Juice IT – 2017-05-15T04:47:04.840
@Spittin'IT Unfortunately the API only accepts requests for User and Organization information... it's a very very limited API. Otherwise, I'd love to try this out! I appreciate your ideas, I wish I could act on them. I'm currently still waiting to hear back from our email/IT admin. – Odinson – 2017-05-17T15:09:28.303
Due to the limitations of the LMS and internal facilities, I ended up having to manually schedule the report on a weekly basis. Then I found a powershell script that allows me to combine (append) files together. Once I get them all combined, I simply use the spreadsheet as my source and use Power BI to get the values that I need. @McDonald's Thank you for your help. I wish I could implement something from your ideas as it would really make things easier. – Odinson – 2017-06-01T19:33:11.183