1

When doing a DE export using automation studio data extension export activity, I've exported data from a DE to a file to SFMC SFTP with tab delimitation. I've tried as .csv and and .txt but when I open with Excel (as the end recipient of this file will do) It shows all columns in column A only. I've also tried commas.

I'm not sure what I'm doing wrong.

It exports as UTF-16 so there is a conversion step to covert to UTF-8.

Any ideas?

1

The exported CSV or Tab delimited TXT file are formatted in a very basic text type format, which means that excel, etc. would not recognize the columns and formatting without setting these formatting options.

You will need to import the csv or txt file inside excel using 'Data From Text' option.

enter image description here

You would click the From Text option in the 'data' tab and then browse for your file.

enter image description here

From there you would select 'delimited' and then choose the char to delimit the file (comma or tab) and then mark every column as 'text' to ensure it retains all data on format.

enter image description here

Then you click finish and it should open the file correctly formatted.

enter image description here

| improve this answer | |
  • Thank you for the detailed response with pictures. Do you know why all of this is necessary when exporting data using automation studio but not if you export directly from the Data Extension? – JulieHurtz Apr 15 '19 at 15:03
  • The export via UI is configured and formatted to fit into a spreadsheet format (assumption is it will be used in Excel or similar spreadsheet software), the AS export is designed more for usage to import into other platforms, services, etc. where this formatting can cause issues with the data passed. By keeping it in the simple delimited text format, it removes complications (reduces risk of error) and also allows it to be more performant due to a smaller file size. – Gortonington Apr 15 '19 at 15:52
  • 1
    Thank you. Ultimately what we found was that when SFsupport set up a custom data extract type for us to convert from UTF-16 to UTF-8, there must've been something wrong bc a 2nd version of the data extract type is working as expected. That's not a full explanation of the problem so it may not help everyone, but I'd say if you do run into this issue and had Salesforce configure a data extract type for you that you should contact them to look into correcting the issue on their end becase there isn't anything you can do in the front end UI for data extract types unfortunately. – JulieHurtz Apr 15 '19 at 19:21
0

In order to export Data Extensions as *.CSV or *.TXT files, that are easily recognised by Excel, you will have to utilise a special type of Data Extract.

The type is called File Convert (if it's not available in your account, ask Salesforce support and they will enable it for you). File Convert

Choose one target encoding. Currently, you can convert the file to:

  • ASCII
  • UNICODE
  • UTF-16
  • UTF-8
  • UTF-7

The final Automation workflow will consist of 3 Activities.

  1. Data Extract type Data Extension Extract (extracts your Data Extension to a Safe House)
  2. Data Extract type File Convert (converts your Extract to the desired encoding)
  3. File Transfer (moves your file from Safe House to the SFTP)

NOTE: Avoid using %%Second%% Wildcard in the file naming pattern. Processing takes some time and the consecutive Activities will have issues. To be safe, I would recommend avoiding %%Minute%% Wildcard as well.

| improve this answer | |

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.