Reading attachment data and entering into a table

Jason Stephens · ‎09-10-2012

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 · ‎09-20-2012

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 != '' &amp;&amp; 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);
  }
}

View solution in original post

David OBrien · ‎03-26-2013

Tony,
I've been trying to setup an import using these scripts and my import is failing, though it appear to run through the whole script. It removes the "source" from the scheduled import each time it runs, so a second attempt gets an "Unresolved data source for ScheduledImportJob" error. Any thoughts on what I might have setup wrong?

Thanks.
Dave

Edit 3/26/2013 - turns out I had the wrong table for my inbound action...

Vidhu · ‎03-06-2013

Hi Tony,

Need some suggestions in reading an csv file attached to an Request Item.
I need to check the number of columns in the attached csv file. and then can continue with the transform map.
Is there any ways to get the number of columns in the attached csv/ excel.

Appreciate your help.

Thanks,
Vidhu

Jamie_douglas · ‎01-02-2014

Hi,

I was reading this post and I know its marked as "answered" but wondered if Vidhu or anyone else managed to figure out a way to read the number of columns in the csv/excel file prior to transforming?


Vidhu

I have figured out a way to read the column headings on a csv file prior to transformation and validate these headings. I cannot for the life of me figure out how to read the column headings of an xls file though. Any help would be great.

The code I used to read a csv file header is: (try in a background script remembering to replace the sys_id with a sys_id of your own csv attachment)



var att = '4a072a906f251100e60f50be5d3ee419'; // sys_id of csv attachment
var attIs = Packages.com.glide.ui.SysAttachmentInputStream(att.toString());
var inDIS = new Packages.java.io.DataInputStream(attIs);
var br = new Packages.java.io.BufferedReader(new Packages.java.io.InputStreamReader(inDIS));
var line = br.readLine();
var lineArray = line.split(",");

for (var i = 0; i < lineArray.length; i++) {
gs.print(i+1 + " - " + lineArray<i>);

}

The results from a background script i get are:



*** Script: 1 - Department
*** Script: 2 - Location name
*** Script: 3 - External system ID
*** Script: 4 - CI Name
*** Script: 5 - CI model
*** Script: 6 - Serial number
*** Script: 7 - IP address
*** Script: 8 - Short description
*** Script: 9 - Path
*** Script: 10 - Manufacturer
*** Script: 11 - Status

Any help with reading the column headings from an excel file would be great!

bookman1502 · ‎10-21-2015

Hi,

I'm trying to read a csv file attached to an email in an inbound email action and process the data before putting it into a data source. You said to "replace the sys_id with a sys_id of your own csv attachment." How do I find out what the sys_id of the csv attachment would be?

sumitjumale · ‎09-19-2017

Hi Jamie,

I am getting below error when I tried.

I replaced line number two with " new GlideSysAttachmentInputStream(theSysID.toString());". But Igot error in line number 3 as below

Please suggest ASAP.

Regards,

Sumit