Export to Excel - UTF-8

[Tsanka]

Hi

Our end users are complaining that when they export data from PowerBI and open it in Excel, the text with cyrillic letters is not readable. This is due to the fact that the export is in UTF-8 and one cannot force the Excel to recognize the encoding. Therefore the workaround is - save the file in csv, open it with a text editor, save it as ANSI and then open it with Excel. 

This is not an issue for an IT guy, however in our case Power BI is used from non-IT C-level managers and the above steps are too complicated and annoying for them.

Does anyone have an idea how to solve this? I have read the Excel forums,  have tried wth different browser options - unfortunately with no success.

Thanks

Tsanka

[Tsanka]

Update - it seems currently the problem is solved. I am able to export data with cyrillic and to open them directly in Excel.

[KamilG]

Three weeks ago, something was changed in csv file. Now line with "sep=," is deleted when enything is exported from Power BI. This mean that the file can't be open in Excel. It is always opening as a text not divided into column. User have to import data from text to Excel which is very annoying. How to change it that Excel will open the file as was before change?

[SandySan21]

I think sep= is removed because it cause Excel to ignore BOM in the file which caused other issues.

For your scenario, you can change the list separator in Window.

1. Click the Windows Start menu.
2. Click Control Panel.
3. Open the Regional and Language Options dialog box.
4. Click the Regional Options Tab.
5. Click Customize / Additional settings (Win10).
6. Type a new separator (comma) in the List separator box.
7. Click OK twice.

[SandySan21]

I believe 'sep=' instruction was removed because it will make Excel to ignore the Byte Order Mark which breaks many scenarios.

For your situation, you could workaround the issue by changing separator in windows control panel

1. Click the Windows Start menu.
2. Click Control Panel.
3. Open the Regional and Language Options dialog box.
4. Click the Regional Options Tab.
5. Click Customize / Additional settings (Win10).
6. Type a new separator (use comma) in the List separator box.
7. Click OK twice.

[KamilG]

For last 3 weeks file exported to csv was changed and line with "sep=," is deleted. Now Excel can't open it directly, always have to import from text to have it properly opened. Do anybody know how to change it that Excel can directly open file as was 3 weeks ago?

[sandrasanchez]

Why doesn't power bi export it in ANSI?? this way wouldn't be any problem, would it? I think changing it in power bi would resolve it, so lets say them , I have a ticket with this case but as more tickets more possibilities to be solved.... thanks

[morph268]

Because it will break for characters which can't be represented in ANSI <for example: Chinese, Korean etc>

[Tsanka]

If the problem is recognized by the PowerBI team, a solution can be found and delivered.

And the problem exists - the current export is clumsy for those unlucky to have data in encoding different from Win-1252.

[morph268]

Well, what I am trying to say is that instead of asking Power BI to have workaround which have 0 workaround in some situation.

You could probably vote for the following:

https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/9783765-support-utf-8-for-csv-im...

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14154036...

Which probably would benefit a larger audience, since lots of people complain about that:

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

just my $0.02 🙂

[Greg_Deckler]

Have the users try this:

1. Open Excel
2. Import the data using Data-->Import External Data --> Import Data
3. Select the file type of "csv" and browse to your file
4. In the import wizard change the File_Origin to "65001 UTF" (or choose correct language character identifier)
5. Change the Delimiter to comma
6. Select where to import to and Finish

[Tsanka]

Hi @Greg_Deckler and thanks for the reply.

The default application for opening csv files is set to MS Excel. Because of this the exported data file is opened directly with Excel and the cyrrilic letters are shown as gibberish.

I was thinking of setting the default csv application to Notepad, then the users could copy the text lines (all but the first), open Excel and from Paste->Use Text Import Wizzard paste the text into the sheet. Unfortunately I am sure the users will not like it.

[Greg_Deckler]

Yeah, understood, either way it is fairly complex for the user versus "double-click the file". Not sure of a way around it short of writing VBA code of some sort.

[sofi]

Hi, we have bag that describe below:

We get the first row the letters [sep=,] its prevent us to open file in excel as you instructed us.

Thanks