Uploading to External Connector

Jasleenk

I am uploading data from a Magic ETL output dataset to Amazon S3 using the connector. I have a column with phone numbers (Text Datatype) which is automatically appending an apostrophe (`) before the number. I need to get rid of that apostrophe before uploading it.

ArborRose

You could try using a formula tile with a formula like

CASE

  WHEN LEFT(`phone`, 1) = '\'' THEN

    SUBSTRING(`phone`, 2)

  ELSE

    `phone`

END

Checks to see if first character is a single quote and removes it using substring.

Jasleenk

Hi, Thanks for the formula. Tried, but it didn't work.

My ETL has an Output dataset which is uploaded to S3 via connector. The file has 2 columns- Channel (Text field) and Number (Text field each number in the format +1xxxxxxxxxx). But the numbers are getting stored with an apostrophe (`). I am looking any way to get rid of apostrophe before uploading file to S3 via connector.

ArborRose

I've seen that when text fields look like numbers. Try something like this

CASE WHEN LEFT(`Number`, 1) = '''' THEN SUBSTRING(`Number`, 2) ELSE `Number` END

Just in case the following is useful to you. I just did a formula yesterday to deal with phone numbers like that. I strip the parenthesis, dash, plus sign, and the 1 after the plus sign with this

CASE 

  WHEN LEFT(

    REPLACE(

      REPLACE(

        REPLACE(

          REPLACE(

            REPLACE(`phone_work`, ' ', ''),

          '(', ''),

        ')', ''),

      '-', ''),

    '+', ''), 1) = '1'

  THEN SUBSTRING(

    REPLACE(

      REPLACE(

        REPLACE(

          REPLACE(

            REPLACE(`phone_work`, ' ', ''),

          '(', ''),

        ')', ''),

      '-', ''),

    '+', ''), 2)

  ELSE REPLACE(

    REPLACE(

      REPLACE(

        REPLACE(

          REPLACE(`phone_work`, ' ', ''),

        '(', ''),

      ')', ''),

    '-', ''),

  '+', '')

END

Jasleenk

Many Thanks for the solution. Though it didn't work but I enclosed individual numbers in double quotes and checked 'Remove Quotes' option in S3 connector setting. It worked :D

Thanks!