Solved

Data import and chart automation

Posted on 2016-07-31
5
61 Views
Last Modified: 2016-08-08
I need to automate some reports and I'm looking for advice on some ways I can do that.  I receive a daily csv file that I then import into an excel spreadsheet which I then generate graph reports from.   I then export the graphs as PDF and email them.  I would like to automate the entire process including saving the csv attachment that comes in on email to a location on the network.

I have some experience using VBA to automate and perform functions.  I'm not a fan of the reporting tools built into access which is why I'm using Excel but I'm open to using crystal reports if there is a reliable solution using crystal.  

Thanks for your help!
0
Comment
Question by:PriceD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 100 total points
ID: 41736421
Crystal could do what you want.  Here is a method of running the reports using a dataset as the report source.

Feeding Crystal Reports from your application
http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

Crystal Reports – Part II - Exportng
http://www.emoreau.com/Entries/Articles/2006/10/Crystal-Reports--Part-II.aspx

mlmcc
0
 
LVL 18

Assisted Solution

by:vasto
vasto earned 300 total points
ID: 41736650
You can use Crystal reports to present the data and use CSV or Excel as a datasource for your report. Once you have the report in Crystal reports you can use 3rd party software to automate it. For example you can schedule the report to run at specific time every day, export the report to a PDF and send it to some emails. You can find a list of 3rd party schedulers for Crystal reports here:
http://kenhamady.com/cru/comparisons/desktop-scheduling-engines
This PDF is comparing the schedulers :
http://kenhamady.com/desktopschedulers.pdf

Generally pricing is pretty reasonable. There are even some tools , which are free or provide free version if you don't need technical support.
0
 
LVL 18

Assisted Solution

by:vasto
vasto earned 300 total points
ID: 41737862
PriceD, It is not necessary to import the data to a SQL database, CSV file could be used directly as a datasource through an ODBC connection and Microsoft Text driver or using Access/Excel connection. It doesn't matter who is going to read the data from the CSV file, Crystal report or the importing procedure, they will both use the same drivers. Adding additional step to import the data probably looks cool and is a good selling point for an outdated software , but in reality will create just problems and will slow the reporting process.

The process with direct read from Crystal will look like this:
 1 read data from Crystal report  
 2 use data inside the report

the process with importing data will look like this:
 1   read data from importing procedure
 2   create a lock (traffic light) so no other report can run when an instance of report is started
 3   save data
 4   read data imported into the database from Crystal report  
 5   use data inside the report
 6   clear data
 7   delete the created lock and allow other processes to run

Steps 2 and 7 are required to handle scenarios when 2 processes are running the same report. If they are missing, one of the processes will overwrite the data for the other one in step 3 and the results will be wrong. Step 6 is against any good practices. Reporting application should never change saved data, but you cannot avoid that if you use the process to also generate data. Obviously the importing process above is more complex, dangerous and slower than the direct process. I have no idea why importing will be considered as an option if you are able to use the data directly. In case you have plenty of subreports , which are using the same data generating temp table might have advantages, but this should be handled with a stored procedure inside a transaction and managed by the database, not by the reporting tool. Also the software should support setting external datasource to the report, which is not the case with most of them. Keep in mind that most of the 3rd party software for Crystal reports is not written by professional developers. Check few products before to make a decision and don’t trust sellers and beginners.
0
 

Assisted Solution

by:APB Reports
APB Reports earned 100 total points
ID: 41744741
Hi PriceD,

Your request could be solved with a Crystal Reports tool called Visual Cut from Millet software. This tool can automatically download new emails and detach the csv attachments to a folder of your choice. The tool could then run a crystal report against the csv file and send the exported file to one or more emails. This process could be handled with one batch file with a couple of commands which would make it simple to schedule with Windows tasks.

I do not work for Millet software. I only recommend this tool as I do very similar tasks for my clients with this tool. It has some very powerful functionality when working with Crystal Reports.

Hope this helps you.
0
 
LVL 18

Assisted Solution

by:vasto
vasto earned 300 total points
ID: 41744900
There are plenty of tools, which support similar features and more. Even a cheap software like  EasyView has its own scheduler service and is not using the buggy Windows Task manager. I do not recommend EasyView because it is not supported now, but the fact is that even this $49 tool is using its own service and is not cutting corners.   Batch files are security risk. I am sorry if somebody missed the last 20 years and still recommends them as an option. Again, check few products before to make a decision and don’t trust sellers and beginners.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question