Can I read a file attachment header prior to loading in a data source?

[ericroberts]

I have a file generated by an external source (an .XLS file)...that I have to load in every few weeks....sometimes longer.  Every once in a while the file comes to me with an extra column header...or a column heading has a different name from when last exported.  I have no control over that part ... all I can do it go back to that dept. and ask them to fix the header row...but usually not before running the import and finding out something was hosed up.

Is it possible to get at that header line prior to loading in a data source.  I'd like to have a function where I pass in the header row values and check them against my good known list of valid column names I'm expecting...prior to actually doing the load.

Possible?

Thanks,

ER

[Ankur Bawiskar · Accepted solution]

Hi Eric,

you can parse the excel and get the first row which is the column headers and compare it with your standard header columns;

if there is mismatch then don't run the import

https://developer.servicenow.com/app.do#!/api_doc?v=kingston&id=GEPS-GlideExcelParser

Sample script below; it would take attachment record sys_id and print the column headers

var parser = new sn_impex.GlideExcelParser(); 

var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream('dc0a757f4f273340fc11fa218110c752');

parser.parse(attachmentStream); 

//retrieve the column headers
var headers = parser.getColumnHeaders();  
gs.info(headers);

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[shivaprasad1]

Hi Ankur,

I am not able to read the header of excel sheet and getting below error.

"Unable to parse excel file: error in loading headers from the xlsx data source: null"

Please let me know how to get the headers of excel sheet properly.

Note:  script is working fine for few and not working for few.

Thanks and Regards,

shivaprasad K N

[kunal16]

Hi Shivaprasad,

Did you get the solution or any workaround for this issue? Even I am getting the same null error for gs.info(headers).