Uploading to External Connector

Jasleenk
Jasleenk Member
in Magic ETL

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.

Answers

  • ArborRose
    ArborRose Coach

    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.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Jasleenk
    Jasleenk Member

    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
    ArborRose Coach

    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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • Jasleenk
    Jasleenk Member

    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!

Categories