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

brian_degroot · ‎06-15-2018

Hi Krishna,

The biggest limiting factor is that the MID Server does not have an FTP Client implementation. It's much easier to configure imports using web services or JDBC. Are you looking to import files from the MID Server host itself? If so, there are ways you can interface with the host's terminal directly and run commands remotely. If you can go into detail on what the end goal is, I can try and give you some better recommendations.

Best regards,

Brian

Abhinav27 · ‎06-03-2020

I have given the below roles to the user - import_set_loader and import_set_admin. However when i do a post from Post to below URL i get an error "Sorry, an error occurred or this page isn't available"

1. https://<instancename>.service-now.com/sys_import.do?sysparm_import_set_tablename=u_service_now_user_report&sysparm_transform_after_load=true&uploadfile=D:\service_now_user_report.csv

2. https://<instancename>.service-now.com/sys_import.do?sysparm_import_set_tablename=u_service_now_user_report&sysparm_transform_after_load=true

Is there a step I am missed to enable the use of this method to import the data?

tkrishna29 · ‎06-17-2018

Hi Brian,

Thanks for the quick response. We have an 3rd party program that posts the CSV file to the mid-server location on a daily basis. We need to pick this file and load the data from this file to ServiceNow table on a daily basis.

What would be my options? As an alternative, I can ask the 3rd party program to do a FTP to ServiceNow. Is that possible? I thought they doing FTP to a mid-server is a better option. Please let me the best practice to approach to the solution. We have multiple files this way I get from other systems where they can send only xls / csv file and I have to receive it someway and load it's data to ServiceNow.

Greatly appreciate if anyone can help as well.

Thanks,

Krishna

tkrishna29 · ‎06-21-2018

Hello Team,

Greatly appreciate if anyone can throw some ideas on this. THanks.