When importing Excel Workbook having Superscript text in Header it does not show the column involved

[CJW1960]

When trying to import an Excel Workbook,  using Get and Transform Data, having Superscript text in Header, it does not show the second column, having the Superscript text.
However when double clicking the same Excel Workbook it does show the column:
Superscript text example "[m³]" (for cubic meters)

[PhilipTreacy · Accepted solution]

yeah understood @CJW1960 , not surprising but worth asking.

Is there no way to alter the way the XLS files are produced to stop the superscript characters being used?  Or to export the data in a different format like CSV?

Phil

[edhans · Accepted solution]

Sorry @CJW1960 - didn't see you had attached it already. Yes, like @PhilipTreacy I get the error. There are twopossible ways to fix it.

1. Remove the superscript char
2. Convert the workbook to XLSX. I confirmed saving as XLSX fixes the issue.

This is the second confirmed issue I've seen with XLS formatting and Microsoft will not fix. I reported this to them earlier this year and the product team looked at it and basically went "Yup. You are right, it fails, but we aren't going to spend any time coding around an Excel format that was EOL'd in 2007." And I fully understand.

The only suggestion they offered was to install the Access 2010 Engine Redistributable. Links and more info here. That code actually has some stuff for XLS files. However, it did not fix my issue, so no promises here.

[CJW1960 · Accepted solution]

Thanks @edhans, tried the potential Access 2010 solution but as you said it did not fix the issue.
Think i will create a workaround opening all files and saving before closing using a macro.
Cheers, Kees

[PhilipTreacy]

Hi @CJW1960 

Yes, I got the problem with that file.

But if I convert the data in the file to a table (CTRL+T), PQ sees both columns.  Is converting the data to a table an option for you?

Or opening and saving the file as XLSX?

Or just leaving the superscript character(s) out of the header?

Phil

[CJW1960]

Hi @PhilipTreacy 
Not really then I have to open every file after downloading 😞 or modding and saving.
Hoped there is a better solution. Tried opening the file with Notepad++ but that didn't give me any information but garbage
Thanks for your help, very appreciated

[PhilipTreacy · Accepted solution]

yeah understood @CJW1960 , not surprising but worth asking.

Is there no way to alter the way the XLS files are produced to stop the superscript characters being used?  Or to export the data in a different format like CSV?

Phil

[CJW1960]

@PhilipTreacy Think I will have to ask the independent supplier of the free data as a last solution. He will probably ask me to take a subscription...
Cheers, Kees

[PhilipTreacy]

Hi @CJW1960 

Sorry, still can't reproduce the error. Have saved your example data into an XLS and imported just fine.

Maybe @edhans has some ideas.

Phil

[CJW1960]

Hi @PhilipTreacy ,
I could not reproduce either when copying an saving in an old Excel 5.0/95/97/2003 format. Also when editing the file(s) and saving in the same format made the column available again. Below a link to one of the xls involved:

Kees
https://drive.google.com/file/d/1iRvkkt1Ad1xBJluMjsfvjzzD9zJvxxDJ/view?usp=sharing

[edhans]

Can you share your workbook or the M code from your query @CJW1960 

I just tried to reproduce and it imports just fine. The 3 in my example is full sized of course for the column name.

The only thing I can think of that might be an issue is if you are using an XLS file format instead of XLSX. I know there are issues with formats in XLS workbooks that will cause Power Query connections to work incorrectly.

[PhilipTreacy]

hi @CJW1960 

Sorry I can't reproduce the issue. Maybe you can share your data and query?

Phil

[CJW1960]

Hi @edhans @PhilipTreacy ,
Thanks for your help, as I am new to this community, can one of you tell me how to share/upload within this topic?
Cheers

[edhans]

Hi @CJW1960 - please share data using the links below. Images/screen caps you can just paste in the browser. For sharing files, use OneDrive, DropBox, etc. with a public share link.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[CJW1960]

Hi @edhans ,

Thanks for the tip, below an example of the data stored in a xls file.
Kees

Tijdstip	levering totaal [m³]
19-10-2019	3,99
20-10-2019	3,77
21-10-2019	2,73
22-10-2019	2,37
23-10-2019	1,03
24-10-2019	1,08
25-10-2019	3,11
26-10-2019	1,03
27-10-2019	4,13
28-10-2019	4,39
29-10-2019	5,64
30-10-2019	4,56
31-10-2019	6,64

[edhans]

Sorry @CJW1960 - I cannot get it to fail either. The data you posted is not a 3 superscript format. it is the ASCII character 252 which is very different. However, I replaced it with a normal 3 and then applied the superscript format to it and nothing would fail. 

Your posting data to the forum is stripping something out. 

If you are really using an XLS workbook, save it as XLSX. Then see if it works. If it still fails, you will have to share the workbook with us to really test.

1. To protect data privacy, you should save it as a new file in the exact same format. (Excel 2003, Excel 2007-2020, XLSM, whatever format it is. Don't change that)
2. Delete all unnecessary sheets
3. Delete all data around the table so nothing is there but the table.
4. You should be able to delete all but the first row of the table.
5. You should be able to key in fake data to that first row.

Save it and see if it still fails. If it does, share the workbook via OneDrive, Dropbox, etc.

DO NOT copy the offending sheet to a new workbook and share that with us. That will strip out dozens of hidden things like styles, names, etc that might be the root of the issue. Do what I said above.

[edhans · Accepted solution]

Sorry @CJW1960 - didn't see you had attached it already. Yes, like @PhilipTreacy I get the error. There are twopossible ways to fix it.

1. Remove the superscript char
2. Convert the workbook to XLSX. I confirmed saving as XLSX fixes the issue.

This is the second confirmed issue I've seen with XLS formatting and Microsoft will not fix. I reported this to them earlier this year and the product team looked at it and basically went "Yup. You are right, it fails, but we aren't going to spend any time coding around an Excel format that was EOL'd in 2007." And I fully understand.

The only suggestion they offered was to install the Access 2010 Engine Redistributable. Links and more info here. That code actually has some stuff for XLS files. However, it did not fix my issue, so no promises here.

[CJW1960 · Accepted solution]

Thanks @edhans, tried the potential Access 2010 solution but as you said it did not fix the issue.
Think i will create a workaround opening all files and saving before closing using a macro.
Cheers, Kees

[edhans · Accepted solution]

Sounds good @CJW1960 - I'm doing the same thing with a file I get 2x per month from an outside provider.

Can you mark one/more of these posts as the solution so everyone will know the thread is solved and if they have the same/similar issue will know that saving the file as XLSX is the workaround.