0

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.

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

| improve this answer | |
  • 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. – Grégoire Sarrola Feb 12 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.