Issues with Unicode uploads

[Anonymous]

Has anyone else noticed a change when uploading unicode files? I tried several different methods of uploading a file (including this Import a Non-Latin Characters List - Marketo Docs - Product Docs​), but I always seem to have to retype the headers in the upload process:

I also noticed that the bottom of the form says that zero fields are ignored, but that's definitely not the case.

Any one have insight or want to vent about the same issue?

[Dan_Stevens_]

On Monday (3/27), engineering pushed a patch into production to address this.  I did hear back from my team and they confirmed that the Unicode import is now working as expected.

[Anonymous]

Anyone have any luck yet? Marketo Support is saying that it should work as UTF-8, but I am not getting any different results.

[Dan_Stevens_]

Nothing yet.  Support is still telling me they're looking into it. We have a bunch of lists we need to upload, but can't.

[SanfordWhiteman]

Hey Dan,

Convert Excel's UTF16LE file to UTF8 and it'll work.

To do this I used the classic ​iconv​ which you can get from LibIconv for Windows

Command line:

iconv -f UTF-16 -t UTF-8 "E:\My Documents\unicode_export_utf16lebom.txt">"E:\Unicode_tests\unicode_export_utf8nobom.txt"

Screenshot showing success:

[Dan_Stevens_]

Thanks Sandy, but these are already in UTF-8 format.  Even if this workaround is a solution for whatever reason, we can't expect our marketers in every country to download this software (which will have to go through a security review) and process every list using the command line approach.  The fact is this was such an easy process for us for years - take Excel file, save as Unicode text file, import into Marketo.  Done.  And now, since the Winter release, this no longer works.

[SanfordWhiteman]

Excel "Unicode Text" is UTF-16LE, not UTF-8.  UTF-16LE w/BOM (which Marketo is misleadingly calling "binary") is no longer supported, apparently, but UTF-8 w/no BOM is ok.

Thought you said you had lists waiting to be imported, so this would be how to get 'em done.

(FWIW you could write an Excel macro to do the conversion, too -- I just didn't feel like it as iconv was out there, and it's been a standard Linux/Mac OS tool for many years.)

[Dan_Stevens_]

So even when saving a Unicode text file as follows, it's still not really UTF-8?

As far as it not being supported any more, the last I heard from support is the following:

This issue is affecting multiple customers, and Engineering is working diligently to see this resolved. I will update you further as progress is made.

So hopefully it's still supported and just considered a bug that's going to eventually be resolved.

[SanfordWhiteman]

That dialog only applies to saving as HTML! The TSV generated is still ISO-8859 (or maybe Windows-1252) not UTF-8.

Note (since there seems to be confusion above and from Marketo) Excel UTF-16LE is still tab-delimited text, not "binary"... there's no magical format that avoids the need for a delimiter w/variable-length data!

You could have UTF-16LE that's comma-delimited as well. The question of delimiter collisions (i.e. commas within data) is a good one, but quoting should automatically take care of that. Also, using tabs instead of commas just shifts the delimiter collision over to tabs, it doesn't eliminate it. You can have tab characters within a table cell/column, too.

[SanfordWhiteman]

Danny Tran​ Gary Verster​ Dan Stevens​ I packaged the encoding conversion as a simple Excel VBA macro.

You can get it from here: https://s3.amazonaws.com/blog-images-teknkl-com/marketo_import_template_v1.xlsm

When you run the macro ExportAsUTF8 you get a Save As dialog:

The filename you choose is stored as the standard Excel "Unicode Text" (UTF16LE as mentioned above), and also saved to a separate file as UTF8 (in the same directory) that's suitable for Marketo importing. The latter is suffixed "_MarketoReady_utf8".

Give it a shot and let me know how it goes. The sheet was Authored in Excel 2007/Windows and tested in Excel 2013/Windows as well.

[Gary_Verster2]

Thanks, Sandford. As per Dan's reply, thanks for working on this and coming up with an interim solution.

That said, I won't be retraining my marketers on how to upload using this process just yet... explaining that they need to enable macro's and then showing them how to run the fix is probably a step too far for more than a few of them, especially if it's an interim fix. But it will definitely help me to fill the gap myself more painlessly in the meantime!

Thanks, chap!

[Dan_Stevens_]

Thanks Sandy - that worked (using Excel 2016).  Really appreciate you taking the time to develop this interim solution for us while the engineers at Marketo figure out a way to fix this bug.

Latest response from Marketo Support:

We are continuing to investigate the full scope of this issue, prior to making an immediate changes. I will update you further as progress is made.

[SanfordWhiteman]

Blogged at http://blog.teknkl.com/fixing-the-current-excel-marketo-list-import-bug-unicode-characters/​

Spread the word!

[Josh_Hill13]

Interesting thread. I had an odd thing yesterday that was likely related to Excel 2011 Mac where I saved as CSV, but Marketo claimed it had an "Unclosed quotation" which I had not noticed before. I looked at the text file and it had somehow created a mess on the header. When I opened in Excel again, the first two rows were misaligned.

Then I saved as Windows CSV with no further problems.

I know there are better Mac text editors that'd help here. Hope this is fixed soon.

[SanfordWhiteman]

But no non-Latin-1 characters in there, yes?  Windows CSV uses Windows-1252 so you can store

     þþþ

but not

     āāā

(to use an example of crossing the character boundary from #255 to #257)

[Anonymous]

I tried this solution below and it works for me:

Special Characters when downloading data from Marketo or Uploading into Marketo

[Gary_Verster2]

Hi Said,

Yeah, the comma-separated UTF-8 upload is always a fallback, but this could introduce extra columns into the data (which is why there is the 'Open in Wordpad/Notepad' step in the solution). The chances of getting a comma in an address field is not exactly low, so what should be a relatively clean, quick and painless upload of a tab-delimited file could easily become a much longer, painful one. And I fear for what would happen to those people who are less adept with data cleaning...

Needs to be fixed... that's all there is to it.

G.

[Dan_Stevens_]

Totally agree.  Using the Unicode approach is the simplest and least error-prone way to get these lists into Marketo. Why they would remove this functionality is beyond me and alienates Marketo's international customers.

[SanfordWhiteman]

For my usual nitpicky reasons I'm gonna point out that Unicode isn't a file format (despite the label): it's still UTF-16 LE, which isn't any more legit Unicode than UTF-8.

There's clearly a regression, of course. It may be in Marketo's endianness (expecting BE as opposed to LE).

[Dan_Stevens_]

This issue exists for us as well.  After selecting the list and setting the options, we see this:

After manually selecting the proper fields to map, the import results in new leads with no data!

This is a huge issue for us as we are a global company.  We import program members weekly (and sometimes daily) containing leads with special characters.

[Dan_Stevens_]

Update from Marketo Support:

We have found that the problem is related to changes between php 5.3 and 5.6 (which was upgraded around the same time as release). It is not able to process a special character present in an existing file.  We are currently working on building a patch for this. I will continue to update you on that as progress is made.