Forum Discussion
Ross Evans
Jul 03, 2017Copper Contributor
Import text file
Hi all,
My Office 365 has recently updated with the new wizard for importing data. I work with a number of text files which I import into excel using the Text Import Wizard. I would insert Fixed width columns and manually create column breaks as the data does not suit the delimited option, this has worked sufficiently for me.
I am aware that the old wizard can still be used to do this but I am wondering if there is a way to do this using the new wizard?
Hi Ross,
In File->Options->Data enable "From Text (legacy)" wizard and after that you may use it from Ribbon menu Data section
Get Data->Legacy wizards.
And yes, you may use new wizard, it's much more powerful. Check Power Query or Get & Transform for more details.
- Ross EvansCopper ContributorHi Sergei,
Thank you for the response, however I am wondering whether the same functionality is available in the new wizard. I have already enabled the legacy wizard but thought I could have missed something with the new wizard.Ross, in new wizard instead of click on Load after you connected the file use Edit. You'll be in Query Editor. In the Ribbon of the tool find Split Columns and here By Number of Characters.
You may choose form options to split on two columns or repeatedly by fixed width. If you have different widths you may split sequentially each next column or after first step in formula bar for this step modify it manually like
= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 5, 10, 12}, false), {"Column1.1", "Column1.2", "Column1.3", "Column1.4"})
expanding the list of positions where to split and adding columns name.
Setting the columns by mouse clicks like in legacy wizard doesn't work here.