Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Tsanka
Kudo Collector
Kudo Collector

Export to Excel - UTF-8

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

13 REPLIES 13
Tsanka
Kudo Collector
Kudo Collector

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

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?

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.

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.

 

 

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
Helper I
Helper I

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

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

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
Microsoft Employee
Microsoft Employee

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

csv.jpg

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors