Go Premium for a chance to win a PS4. Enter to Win


Data import and chart automation

Posted on 2016-07-31
Medium Priority
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!
Question by:PriceD
  • 3
LVL 101

Accepted Solution

mlmcc earned 400 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

Crystal Reports – Part II - Exportng

LVL 18

Assisted Solution

vasto earned 1200 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:
This PDF is comparing the schedulers :

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

Assisted Solution

vasto earned 1200 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.

Assisted Solution

by:APB Reports
APB Reports earned 400 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.
LVL 18

Assisted Solution

vasto earned 1200 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.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

885 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