Custom and predefined Import/Export formats and encoding

[Grégoire_Miche2]

When exporting data from Marketo, we always get a UTF-8 encoded CSV file. When importing, the semicolon and tab separated are allowed, but the encoding is llimited to UTF8 and UTF16.

But not all countries go by this format and encoding by default. Some will prefer semicolon or tabs, and will prefer Windows 1252/ANSI, ISO-8859-1/Latin 1 or japan, chineese or Korean encoding.

We should be able in admin to define "file format" that would be some combinations of formats and encoding. I would for instance define a "France" format that would be Windows 1252 encoding with semicolon separation. Then, when importing or exporting a file, I would just have a drop list with the available file formats, have to pick it and it will be immediately available, saving a lot of time and errors to users (conversion of the separator, conversion of the encoding).

Ideally, a series of ready to use file formats would be preset (the admin user being able to change or remove/deactivate some).

These custom "file formats" should also be made available when calling the import or export API's.

-Greg

[Dan_Stevens_]

Greg, when training some of our country marketers last week - specifically in Italy and France - we noticed that when they saved their test lists from Excel to CSV (no special characters, so this was fine) and imported that list into Marketo (by selecting the CSV option), Marketo only saw a single field of all of the data.  In other words, the "step 2" mapping windows only contained one field and all of the values within the entire spreadsheet were contained in this one cell.  When looking at the underlying CSV file, the fields were separated by semi-colons.  We then tried importing as "semi-colon delimited" and same issue.  Any tips on how to overcome this?

[Grégoire_Miche2]

Hi Dan,

This is typical to a few latin country, and hence the idea above...

In France or Italy, MS Excel, by default, is configured to work with semi-colon separated files and also with "," as a digit separator, instead of "." in the US. The "," being used for digits is the reason with the ";" is used for lists... The file encoding is also a problem. I do not remember for Italy, but for France, Excel saves file in the windows 1252 (aka ANSI) format... which creates a whole mess with accented characters when importing in Marketo. And MS Excel is very poor at handling these as it does not offer any options.

2 things you can do here:

• You can leverage that fabulous piece of software named Notepad that can convert the encoding (on save as, you have the option at the bottom of the screen) and also use it to:
  • remove all "," from the texts (otherwise it will create fake separators)
  • replace all ";" with ","
• Change your windows settings (but can cause issues if you have to exchange files with other users in the same country). Go to config panel -> regional parameters, then click the language preferences, and change the digit separator to ".", the thousands sep to " ", the list separator to ",". The problem with this is that it will impact the way you have to enter numbers in your spreadsheets.

None of these 2 are a perfect solution, so vote and have your European users to vote as well!

-Greg