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

[tobrien]

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 · Accepted solution]

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

[tobrien]

Excellent, Jarod !!

I'll give it try first thing in the AM -- and get back to you!

[tobrien]

Yep, that works very well... thanks a lot !

[jarodm]

Great! Glad to hear it.