1

I don't know if someone will be able to solve my problem but let's try it anyway.

Here is my situation :

  • I have an automation that contains a filter activity (step 1), a data extract activity (step 2) and a file transfer activit (step 3).
  • The automation is working perfectly fine, file is correctly transfered to Marketing Cloud SFTP.

My problem is the following: when I open the CSV file downloaded from Marketing Cloud SFTP, data for one record is displayed on two different rows and I can't find a way to format it. I don't know if the problem comes from Marketing Cloud or not.

Here is how my data is displayed

enter image description here

Here is how my data should be displayed

enter image description here

UPDATE: I found the source of the problem. In Sales Cloud, data for the field "Address" contains a line break. Therefore, there is a line break in my DE and that is why data is displayed on two separate rows in CSV file.

2 Answers 2

1

This could also be actual customer input, if someone entered a line break into a text field in an improperly validated form page.

The characters you would need to replace to clean this up could be either Carriage Return (CR) or Line Feed (LF). If your data originates on a cloud page or other form, the best solution would be to introduce a backend validation logic at that source (e.g. replace already on the handler page using AMPScript Replace() function)

If you just want to clean it out post-hoc, you can use SQL logic to replace CR and LF with spaces:

REPLACE(REPLACE(myAddress, CHAR(13), CHAR(32)), CHAR(10), CHAR(32)) AS myAddress

CHAR(13) is CR, CHAR(10) is LF, CHAR(32) is space.

Source: https://www.mssqltips.com/sqlservertip/6022/sql-server-char-function-and-reference-guide/

google search term: t-sql char reference

1
  • Hi @Jonas Lamberty, thanks for your answer! In fact, there is a line break in my DE in Marketing Cloud, but that hasn't been done on purpose, it has been made by the system. Also, my data comes from Sales Cloud. Feb 12, 2020 at 13:26
1

By the sounds of the issue, I am likely to think the issue is because of the encoding of the extracted file.

You can enable/disable encodings within Data Extracts through Salesforce professional services team.

Here are some of the encoding types that I have enabled on my account:

enter image description here

Personally, encoding the file to UTF8 have worked for me but this could be dependent on the system's encoding requirements.

2
  • Hi @Brad Sapkota, thanks for youre reply! What do you mean by "system's encoding requirements"? Feb 12, 2020 at 13:21
  • The system denotes to the target application which will ingest these files, for example analytics or business intelligence tools Feb 12, 2020 at 21:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .