Scheduled job pre script

[Tee1]

Hi,

I have set up a scheduled import job from an FTP Server which imports the spreadsheet file below, however I need sanitise the data before the data is "transformed" into the custom table.

Hope I can get some help with some scripting to remove rows 4 to 10 and rows 22 & 23 via the pre script option on on the scheduled data import job.

Also, there is a condition option on the scheduled job, can I use this functionality to do a check on whether I want this job to run? (the check would along the lines of ensuring a new file has been uploaded onto the FTP server in the last 24 hours)

Many Thanks

[Michael Ritchie]

In your Table Transform Map, check the Run script box and this will expose a script box.

In the script box, use a script similar to the following.   I don't know your import set field names, so just guessing based on your screenshot:

if (isNaN(source.u_extension)){

    ignore = true;

}

This uses the isNaN javascript function which you can read about here.   If the extension is not valid then it will ignore the row.   This assumes your spreadsheet has all numbers in that column.   If this isn't a correct assumption, I notice that date and time are empty, you could check for that condition as well.

Please mark any posts helpful or the correct answer to your question so others viewing can benefit.

[Tee1]

Thanks Michael,

I'm trying the script (below) you suggested but not having any luck. I have attached the excel document that I am importing and trying to exclude the Total and Extension rows.

if you have any other suggestions or amending the script, I would greatly appreciate them.

Kindest Regard,