Microsoft quietly replaced the comfortable Text Import Wizard from Excel and replaced it with the “Get & Transform” tools. The “Get & Transform” tools offer a lot of options and are very powerful. Unfortunately, they are quite complicated to use. Here is what you should now.
In a hurry? Click on “File” –> “Options” –> “Data” and set the corresponding checkmarks for reactivating the “Text Import Wizard” in Excel. Start the text import by clicking on “Data” –>”Get Data” –> “Legacy Wizards” –> “From Text (Legacy)”.
Introduction
In Excel 365 (only) 2016 (since version 1704) the “Text Import Wizard” was removed. It was replaced by the powerful “Get & Transform” tools. The “Get & Transform” tools also provide a function to import text and CSV files into Excel.
You have the following two options:
- Luckily, the comfortably “Text Import Wizard” still exists. You can re-activate and use it for importing text and csv files into Excel.
- Use the import function of the “Get & Transform” tools.
Restore the “Text Import Wizard”
The good news: You can easily restore the “Text Import Wizard”. Unfortunately, the option for re-activating them is hidden.
Follow these steps:
- Click on File and then on “Options”. Go to “Data” on the left-hand side.
- In the lower section of the window you can select the wizard you’d like to restore. For only importing text- or csv-files, select “From Text (Legacy)”. Feel free to also activate the corresponding wizard for importing Access files, files from web, from SQL servers and so on.
- Confirm with OK.
Now, you can find the so-called “Legacy Wizards” in the “Get Data” drop-down menu. In order to use them, follow these steps:
- Go to the “Data” ribbon.
- Click on “Get Data” on the left-hand side.
- Next, go to “Legacy Wizards”.
- Click on “From Text (Legacy)”.
How to use the “Text Import Wizard”
The steps for using the “Text Import Wizard” in Excel are shown in the screenshots.
- Go to the “Data” ribbon and click on “From Text”. If you have a recent Excel version and there is no button called “From Text” (but instead “From Text/CSV”), click on “Get Data”, then on “Legacy Wizards” and then on “From Text (Legacy)”. Please refer to the paragraph above if this option is missing.
- Select how you want to define the columns: Either with a character as a separator or with a fixed width.
- If the first row contains headers, check the corresponding box.
- Continue with “Next >”.
- Select the delimiter. This is the character dividing the data into columns, for example “Tab”, “Semicolon” or “Comma”.
- Usually text fields use quotation marks marking the beginning and end of a text field.
- For each column, you can choose the data format. For dates, you could define the order of days, months and years.
- Click on “Advanced”…
- …for defining decimals and thousands separators.
- Finalize the import by clicking on “Finish”.
Import text and csv files with the “Get & Transform” tools
Importing text files in Excel with the “Get & Transform” tools requires many steps. Please refer to the numbers on the screenshots:
- Click on “From Text/CSV” on the “Data” ribbon in order to start the import process.
- Choose the delimiter (e.g. semi-colon, comma etc.). Here you can also switch to “Fixed Width”. If you want to separate your import data with the “Fixed Width” option, you have to type the numbers of characters, after which you want to data to be divided.
- For further options (e.g. switching thousands- and decimal separators) click on “Edit”.
- If you data is not represented correctly, delete the automatically created step “Changed Type”.
- Change the date format: Right-click on a column that contains a date. Alternatively click on the small “ABC” symbol in the top left corner of the column heading.
- Move the mouse to “Change Type”.
- Click on “Using Locale…”.
- Select “Date”.
- Select the locale format for dates. In this example, the German date format is used.
- Confirm with OK. Repeat the steps 5 to 10 for each date column.
Recommendation: Select several date columns at the same time by pressing and holding the Ctrl key while selecting the columns. - Change the decimal and thousand separators: Right-click again on a column with decimal numbers.
- Move the mouse to “Change Type”.
- Click on “Using Local…”.
- Choose “Decimal Number”.
- Select the local number format. Please refer to this article for a list of local number formats.
- Confirm with “OK”.
- Last step: Insert the data into a worksheet. In order to achieve this, click on “Close & Load” in the top-left corner.
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.
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,
Henrik
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!
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.
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.
Thanks!
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,
Henrik
THANK YOU THANK YOU THANK YOU!!!! IT WAS SOOOOOOOOOOOOO ANNOYING THAT THEY REMOVED THIS WIZARD!
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.
Hi Ron,
Thanks for the correction. I’ve changed the introduction as you suggested.
Best regards,
Henrik
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
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?
Thank you so much sir. Your article was of great help. Couldn’t have solved this problem without you. Much appreciated.
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.
I’d been looking for this solution for months. Thank you so much.
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
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.
Suggestions?
Sorry, I haven’t experienced this before. What happens if you only enable “From Text (Legacy)”?
THanks. WE recently changed to Office 365 and notice we lost the wizard option our ADMINs almost blew a fuse!! Thanks a lot.
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!
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… 🙂
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.
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?
Thank you very much. This is just the information I have been searching for on re-enabling Import Wizard.
You’re welcome 🙂
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.
-AR
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?
You saved my life. Thank you, man!
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.
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?
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!
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 😉 ).
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?
Thank you for this! How frustrating that they made importing from a .txt file so complicated!
Much Appreciated
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.
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!
In Excel 2016, there is no “Data” selection under Excel options. The Office dev team needs to be replaced.
Microsoft seems very efficient sometimes in destroying intuitive things… doesn’t matter how much powerful the new tools are, I was just caught suddenly by this new tool and couldn’t find what I wanted… the old wizard interface was very intuitive, you would understand what was going on right away in the first use….. of course advanced options are needed and welcome, but intuitive access to everyday needs is a virtue that is being destroyed…..
To put it in another terms… I used the old Wizard interface correctly when I was a young teenager using computers for the first time, no knowledge of math, no knowledge of computers whatsoever… I just opened and it made sense….. Now, working with programming, engineer… I opened this new tool for the first time and couldn’t immediately do the most stupid input datatype change for Date text without the help of some internet tutorials to find the hidden options…. (actually, I read it now, because I lost patience looking at that crazily stupid interface and just did the change I wanted directly on the original text file using R). It doesn’t matter how many powerful tools the new interface has… the destruction of the intuitiveness of the old Wizard interface is way beyond belief. They developed a tool very intuitive for those who developed it, but this is the wrong concept of how it should work…
Yes, Adriano, fully agree. Although I’m a big fan of PowerQuery – a fast and simple import doesn’t seem possible. Simple things like importing a CSV file with swapped thousands- and decimal-separators needs a big effort now.
Thank you for this excellent article without which I would have spent many hours trying to work out how to carry out an operation that is simplicity itself using the legacy application.
This is one of those cases where the phrase ‘if it ain’t broken, don’t try to fix it’ is very apt.
Thanks – I totally agree!
Is there a way to always open de legacy text import wizard when opening a csv file from windows file explorer?
Im on windows 10 excel 365 and it just opens the cvs as plain text so a I to select all data and use split coloumns.
Thanks
Hi Ricardo,
Yes, just use it from within Excel. So, don’t double-click on the CSV-file to open it but go to the Data ribbon and click on Get Data on the left. Then click on Legacy Wizards and on “From Text (Legacy)”.
Best regards,
Henrik
I’m confused now…I had found that way to bring back the Legacy Wizards and use the resulting connections to import data on a daily basis. I am now trying to add more text files and the Legacy Wizard option is greyed out. Checked and those options are still checked in the options menu. I’m using MS Office 365 ProPlus Version 1910 (Build 12130.20184 Click-to-Run. Did I lose Legacy Wizards usage in some kind of update?
Thank you for the short and to the point path on how to get this to work again! can’t thank you enough, really!!
When i am opening a csv file formatted with custom separator in Excel, i have to:
– unzip the file
– rename it to something else (like .xxx)
– open excel manually, click on open, locate the folder and file, load the file, and then i get the import wizard.
With Open office software you have just to doubleclick to open the file and the software opens automatically and also the import wizard opens automatically.
Excel works like a charm!!!!!
It does not work in Office 365. The Legacy Wizzard is greyed out regardless of how many options are enabled.
Fantastic! Glad someone puts in the effort to share information and counter MS’ ongoing efforts to make us less productive.