File Import (CSV / XLS) file from Mid Server location

tkrishna29 · ‎06-15-2018

I see this question has been asked several times and I see a recommendation of using an app from ServiceNow guru / Servicenow share / crossfuze solution. Most of the recommendations were dated 2 years ago.

Anything changed in ServiceNow where it's straightforward to import data from Mid Server? Greatly appreciate if someone can give pointers on best approach to do it without much custom coding.

Thank you,

Krishna

brian_degroot · ‎06-22-2018

Hi Krishna,

Instead of writing the files to the MID Server host, it will probably be easier to have your 3rd party application write the file directly to the instance.

You can either send it as an import set to the instance with the following endpoint URL:

https://<instance>.service-now.com/sys_import.do?sysparm_import_set_tablename=<import_table_name>&sysparm_transform_after_load=true&uploadfile=<path>.csv

More information on this method can be found here:

Alternatively, you can have the file attached to an existing record, such as an incident, by using Attachment API

If that's not an option and you need to pull from the MID Server host, you can take advantage of the MID Server command probe. This allows you to run shell commands on the system. What this would look like is:

var probe = SncProbe.get("Command");
probe.setName("cd /Users/me/Desktop && cat example.csv | base64");
probe.create("<MID Server Name>");

The response to this will be a base64 encoded string of the file that will get written to ECC Queue in the response. You can then turn this into an attachment with a script on the instance

var encStr = '<long base64 encoded string>';
var stringUtil = GlideStringUtil;
var attachment = new Attachment();
attachment.write('<table>', '<record sys_id>', '<file name>', 'text/csv', stringUtil.base64DecodeAsBytes(encStr));

I'm sure there's a better approach than that, but hopefully that gives you some ideas to work with. Best of luck!

-Brian

View solution in original post

jaytee_itsm · ‎11-05-2018

Good Day

Hi Krishna, we have a similar requirements and checking if you could share your solution please? Thank you in advance

tkrishna29 · ‎11-08-2018

Hello Jaytee,

I utilized the "Remote File Import" solution I got from share.service-now.com. That worked for my scenario.

Regards,

Krishna

jaytee_itsm · ‎11-09-2018

Thank you Krishna. I did try it but it seems to be not doing anything, any modification to fit on your requirements? Basically, we just need to pull data from MID server and transform it to SN record.

Please advise

Jay Gamblin · ‎02-25-2019

We had a similar need where the source file was from an internal source so we had to use the linux MID server to get the file to the instance. We were successful in using curl and the attachment API to upload the file to a data source as an attachment. Business rules from there can be used to kick off a transform once a new file is uploaded. The curl was like so:

curl --user 'username':'password' --request POST --header "Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" --data-binary "@/apps/opt/application/servicenow/midserver/agent/import/filename.xlsx" "https://instance.service-now.com/api/now/attachment/file?table_name=sys_data_source&table_sys_id=a56002561bf767004287ecae6e4bcb98&file_name=test.xlsx"

Inacitive_User · ‎03-16-2019

Hi Brian,

I tried this URL which you've mentioned in above post:

https://<instance>.service-now.com/sys_import.do?sysparm_import_set_tablename=<import_table_name>&sysparm_transform_after_load=true&uploadfile=<path>.csv

for the last parameter, i have the file in D:\temp\b.xlsx in my laptop

i got the following error :

HTTP Status 400 – Attachment could not be successfully extracted.

not sure i am missing some steps here. can you please provide some input here