Checking if a spreadsheet is populated in a datamodel reload

[ryanfoster84]

Hi,

i have the following code that loads in data from a spreadhseet

LOAD [Call ID] as z_Call_Report_id, 

     Text as Call_KeyWord, 

     Type as Call_KeyWord_Type, 

     Score

FROM

$(vExtData)text_analytics_calls_daily.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

but sometimes the spreadhseet when its populated comes back with no records EG Call ID, Text, Type and Score aren't in the spreadsheet.

is there a way to check this so the datamodel reload doesn't fail?

[Anonymous]

is this code right??

If(IsNull([Call ID]),I"Call ID"

I think it should be like:

If(IsNull([Call ID]),nul(),[Call ID]) as z_Call_Report_id

or still you can do like:

if(len(trim([Call ID])>0,[Call ID]) as z_Call_Report_id

[ryanfoster84]

LOAD [Call ID] as z_Call_Report_id, 

     Text as Call_KeyWord, 

     Type as Call_KeyWord_Type, 

     Score

FROM

$(vExtData)text_analytics_calls_daily.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

apologies i pasted a test i was doing

[sunny_talwar]

May be something like this:

SET ErrorMode = 0;

LOAD [Call ID] as z_Call_Report_id,

    Text as Call_KeyWord,

    Type as Call_KeyWord_Type,

    Score

FROM

$(vExtData)text_analytics_calls_daily.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

IF ScriptError >= 1 then

Your Alternative

UPDATE: Important thing here is the ErrorMode statement which will skip any errors and continue to reload the script.Make sure you SET ErrorMode = 1 as soon as you are done with this part of the script so that you still see errors elsewhere

[petter · Accepted solution]

The answer depends on whether the column headers themselves are missing or if it is only no rows or null-values in the rows constituting the data...

If the column headers are missing entirely this will do it:

Set ErrorMode = 0;
myTable:
LOAD [Call ID],
    Text,
    Type
FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
anError = ScriptError;
a = MsgBox(anError);

ErrorMode=0 turns off immediate error triggering/reporting during the script execution so the script write can do his/her own error handling checking the ScriptError system variable and it's siblings...

Of course you should do some sensible error handling instead of line 10 ...

If the data rows are missing but the headers columns are still there you could do:

nRows = NoOfRows("myTable");

[ryanfoster84]

Thanks for the replies have got it working

[gsbeaton]

Good solution Sunny T,

I'd also suggest ensuring that a log file was generated and regularly reviewed and you may want to provide the user with some information such as 'Last Successful Reload Time:'.