Use an email with an attachment to create a Data Source, Load Data, and Run Transform

[jason_petty]

---

The method below was originally published on Oct 25, 2012, is an outdated process 
Please see the following post for help! >   Loading data from an email attachment 

---

I had a number of people respond to this post with the same problem and together with my colleague I re-wrote the instructions to make it work.

I had a customer that wanted to have IBM Maximo product send an email to ServiceNow and have a CSV/XLS file attached to the email automatically create a new Data Source, load the data, then transform it all automatically just from receiving the email. Here is what I did to get that to work:

1. Create a Data Source Manually first so you can set up the Import Set Table and the Transform Map that you will use later. This is necessary since you need a Transform Map defined before you send the email inbound to ServiceNow. This Data Source can be deleted after creating the Import Set Table and Transform Map.
2. Get a sample CSV/XLS file and attach it to the Data Source and load the data at least once.
3. Define your Transform Map and set it up so the data is getting transformed into the right table in the format that you need.
4. Create a user that you will use for basic authentication with no roles and has a password.
5. Create a REST Message that points to your own instance for the REST endpoint: https://instancename/sys_import.do as the url named "Inbound Import Set Poster"
6. Check the box for "Use basic auth" and set the username and password from step #4
7. Edit the "get" function and add the following function parameters:

8. Create an Inbound Email Action with the following settings:

HERE IS THE CODE TO COPY:

if (current.getTableName() == "sys_data_source") {

       current.name = email.subject.substring(3);
       current.import_set_table_label = email.body.import_set_table_label;
       current.import_set_table_name = email.body.import_set_table_name;
       current.file_retrieval_method = email.body.file_retrieval_method;
       current.format = email.body.format;

       current.update();

var r = new RESTMessage('Inbound Import Set Poster', 'get');
r.setStringParameter('tableLabel', encodeURIComponent(current.name));
r.setStringParameter('customDataSource', current.sys_id);
               r.setStringParameter('tableName', current.import_set_table_name);
r.execute();
}

9. The Condition can be anything you want but the Target table needs to be sys_data_source.
10. Enable the inbound email settings in your instance to receive email and process them. See WIKI http://wiki.servicenow.com/index.php?title=Configuring_Email for this.
11. Now send an email like this to the email instancename@unix.service-now.com (The "unix" part of this avoids any email formatting issues with email clients):

1. To: instancename@unix.service-now.com
2. Subject: DS:Test Data Source 1
3. Body:

              import_set_table_label:Test Import Set Table
              import_set_table_name:u_test_import_set_table
              file_retrieval_method:Attachment
              format:CSV

NOTE: CSV would be Excel if you are using an XLS file

12. Wait until the email is processed and it should have created your Data Source with whatever is after the "DS:" in the subject
13. Make sure the import_set_table_name is set to whatever you named your import set table when you created your Data Source in step 1 (This is very important since the Transform Map is linked to this table)
14. The format is set to either Excel or CSV.
15. You need to attach the file you want to attach to the Data Source.

>

---

The method above was originally published on Oct 25, 2012, is an outdated process 
Please see the following post for help! >   Loading data from an email attachment 

---