Get the Name of the attached XLS file for an Import DataSet & Transform??

tobrien · ‎02-28-2017

Hi,

We're on HELSINKI.

I have crafted (read: stolen!) a means to get an inbound email with an attachment to have that "attached" XLS pushed into a DataSet and then Transformed into rows of a new table.

It all works swell!

The last wrinkle is that we are receiving these emails from a variety of 'vendors' and *their* names are in the XLS Filename -- i.e. JoeBloeWaterPipes_daily_orders.xls

What I would really like to be able to do is parse the filename to get the Vendor's Name portion and push that into a column of the dataset import row (currently I get just the rows within the file to populate the dataset import table.

I seems to me that this needs to happen right at the IMPORT (as the TRANSFORM can only operate on what was IMPORTed.)

I don't see any SCRIPTING functionality EXCEPT to use a BizRule (Before Insert) on the Import Set table to somehow parse the attachment's filename and place it into a column of the table so that the TRANSFORM just does it's thing.

All suggestions are most welcome.

BTW, I have thought of adjusting the file spec to force them to add their names into each row of their files... and I will use that as a last resort.

jarodm · ‎02-28-2017

Anthony,

You should be able to dot-walk on this path up to your attachment. From your import set table, a field named 'Set (sys_import_set)' will allow you to get to the Data source (data_source).

Transform Map Source Script (theory, not validated)

//Transform map source script

var dsid = source.sys_import_set.data_source.sys_id

var customerName = 'undefined';


var gr = new GlideRecord('sys_attachment');

gr.addQuery('table_name','sys_data_source');

gr.addQuery('table_sys_id',dsid);

gr.orderByDesc('sys_created_on');

gr.query();

if (gr.next()){

         //This should be the most recent attachment to the data source

         var fileName = gr.file_name;

         //Add string logic here to strip

         //out the customer name

         customerName = strippedOutCustomerName;

}


return customerName;

Please mark this as helpful/correct (if it was) and make a follow-up post to let us know if it was successful.

JarodM

View solution in original post

Stuart Diener · ‎02-04-2021

Hi Jarod,

Is this source script better added at the main section of the transform map (when you click the "run script" checkbox, or should it be added as an onStart script on the transform scripts tab to the right of the field maps tab?

Cheers,

Stu