If you have a need to save csv or excels to a database, you can automate it with ease with Mail Attachment Downloader PRO Server.
If you already receive these csvs or excels on email, then great we will show you how to save these to a database.
If not, you can email these csvs to an email address of your choice and have the program extract the attachment and automatically save it giving great power to you on-the-go.
Save csv, excels to a database
First ensure that you have a database connection configured as instructed here.
The next step is to create a Global rule filter so we can first identify the email that contains the csv, excel. You can do so by adding a rule filter as shown earlier in this blog post. You can also specify a file type filter to choose .csv or .xls or .xlsx emails with filtering rules.
Once those are both done, saving the csv or excels to a database requires you to add an action.
- Click Add action -> choose Extract csv and save to DB and then click the Load sample file button to load a sample Excel or Csv file.
- Pick the sheet from the Choose sheet dropdown you want to load data from.
- Click the Load column names from sample file button. This will load the column names as cell id’s (eg. A1, B1 etc.).
- If your Excel/Csv starts from a header row (with header titles for each column), check the Starts from a header row and then click the Load column names from sample file again. This will auto-import the column names for easier manageability.
- For each column you want to import, specify a field name within {} as shown below. By default all fields are imported as VARCHAR’s. If you want to change the type, you can also choose Extraction Wizard for this field to change the field type or manipulate data from the Csv if needed.
- Once all fields have been given a name within {}, click the Insert into database tab. Pick the DB source you have already configured and the table name you want the data inserted into.
- Now specify the SQL including fields that you want inserted as shown.INSERT INTO {TABLE_NAME} (‘Sales description’, ‘Sales price’, ‘Sales quantity’) VALUES ({DESCRIPTION}, {PRICE}, {QUANTITY})
- Now click the Test SQL using Sample File button. If everything is correct, then you should see something like the below. You can then look at the database to ensure that the rows from the Excel or CSV were inserted into the DB.
That’s it.
Now any emails matching the criteria would end up being added into the DB.
Further reading
Some interesting how-to links and posts for further exploration:
- Save emails to a database
- Save to multiple folders from multiple accounts using multiple email rules.
- Send emails automatically after downloading files or attachments or body
- Unzip files, Decrypt pdf’s and run script’s
- Extract data and download emails for invoice processing
- Convert email attachments to different formats
- How email rules work in-depth
Contact us
Contact us if you have any questions, suggestions about the functionality described in this article.