Jul 03 2017
04:50 AM
- last edited on
Jul 12 2019
10:47 AM
by
TechCommunityAP
Jul 03 2017
04:50 AM
- last edited on
Jul 12 2019
10:47 AM
by
TechCommunityAP
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?
Jul 03 2017 06:10 AM - edited Jul 03 2017 06:12 AM
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.
Jul 03 2017 06:55 AM
Jul 03 2017 07:17 AM
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.
Jul 03 2017 08:09 AM
Jul 03 2017 08:25 AM
Ross, if you have some logic of how to split (e.g. based on width of the first three words in the column before the space, whatever) i guess it'll be possible to formalize it and automate. Query itself knows nothing about that logic, you have to define it somehow.
Depends on how complex is the logic, perhaps it could be possible to define query steps just from user interface; or some coding will be required.
Jul 06 2017 06:08 AM
Jul 06 2017 08:02 AM
Hi Ross,
Yes, Text to Column actually uses the same engine as legacy wizard, just another entry into the it.