Reading attachment data and entering into a table

[Jason Stephens]

Is there any way to receive an email attachment (csv, xls), and write the data into a system table? I have a few places where I could use this functionality, but I can't seem to figure out how to do it, or if it's even possible. Has anyone ever done this?

Thanks for the help.

Jason

[tony_fugere · Accepted solution]

There's a few out there have done this. I am one of them.

Scripting an Inbound Email Action to add attachments to a Data Source
Data imports from email attachments

Here is a Script Include I've had success with after learning from the posts above.

Usage from Inbound Action:

var type = {};
type.schedule = 'Dell Shipping Report'; //Display name for scheduled import
type.table = 'u_import_dell_shipping'; //Import table name

u_importViaEmail(type, 'Dell Shipping Report');

Script:

function u_importViaEmail(type, logSource) {
  if(type.schedule != '' && type.table != '') {
    gs.log('1. Setting up data source', logSource);
    current.name = type.schedule + ' ' + gs.nowDateTime(); //append date time to name of data source for audit
    current.import_set_table_name = type.table;
    current.import_set_table_label = "";
    current.type= "File";
    current.format = "Excel";
    current.file_retrieval_method = "Attachment";

    var myNewDataSource = current.insert();

    gs.log('2. Data source inserted with sys_id - ' + myNewDataSource, logSource);

    //point the scheduled import record to the new data source
    var gr = new GlideRecord ('scheduled_import_set');

    gr.addQuery('name', type.schedule);
    gr.query();

    if (gr.next()) {
      gs.log('3. Found Scheduled Import definition', logSource);
      gr.data_source = myNewDataSource;
      gr.update();
      gs.log('4. Scheduled Import definition updated to point to data source just added', logSource);

      Packages.com.snc.automation.TriggerSynchronizer.executeNow(gr);

    } else {
      // add error conditions to email somebody that this has occurred
      gs.log('5. ERROR - Unable to locate scheduled import definition. Please contact your system administrator', logSource);
    }

    gs.log('6. Inbound email processing complete', logSource);
  } else {
    gs.log('7. Inbound email processing skipped', logSource);
  }
}

[adiddigi]

Thanks tony! This is a good script to do it right. I used to do it by reading the attachment, getting the bytes of data and inserting it into a import set table(which is mapped onto a import set, sync). I will try this.

[Jason Stephens]

So this is assuming that the schedule and table that you are referencing in the inbound action are already present right? I'm just getting into some of this data import stuff and I'm trying not to ask too many crazy questions. I just noticed when I tried to use this that I saw an error of:

GlideRecord.setTableName - empty table name (; line 1)

I'm assuming that the background pieces need to be in place so that the code can reference them.

Jason

[tony_fugere]

Yes, you need to manually import the file once via the "Load Data" module and/or create the data source before using this.