Forum Discussion
Import text file
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.
- Ross EvansJul 03, 2017Copper ContributorThank you, that works perfectly for the files I know the column breaks for.
For the files I don't know, which have different widths, is there a way to easily identify the number of characters other than manually counting them? The lack of a ruler/indicator makes the process quite time consuming.- SergeiBaklanJul 03, 2017Diamond Contributor
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.
- joenmarzJan 16, 2019Copper ContributorThanks @Sergei