[SalesForce] SFMC query help- removing carriage returns from data extension

I have a nightly file that pulls all susbscriber bounces through an automation that recently began adding carriage returns to it and won't be fixed until the next release. Support gave me the steps for a work around below, but I need help with the SQL code. I have VERY limited SQL skills but below is what I have. Syntax checking says I have the below errors. Please help!

  • An unmatched parenthesis occurs in the query.
  • Error while parsing query. Please check the syntax
SELECT CLIENTID, SENDID, SUBSCRIBERKEY, EMAIL, LISTID, EVENTDATE, EVENTTYPE, BOUNCECATEGORY, SMTPCODE, BOUNCEREASON, BATCHID, TRIGGEREDSENDCUSTOMERKEY, PROMOID
FROM EEMSBouncesDaily_New 

REPLACE(REPLACE(CLIENTID,CHAR(10),'_'),CHAR(13),'_')) CLIENTID AND 
REPLACE(REPLACE(SENDID,CHAR(10),'_'),CHAR(13),'_')) SENDID AND
REPLACE(REPLACE(SUBSCRIBERKEY,CHAR(10),'_'),CHAR(13),'_')) SUBSCRIBERKEY AND
REPLACE(REPLACE(EMAIL,CHAR(10),'_'),CHAR(13),'_')) EMAIL AND
REPLACE(REPLACE(LISTID,CHAR(10),'_'),CHAR(13),'_')) LISTID AND
REPLACE(REPLACE(EVENTDATE,CHAR(10),'_'),CHAR(13),'_')) EVENTDATE AND
REPLACE(REPLACE(EVENTTYPE,CHAR(10),'_'),CHAR(13),'_')) EVENTTYPE AND
REPLACE(REPLACE(BOUNCECATEGORY,CHAR(10),'_'),CHAR(13),'_')) BOUNCECATEGORY AND
REPLACE(REPLACE(SMTPCODE,CHAR(10),'_'),CHAR(13),'_')) SMTPCODE AND
REPLACE(REPLACE(BOUNCEREASON,CHAR(10),'_'),CHAR(13),'_')) BOUNCEREASON AND
REPLACE(REPLACE(BATCHID,CHAR(10),'_'),CHAR(13),'_')) BATCHID AND
REPLACE(REPLACE(TRIGGEREDSENDCUSTOMERKEY,CHAR(10),'_'),CHAR(13),'_')) TRIGGEREDSENDCUSTOMERKEY AND
REPLACE(REPLACE(PROMOID,CHAR(10),'_'),CHAR(13),'_')) PROMOID

SFMC DIRECTIONS
As stated on the phone you will need to create a query that queries the data extension and removes the carriage returns from the data. I have provided an overview of the steps below.

  1. You will need to create a data extension to transfer the data from the current data extension being targeted by the Data Extract

  2. Create a Query that retrieves the information from the Sendlog Data Extension and removes carriage returns within the Data, storing this information within the Data Extension created in step one.

You will need to create a query that selects the data from the Data Extension in question, which contains the statement: 

REPLACE(REPLACE(*NameOfColumn*,CHAR(10),'_'),CHAR(13),'_')) *NameOfColumn*

This will replace all carriage returns in that field. This will then populate a resulting data extension in which you will be able to setup your current data extract to target. Using this method, you will ensure that carriage returns are not within the data that you are extracting. If you need assistance in creating the query, I would recommend visiting our Developer site salesforce.stackexchange.com, where you may post questions to our Developers directly at no cost to you. Please let me know if I can be of further assistance. Have a wonderful day!

  1. You will need to change the targeted data extension within the Data Extract activity from the original data extension to the DE created in Step 1. This will cause the data extract to retrieve the corrected data instead of the original data extension which contains carriage returns.

  2. You will need to create a automation that runs these steps in order.

The Automation Steps should follow this order: 

Step 1. Query Activity to remove carriage returns 
Step 2. Data Extract Activity 
Step 3. File Transfer Activity

This should correct your data issues and ensure that you receive the proper information.

Best Answer

REPLACE(REPLACE(*NameOfColumn*,CHAR(10),'_'),CHAR(13),'_')) *NameOfColumn*

In the above form and in every line of your code, the above has an unmatched parens. There's an extra parens at the end of the above line.

I don't know anything about SFMC, but you appear to have an extra parentheses in each line of your code (the very last one). I recommend you try removing it to see if it fixes your issue.

EDIT:

My "gut" is telling me to suggest trying something like this:

REPLACE((REPLACE(CLIENTID,CHAR(10),'_'),CHAR(13),'_') CLIENTID AND 
(REPLACE(SENDID,CHAR(10),'_'),CHAR(13),'_') SENDID AND
(REPLACE(SUBSCRIBERKEY,CHAR(10),'_'),CHAR(13),'_') SUBSCRIBERKEY AND
(REPLACE(EMAIL,CHAR(10),'_'),CHAR(13),'_') EMAIL AND
(REPLACE(LISTID,CHAR(10),'_'),CHAR(13),'_') LISTID AND
(REPLACE(EVENTDATE,CHAR(10),'_'),CHAR(13),'_') EVENTDATE AND
(REPLACE(EVENTTYPE,CHAR(10),'_'),CHAR(13),'_') EVENTTYPE AND
(REPLACE(BOUNCECATEGORY,CHAR(10),'_'),CHAR(13),'_') BOUNCECATEGORY AND
(REPLACE(SMTPCODE,CHAR(10),'_'),CHAR(13),'_') SMTPCODE AND
(REPLACE(BOUNCEREASON,CHAR(10),'_'),CHAR(13),'_') BOUNCEREASON AND
(REPLACE(BATCHID,CHAR(10),'_'),CHAR(13),'_') BATCHID AND
(REPLACE(TRIGGEREDSENDCUSTOMERKEY,CHAR(10),'_'),CHAR(13),'_') TRIGGEREDSENDCUSTOMERKEY AND
(REPLACE(PROMOID,CHAR(10),'_'),CHAR(13),'_') PROMOID)

The alternative being to also try removing the fieldname after the last parens (or even doing both the above and removing it after). For some reason, that seems unnecessary to me, but again, I'm not familiar with their code and haven't seen anything you may not have included in your post. It seems odd there would be nested REPLACE statements like that followed with the fieldname, first with options and then later without with more ANDs tacked on. It seems like there's potential to only need one outer replace statement. Its at least worth trying.

Related Topic