How to set up Scheduled Import for a dynamically changing file name and add CSV file extension?

[mfestger]

Hello,

Business Requirement: Set up a Scheduled Import to retrieve a file from an SFTP. 

I have two issues.

1.) The problem is that the file that is being retrieved does not have a valid file extension. I need to be able to add ".CSV" to the file name prior to import. What would be the best way to go about this? Considering...

2.) The naming convention of the file is not static. As a result, a new file is inserted into the SFTP daily. They are differentiated by having a date and time stamp inserted in the middle of the file name. An example would be: "X.XXXX.XXX.D180712.T0950260.147424417". The sections by "D" and "T" are Date and Time respectively.

In another community post, I saw someone with a similar problem and was able to write a Scheduled Job that altered the Data Source prior to the Scheduled Import to match the new file naming format. This works as the "X" characters above are all static for every file. Everything after that changes daily and cannot be predicted thus rendering my code useless in this state.

The Scheduled Job is below:

var grDS = new GlideRecord('sys_data_source');

grDS.setWorkflow(false);

grDS.autoSysFields(false);

if (grDS.get('076b8809db975300050f72c08c96191b')) {
	
	var gdNow = new GlideDateTime();
	
	var month = gdNow.getMonthLocalTime().toString();
	month = month.length == 1 ? '0' + month : month;
	
	var day = gdNow.getDayOfMonthLocalTime().toString();
	day = day.length == 1 ? '0' + day : day;
	
	var yearGrabber = gdNow.getYearLocalTime().toString();
	var year = yearGrabber.substring(2);
	
	grDS.file_path = '' + 'X.XXXX.XXX.D' + year + month + day; 
// We need to somehow append the rest of the file name here so the file path is correct
	grDS.update();
}

Does anybody on the community know of a way this code can be edited to ignore or perhaps remove the remainder of the file name after the date? That way the Scheduled Import would receive this: "X.XXXX.XXX.DYYMMDD,csv"

Perhaps there is another way of doing this? Any and all help will be appreciated, thank you in advance.

-  Mike

[Ankur Bawiskar · Accepted solution]

Hi Mike,

I have faced similar issue earlier and used the same approach. Just before the scheduled data import runs change the file name with the path you are sure file will be present. There doesn't seem to be any other approach. you cannot use any regular expression in the file path to math certain file name and pick that up. It requires complete path of the file.

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