Hello everyone,
Below are the steps which i followed to make connection with SFTP server and download the file from SFTP server.
Considered 2 scripts here, 1. Map/Reduce, 2. SuiteLet
1. Map/Reduce:
- In getInputData() - created a saved search - Created the Customer payments search in my case.
- processed the data from "map" function(key/value pair)
- In Summerize section collected the data and pushed the results into csv file and created the file in a folder in file cabinet.
- Established a connection with SFTP server (Received the Password GUID from the suitelet)
- Uploaded the CSV file in SFTP server (unfortunately the considered test sftp server doesn't have the "write" permission hence i'm unable to upload, but this code works you can trust and use this, as it worked for one real time sftp server).
- Downloaded a ".txt" file SFTP server in file cabinet.
2. Suitelet:
- Created 2 fields in form one for user name capturing and another credential field is password field (enter the sftp server user name and password here)
- the password field will be encrypted and send the value to Map/Reduce script parameter.
Above steps are my approach to connect to sftp server.
So, overall here are the steps to follow:
1. create a saved search.
2. Generate a CSV file using the saved search results.
3. Create a CSV file and upload in filecabinet.
Got the host key from same website, you can use ssh -keyscan hostname or password.
5. use the host key in the script where you use sftp connection.
6. Create a Suitelet script and generate GUID using the sftp server password.
GUID stands for Global Unique Identifier. This is a unique identifier generated in Oracle NetSuite by a particular algorithm. You can use this algorithm to generate a GUID for a specific domain and script.
7. Create the connection and upload the CSV file into SFTP server or download the file from sftp to NS file Cabinet.
Code:
Map/Reduce Script:
/**
* @NApiVersion 2.x
* @NScriptType MapReduceScript
* @NModuleScope SameAccount
*
* This script push the saved search results into a Folder in FileCabinet
* From File Cabinet, script uploads file from filecabinet to SFTP Server.
* Also, this script downloads a file from SFTP server to File Cabinet.
*
*/
define(['N/file', 'N/sftp', 'N/search', 'N/url', 'N/runtime'],
/**
* @param {file} file
* @param {search} search
*/
function(file, sftp, search, url, runtime) {
/**
* Marks the beginning of the Map/Reduce process and generates input data.
*
* @typedef {Object} ObjectRef
* @property {number} id - Internal ID of the record instance
* @property {string} type - Record type id
*
* @return {Array|Object|Search|RecordRef} inputSummary
* @since 2015.1
*/
function getInputData() {
// Create Customer Payments Saved Search
return search.create({
type: "customerpayment",
filters:
[
["type","anyof","CustPymt"],
"AND",
["mainline","is","F"]
],
columns:
[
"entity",
"statusref",
"amountpaid"
]
});
}
/**
* Executes when the map entry point is triggered and applies to each key/value pair.
*
* @param {MapSummary} context - Data collection containing the key/value pairs to process through the map stage
* @since 2015.1
*/
function map(context) {
var searchResult = JSON.parse(context.value);
var transId = searchResult.id;
var entityId = searchResult.values.entity.text;
var status = searchResult.values.statusref.text;
var amount = searchResult.values.amountpaid;
//key, value pairing
context.write(searchResult.id,entityId+','+status+','+amount);
}
/**
* Executes when the reduce entry point is triggered and applies to each group.
*
* @param {ReduceSummary} context - Data collection containing the groups to process through the reduce stage
* @since 2015.1
*/
function reduce(context) {
}
/**
* Executes when the summarize entry point is triggered and applies to the result set.
*
* @param {Summary} summary - Holds statistics regarding the execution of a map/reduce script
* @since 2015.1
*/
function summarize(context) {
var contents = 'ENTITY ID'+','+'STATUS'+','+'AMOUNT\n';
var totalRows = 0;
context.output.iterator().each(function(key, value) {
contents += value+'\n';
totalRows++;
return true;
});
//set the date and time
var date = new Date();
var dd = date.getDate();
var mm = date.getMonth()+1;
var yy = date.getYear()+1900;
var time = date.getHours() +":"+date.getMinutes()+":"+date.getSeconds();
//append the date and time values to the filename
var file_name = "Customer Payments"+"_"+dd+"/"+mm+"/"+yy+"_"+time
//Create a CSV file and place the search results into it
var fileObj = file.create({
name: file_name,
contents: contents,
folder: 12641,
fileType: 'CSV'
});
var FileObjNum = fileObj.save();
// Get the password GUID from the script parameter (**The GUID is created in Suitelet script)
var pwdVal = runtime.getCurrentScript().getParameter({name:'custscript_custom_password'});
log.debug("pasword Value",pwdVal);
var json = JSON.parse(pwdVal);
log.debug("json data",json);
var myPwdGuid = json.password;
// Get the host key from the SFT server
var hostKey = "AAAAB3NzaC1yc2EAAAABJQAAAQEAkRM6RxDdi3uAGogR3nsQMpmt43X4WnwgMzs8"
hostKey += "VkwUCqikewxqk4U7EyUSOUeT3CoUNOtywrkNbH83e6/yQgzc3M8i/eDzYtXaNGcK"
hostKey += "yLfy3Ci6XOwiLLOx1z2AGvvTXln1RXtve+Tn1RTr1BhXVh2cUYbiuVtTWqbEgErT"
hostKey += "20n4GWD4wv7FhkDbLXNi8DX07F9v7+jH67i0kyGm+E3rE+SaCMRo3zXE6VO+ijcm"
hostKey += "9HdVxfltQwOYLfuPXM2t5aUSfa96KJcA0I4RCMzA/8Dl9hXGfbWdbD2hK1ZQ1pLv"
hostKey += "vpNPPyKKjPZcMpOznprbg+jIlsZMWIHt7mq2OJXSdruhRrGzZw=="
var sftUrl = "test.rebex.net";
// Connect the SFTP with the details
var connection = sftp.createConnection({
username : 'demo',
passwordGuid : myPwdGuid,
url : sftUrl,
port : 22,
directory : "/",
hostKey : hostKey
});
log.debug("Connection established successfully")
log.debug("File Objet",FileObjNum);
/**
* Downloading a ".txt" file from external SFTP server to File Cabinet.
*/
var downloadedFile = connection.download({
directory : '/pub/example',
filename : 'readme.txt'
});
downloadedFile.folder = 12641;
downloadedFile.save();
log.debug("Downloaded Successfully)")
/**
* Uploading the file to the external SFTP server.
*/
var myFileToUpload = file.load({
id : FileObjNum
});
connection.upload({
directory : '/aspnet_client/system_web',
filename : file_name,
file : myFileToUpload,
replaceExisting : true
});
log.debug("File Uploaded successfully");
}
return {
getInputData: getInputData,
map: map,
// reduce: reduce,
summarize: summarize
};
});
Suitelet:
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
define(['N/ui/serverWidget', 'N/task', 'N/file','N/runtime'],
/**
* @param {serverWidget} serverWidget
*/
function(serverWidget, task, file, runtime) {
/**
* Definition of the Suitelet script trigger point.
*
* @param {Object} context
* @param {ServerRequest} context.request - Encapsulation of the incoming request
* @param {ServerResponse} context.response - Encapsulation of the Suitelet response
* @Since 2015.2
*/
function onRequest(context,FileObjNum) {
if (context.request.method === 'GET') {
// Create a GUID form
var form = serverWidget.createForm({
title: "SFTP - GUID Form",
});
// add the username field
form.addField({
id: 'username',
type: serverWidget.FieldType.TEXT,
label: 'Username'
});
// add password field
var credField = form.addCredentialField({
id: 'password',
label: 'Password',
//make sure you mention the script where SFTP connections takes place
restrictToScriptIds: 'customscript_nf_mr_uploadcsv',
restrictToDomains: "test.rebex.net"
});
credField.maxLength = 64;
//Create a Submit button
form.addSubmitButton({
label: 'Submit Button'
});
context.response.writePage(form);
return;
}else {
var requset = context.request;
var myPwdGuid = requset.parameters.password;
log.debug("myPwdGuid", myPwdGuid);
context.response.write(myPwdGuid);
}
var objParam = JSON.stringify({"password": myPwdGuid});
//Create task to call Map/Reduced
var mrTask = task.create({
taskType: task.TaskType.MAP_REDUCE,
scriptId: "customscript_nf_mr_uploadcsv",
deploymentId: "customdeploy1",
params: {'custscript_custom_password': objParam} //use the MapReduce script parameter internalid here
});
var mrTaskId = mrTask.submit();
var taskStatus = task.checkStatus(mrTaskId);
log.debug("taskStatus", taskStatus);
}
return {
onRequest: onRequest
};
});
Output Screenshots:
1.GUID - Generation screen - from Suitelet
2. Create file in file cabinet and check the download file from sftp server.
3. Check the log whether connection established successfully or not and downloaded the file successfully or not
4. Check the .txt file in sftp server which is downloaded successfully.
Unfortunately I don't have proper SFTP server where i cannot upload the file in file sftp server, but able to download the file into file cabinet. I'm sure this code will definitely work if we have proper sftp server with all permissions.
Note: If any issue like permission issue, like failed to upload then system will throw below error:
{"type":"error.SuiteScriptError","name":"UNEXPECTED_ERROR","message":null,"stack":["anonymous(N/file)","summarize(/SuiteScripts/Ram Suite Script files/_nf_uploadcsv_mr.js:160)"],"cause":{"type":"internal error","code":"UNEXPECTED_ERROR","details":null,"userEvent":null,"stackTrace":["anonymous(N/file)","summarize(/SuiteScripts/Ram Suite Script files/_nf_uploadcsv_mr.js:160)"],"notifyOff":false},"id":"c66e19b0-046f-4c01-887c-26e8a6e8724e-2d323031392e31302e3135","notifyOff":false,"userFacing":false}
But, please follow my suggestions, you will be able to upload the file in file cabinet.
Also, suggest me if you have any opensource sftp connection details including hostkey, i'll try to connect.
Thanks,
Ramu
