Solved

Data import and chart automation

Posted on 2016-07-31
5
53 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

740 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