• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

Data import and chart automation

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!
  • 3
5 Solutions
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

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.
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.
APB ReportsCommented:
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.
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now