Welcome Guest

Pages: 1
Excel and UTF-8 (sigh)
stu.farnham@edgeofthep-
alouse.com
PostNovember 28, 2016, 13:26
Newbie
Posts: 10
Registered:
August 23, 2015, 04:28
Normal topicExcel and UTF-8 (sigh)

It looks like Excel 2016 *STILL* does not handle UTF-8 encoded CSV files correctly. My data contains many, many names with UTF-8 Characters that I want preserved across exporting/editing/importing a CSV/TSV.

I have a huge library and the easiest way to update the metadata is vis bulk export/import.

Does anyone know a solution or workaround for this problem? I would prefer to continue to do my editing in Excel (as opposed to LibreOffice or (ugh!) Numbers).

TIA,

Stu

2MR2PostNovember 28, 2016, 13:42
Avatar photo
Administrator
Posts: 2079
Registered:
August 23, 2012, 19:27
Normal topicRe: Excel and UTF-8 (sigh)

They this thread:

http://stackoverflow.com/questions/6002256/is-it-possible-to-force-excel-recognize-utf-8-csv-files-automatically

At the bottom it seems to imply that UTF16 might work. Yate supports UTF16LE and UTF16BE in CSV files.

stu.farnham@edgeofthep-
alouse.com
PostNovember 28, 2016, 14:09
Newbie
Posts: 10
Registered:
August 23, 2015, 04:28
Normal topicRe: Excel and UTF-8 (sigh)

That link was very helpful. It turns out that Excel 2016 Mac does support UTF-8 with a couple of gyrations.

Alternate encodings are not supported if you double-click to open the file. Instead, you have to go to the FIle->Import... menu option; select the input file type (CSV or, for a TSV, text file), and select the input file in the picker.

The important setting is on the first screen of the import wizard. Open the File Origin dropdown; there's a long list of file types including UTF-7, -8, -16BE and -16LE

UTF-8 works like a champ.

Rant follows:

Excel needs a usability expert (but so does the rest of Microsoft). The double-click-to-open feature should detect the file type and at least warn if it is not standard ASCII -- or better yet do the right thing as opposed to sending users digging through menus and wizards. Can you say discoverability, boys and girls?

I read an interesting article recently contrasting Slack with Microsoft Teams. The gist was that, while Microsoft Teams was more feature rich, the Slack experience was frictionless, and that lack of friction made Slack preferable despite its lack of features.

Thus endeth today's rant.

Stu

stu.farnham@edgeofthep-
alouse.com
PostDecember 4, 2016, 21:28
Newbie
Posts: 10
Registered:
August 23, 2015, 04:28
Normal topicRe: Excel and UTF-8 (sigh)

My update is running at long last (we'll how long it takes to get through 152K files). I'm hoping that my experience in doing this project will help others who go down asimilar path.

The most annoying part of the whole process was the last: getting the import to run. The problems were not with Yate, but rather with data transparency.

Yate (correctly) wants UTF-8 encoded input. Although both Excel and LibreOffice can export TSV files, neither of them can do so as UTF-8 encoded (Apple's Numbers was ruled out due to file size considerations. As a result, my data (and filenames, which are also part of the dataset) needed to have commas converted to something innocuous; I chose the tilde(~) as it did not occur anywhere in the data.

The second problem was quotes in the data; these confused a parser during the import so also had to be converted; I chose the carat (^) character.

Here is where I put in a plug for the Mac utility A Better Finder Rename, which made the process of replacing tens of thousands of characters reasonably painless.

Several steps remain when the import is done:
* load all my files into Yate and turn the ~s and ^s back into commas and double quotes
* use A Better Finder Rename to do the same to my files
* take a fresh backup of my library. There have been several times in the course of this project that having a backup saved my bacon (I actually have 4 levels of backup, but that's another story.
* rebuild my Plex and Audirvana libraries with the new metadata.

Did I mention making sure you have backups? I did, but I will say it again.

Thanks to Barry for Yate and for his assistance during this project.

Stu

Pages: 1
Mingle Forum by Cartpauj | Version: 1.1.0beta | Page loaded in: 0.018 seconds.