Professor Excel

Comments 36

  1. Sander

    Very nice!

    We work with a system that spits out delimited text-files with Excel formulas.

    The good-old “Text Import Wizard” has a “default” type for columns. When using this type, Excel will interpret formulas in CSV/Text files. As far as I can see, this is not possible with “Get & Transform”. Therefore, to me, the old wizard is way more powerful than the new “Get & Transform” wizard.

    I hope Microsoft will restore the old behaviour in the new wizard before removing the old one permanently.

    If anyone has any ideas on how to import delimited text files containing formulas with the new wizard, I’m all ears.

    • Henrik Schiffner

      Hi Sander,
      Let’s hope, Microsoft doesn’t remove it permanently. But could you send me an example of your import file (just 2-3 rows are enough, please don’t send any confidential data) to me, so that I could try it myself?
      Thanks and best regards,

  2. Chuck

    Thank you so much! I didn’t know the wizard was going away. Good to know how to get it back when I lose it.

    I was looking for a way to change the default selections in the wizard. On the “Step 2 of 3”, delimiter is always Space, and I always, always, always use Comma. Text qualifier is always Quote, and I always, always, always use Apostrophe.

    Is there a way to make the wizard remember my selections from the last time I used it? Is there something I can twiddle in the registry to set “my” selections as the default?

    Thanks again!

  3. Chuck

    Dur! I just realized that I have lost the Text Import Wizard, but not the way I use it. This might be helpful to your readers.

    I use a SQL query tool and “copy rows.” This puts a CSV-like structure into my clipboard. When I have this “CSV” in the clipboard, Excel gives me the option Paste/Use Text Import Wizard.

    You could get this same access by, say, opening your CSV file in Notepad or something like it, select all text, copy, then go to Excel and use the good old Text Import Wizard.

    Again, thank you for the great information.

  4. Melissa

    Do you know how to bring back the legacy wizard on Excel for Mac? I’ve searched everywhere for similar options, but can’t find them. Logically, they’d be under Excel->Preferences, but I don’t see them there or elsewhere. I could be missing something though. The new version takes considerably longer with many more steps involved to do what I used to be able to do quickly with a couple of clicks. This might be the point where I dump Excel and try Numbers.

    • Henrik Schiffner

      Hi Melissa,
      I use Excel version 15.37 on Mac and I guess it’s the latest version right now. If you try to import a text file by going to Data –> From Text it still opens the original Text Import Wizard. Maybe that’s why you can’t find the legacy wizard because there is no new one on Mac?
      Best regards,

  5. Al


  6. Ron MVP

    Your intro should be tweaked. Instead of starting:
    “In Excel 2016 (since version 1704) …”
    it should read more like
    “In Excel 365 (only) 2016 (since version 1704) …”

    This “improvement’ is one of the “new features” limited to 365 subscriptions ONLY. This nuance of documentation is something that most people ignore. I have one-time payment Pro Plus, currently at Version 1707 Build 8326.2096 but I don’t have this new feature.

    This difference is precisely what I hate about this new marketing “scam” being used to “encourage” people to switch to the 365 subscription license scheme.

    I was trying to give someone help, but we were not working with the same user interface. I wasted an hour doing screen caps, only to have them find this article and point out my mistake.

    • Henrik Schiffner

      Hi Ron,
      Thanks for the correction. I’ve changed the introduction as you suggested.
      Best regards,

  7. Batu

    Thanks a lot.
    I couldnt seperate a txt file using two different seperater. For example i want to use “/” as seperator for date and “,” for rest of data. Txt import wizard is allowed this easily but “Get & Transform” tool not or i cant.
    Thanks again

  8. Jack

    In the legacy wizard we had the option to overwrite data. Is there a way to do this with the new tool?
    We also had the option to not have the data imported as a table but as a range.
    I can not find a way to do this with the new tool and it causes excel t o create new columns to the left preventing the overwrite. I could change to a range after the import but them have to cut and paste to relocate to proper position. I then have to delete the cells created by the import porcess.
    Any ideas?

  9. Shivani

    Thank you so much sir. Your article was of great help. Couldn’t have solved this problem without you. Much appreciated.

  10. Michael

    My import text and csv files with the “Get & Transform” tool does not show the drop down lists as per your example step 2. Really threw me for a loop. Is there a setting I need to turn on to see these? I am trying to import a text filee that has several spaces between the data items. The fixed width approach works great in the old wizard but I cannot figure out how to this with the new tools.

  11. Tom B.

    I’d been looking for this solution for months. Thank you so much.

  12. Liz

    With “Get & Transform” tools, after “From Text/CSV”, if the Delimiter option is not displayed and all data is in one column, click on Edit and on the Home Tab, select Split Column, select By Delimiter and select the appropriate delimiter there

  13. Chris Paul

    I have followed the instructions to enable the Text Wizard (Legacy): Options, Data, From Text (Legacy) (check the box) and From Access Legacy (Check the box).

    When I the select Data, Get Data, Legacy Wizards, I see only From Access (Legacy), and not Text.


    • Henrik Schiffner

      Sorry, I haven’t experienced this before. What happens if you only enable “From Text (Legacy)”?

  14. Dwayne Vance

    THanks. WE recently changed to Office 365 and notice we lost the wizard option our ADMINs almost blew a fuse!! Thanks a lot.

  15. Michael Stimac

    The advice on how to Restore the “Text Import Wizard” was PRICELESS.
    The version of this utility in the Office 365 version of Excel, on the other hand,
    is utterly useless.

    Thank you, Thank you, Thank you!

    • Henrik Schiffner

      Thanks for the feedback. Actually it took me some time as well to figure it out. Especially because I didn’t find much about it online.
      But to be fair: Microsoft highlighted this new “feature” the first time I used it with a help link. But I guess I’ve just clicked it away… 🙂

  16. Adam Birchfield

    This is so much easier. Why would they hide this. I wasted all day trying to do this with their new system and it took a few min with the text wizard.

  17. rspiet

    How do I import a number as text complete with leading zeros with Get and Transform? We have a 5 digit clock number. Clock numbers like 00016 or 01616 should import as is and not be imported as 16 or 1616?

  18. Tony Lau

    Thank you very much. This is just the information I have been searching for on re-enabling Import Wizard.

  19. AR

    The new import tool would work for me but is there really not a way to specify the text qualifiers? Maybe I’m missing it but if not this seems like a major oversight. I did find that you can go into the query editor, select each column individually, go to the Transform Ribbon | Extract | Text Between Delimiters then enter a ‘ in the start and end delimiter. That seems like a lot of work for a feature that was just one checkbox that used to be checked by default.


    • Henrik Schiffner

      You could use the localisation option and therefore indirectly define the text qualifiers. Maybe you could add a step to search & replace the text qualifier?

  20. Robert

    There is no Data section under File -> Options. I’m using Excel 2016 Version 16.0.4639.1000. So… I guess there’s no way to bring the wizard back. Disappointing.

    • Henrik Schiffner

      There is, just checked (although I’m running the current version). Under File -> Options, it’s the third “tab” on the left-hand side. If it’s not there I’d recommend restarting the computer and if it’s still missing un- and reinstalling Office. Does that help?

  21. Sean G

    Henrik, thank you for posting this very useful information.

    I wanted to add some useful information to this discussion
    If you attempt to load a text file with differing quantities of data fields within each line using the new “From Text/CSV” function from the Get & Transform Data group, there is a problem.
    After a file is selected, the window showing the first lines of the file with the drop-downs “File Origin”, “Delimiter”, and “Data Type Detection” will not appear as shown in the above example.
    Only the “File Origin” dropdown appears, while the “Delimiter” and “Data Type Detection” dropdowns will not be shown. If the file is corrected to make all the lines in the file have the same quantity of data fields, then these latter two dropdowns will appear.

    The legacy import wizard doesn’t appear to use the same approach to interpret how to delimit the data fields in a file, and so doesn’t appear to “fail” in this way when an inconsistent data file is imported.

    Just wanted to save others from the same difficulties I’ve experienced!

  22. Michelle

    Thank goodness. I use Get & Transform for other things just fine, but I was trying to import a fixed width file and Get & Transform got the columns wrong with no obvious way to fix them. The legacy wizard did exactly what I wanted quickly and easily (which I knew it would since I’m quite familiar with it 😉 ).

  23. Philippe

    By default, my Excel 365 is set to apply all line breaks. Every time I upload a csv file into Excel, I have to go in the Power Query Editor modify the “Source” parameters of the “Query settings” and change the “Line breaks” parameter from “Apply all line breaks” to “Ignore quoted line breaks”.
    Is it possible to save the configuration once for all?

  24. Mason

    Thank you for this! How frustrating that they made importing from a .txt file so complicated!

    Much Appreciated

  25. Jeff Wiebe

    The removal of the legacy wizard is another wretched Microsoft blunder. They earn legitimate accolades for excellent innovations, and then they earn legitimate wrath for taking them away. The new ‘powerful’ data options that replaced the legacy wizard are horrendously cumbersome where the wizard was immediately effective.
    Further, in my company’s Excel 2016 the helpful steps in this blog post are inapplicable. There are none of the options discussed. That’s not your fault, but is another kick in the pants from Microsoft in their ‘oodles of versions, each with some but not all of the features you want, unavoidably bundled with many of those you don’t’ marketing philosophy.
    I wish they’d just have the policy that anything that worked in the past would just be available, without deep-dive option enabling, etc. It’s like they don’t want to keep getting better, they just want to stay exactly as they are, so when they come up with something new, their practice is: if it is a useful innovation, take something else useful away; if it is a useless innovation, aw heck, take something useful away, too.

  26. JL

    But how do to do this with Excel Office 365 “online”? I can’t find ANYTHING ANYWHERE about saving an Excel file (on top of that converted from a CSV file) as a text file. Thanks!

  27. RTB

    In Excel 2016, there is no “Data” selection under Excel options. The Office dev team needs to be replaced.

Leave a comment

%d bloggers like this: