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

[Sashi K1]

Hi

If you are using a Data Source to attach a Excel spreadsheet to load data which is triggered by your scheduled script then review following field on the data source.

If your data source type is 'File' and Format is Excel, you would see two fields on the form. One is to inform data source to load from which tab on the spreadsheet called 'Sheet Number' and other is which row has the header row called 'Header Row'. Add your line number on the header row and put 1 on the sheet number. That should resolve your problem.

[Tee1]

Thank you Shashi,

Yes, I have seen the sheet number and row and it would work fine for the top few rows, but how would I get around the data in between the total, extension rows?

[Sashi K1]

Hi Tahir

Let them come over to your staging import sets. Use transform maps to ignore those columns and for rows use transform scripts

Use markers from your data to 'ignore' whole row transmission. During transformation, find out which rows have standard format to ignore.

Could you give me few examples on bottom rows?

[Tee1]

Hi Sashi,

On the attached screenshot it would be rows 22 & 23, these lines occur frequently down the spreadsheet. It is the transform scripts that I need help with, as i'm not sure where to start with them

Kindest Regards,

Tahir