Forum Discussion

CJW1960's avatar
CJW1960
Frequent Visitor
5 years ago
Solved

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

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 clicki...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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's avatar
    edhans
    5 years ago

    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's avatar
    CJW1960
    5 years ago

    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's avatar
    edhans
    5 years ago

    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.

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

hi CJW1960 

 

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

 

Phil

CJW1960's avatar
CJW1960
Frequent Visitor
5 years ago

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

    • CJW1960's avatar
      CJW1960
      Frequent Visitor
      5 years ago

      Hi edhans ,

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

      Tijdstiplevering totaal [m³]
      19-10-20193,99
      20-10-20193,77
      21-10-20192,73
      22-10-20192,37
      23-10-20191,03
      24-10-20191,08
      25-10-20193,11
      26-10-20191,03
      27-10-20194,13
      28-10-20194,39
      29-10-20195,64
      30-10-20194,56
      31-10-20196,64

       

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        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.