Dealing with apostrophe in load statement

[chrisbrain]

I have a load script running inside a for loop, something like this:

for i=0 to $(noRows)-1 // loop through every row

    let textRaw = peek('Search_text',$(i),'TwitterConnector_Search'); // get the value for "text" field on each row

    Table1:

             LOAD

        '$(textRaw)' as Search_text,

        status as sentiment_status,

    FROM //.....

next

The problem is that some of the values contain apostrophes, meaning the expanded script (when viewed in the log file) ends up looking something like this:

LOAD

'''text with apostrophe in' as Search_text,

status as sentiment_status

FROM

// ....

Resulting in a:

Syntax error, missing/misplaced FROM:

Error.

Is there any way I can deal with this and load the textRaw into Table1 with apostrophes intact?

Thanks!

[matt_crowther]

Chris,

This may well fall in to the 'Cowboy Workaround' catergory but it could work:

Replace the apostrophes (chr(39)) with another non-used but standard character - for instance '^' (chr(94)) before the load so as to stop them causing problems before reversing the process after the load by replacing chr(94) with chr(39) to re-instate your apostrophes.

As I said "Yeee-Haaw".

Hope that helps a little,

Matt - Visual Analytics Ltd

[chrisbrain]

Haha thanks - but wouldn't this mean I didn't have the correct text in my Table1?

I have actually done a work around now to use a separate id parameter to link the table to the rest of the schema, so it's now something like:

     let textRaw = peek('Search_text',$(i),'TwitterConnector_Search'); // get the value for "text" field on each row          let id = peek('Search_text',$(i),'TwitterConnector_id');

    Table1:

             LOAD

        '$(id)' as Search_id,

        status as sentiment_status,

    FROM //..... Note textRaw is url encoded and used in load request here which is why we need it.

next

It would have been better if I could link on the text but I think this is a safer solution!

[swuehl]

Chris,

I am curious because I also have encountered similar problems.

I don't really understand your solution, for id, you query a field also named Search_text, but from a different table, right? Does this gives you an id, then?

One more idea:

Why do you think a text based link would be better? I would guess from a performance side, a numerical should be probably better. If you doubt that your id is as unique as your search text, you may create a separate hash value using e.g. hash256() function.

Regards,

Stefan