Reposted from https://www.sqlmessenger.com/faqview.html?id=571
Q: Automatically import data from CSV files attached in emails
I receive CSV files from factories every day containing information such as equipment operating hours. I want to automatically import these CSV files into a general 'maintenance' file for easy querying whenever needed. Is there a simple way to achieve this?
A: Yes. SQLMessenger can be configured to achieve the functionalities you need.
We have configured two tasks in SQLMessenger to help you achieve this functionality. First of all, please ensure that your SQLMessenger version is 2.2.0001 or higher.
We assume that the CSV files you receive are formatted as follows:
To achieve the functionality you require, we have configured two tasks.
The first task, "ImportEquipmentInformation", is used to import CSV files. If the email contains an attachment named "equipment-data-plant*.csv" (* represents any character), the task will import the data from the attachment into the system and also copy the attachment file to the directory "d:\equipment-data" for centralized storage.
The second task, "QueryEquipmentInfo", is used to query the imported data. You can flexibly select the data you want to query and export it into a spreadsheet.
Please follow the steps below to test the tasks we have configured:
Step1: Click here to download the task files we have configured, and import "ImportEquipmentInformation.tsk" and "QueryEquipmentInfo.tsk" into your SQLMessenger system. Click here to view the method for importing task configurations.
Note: After importing, you need to enable the tasks in the task manager.
Step2: Configure the email account to start task "ImportEquipmentInformation" when an email is received. In the email account manager, enable the "Allow SQLMessenger to receive emails" option and set the "ImportEquipmentInformation" task to trigger when an email is received.
Step3: Create a folder named "equipment-data" on your D drive to store the received CSV files.
Step4: Use another email account to send an email with the CSV file from the previously downloaded zip file as an attachment to the email account configured in SQLMessenger.
After SQLMessenger receives the email, it will trigger the "ImportEquipmentInformation" task to process the email attachment. Once the task is complete, you can view the imported file information in the task log.
Other Issues
How to modify task parameters?
If you need to modify the task parameters, such as saving the CSV files to the "c:\data", you can change the "TargetDir" variable in the "ImportEquipmentInformation" task:
Can I process emails from only specific senders?
Yes. You can modify the "ManagerEmailAddr" variable in the task so that the task will only process emails from the email addresses you set. If you have multiple email addresses, separate them with commas. Once configured, only emails from the list of addresses will be processed.
Emails from other addresses containing equipment data will not be processed, and the task will reply to the sender informing them that their email was not processed.
What happens if a plant sends duplicate data?
We have implemented a restriction in the task. For the same equipment ID on the same day, only one record is allowed. If duplicate data is sent, the system will keep the last sent data, and the previous data will be deleted. If you need to modify this condition, you can change the SQL of the "SaveFile" command in "TaskCommands":
If you are unsure how to modify the SQL statement, you can let us know your requirements, and we can modify the task configuration and send you a new file.
What if my CSV file format is different from the example task's format?
You can modify the task configuration. If you're unsure how to modify the task, please send your detailed requirements to support@sqlmessenger.com, and we will modify the task for you and send the updated configuration file.
Can SQLMessenger import spreadsheet files '.xlsx' instead of CSV files?
Yes. For an example of importing spreadsheets, please refer to another article: Automating Excel report processing from email attachments.
Top comments (0)