Tag Archives: database

Save csv, excels to a database

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.

  1. 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.csv to db - load sample
  2. Pick the sheet from the Choose sheet dropdown you want to load data from.csv to db - sheet
  3. Click the Load column names from sample file button. This will load the column names as cell id’s (eg. A1, B1 etc.).csv to db - load columns
  4. 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.csv to db - load columns with header row
  5. 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.Csv to db - field name
  6. 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.csv to db - table name
  7. 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})csv to db - sql
  8. 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. csv to db - success

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:

Contact us

Contact us if you have any questions, suggestions about the functionality described in this article.