Solved

Data import and chart automation

Posted on 2016-07-31
5
45 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
  • 3
5 Comments
 
LVL 100

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now