Home

Import text file

%3CLINGO-SUB%20id%3D%22lingo-sub-84360%22%20slang%3D%22en-US%22%3EImport%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84360%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EMy%20Office%20365%20has%20recently%20updated%20with%20the%20new%20wizard%20for%20importing%20data.%20I%20work%20with%20a%20number%20of%20text%20files%20which%20I%20import%20into%20excel%20using%20the%20Text%20Import%20Wizard.%20I%20would%20insert%20Fixed%20width%20columns%20and%20manually%20create%20column%20breaks%20as%20the%20data%20does%20not%20suit%20the%20delimited%20option%2C%20this%20has%20worked%20sufficiently%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20aware%20that%20the%20old%20wizard%20can%20still%20be%20used%20to%20do%20this%20but%20I%20am%20wondering%20if%20there%20is%20a%20way%20to%20do%20this%20using%20the%20new%20wizard%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-84360%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319455%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319455%22%20slang%3D%22en-US%22%3EThanks%20%40Sergei%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-85177%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-85177%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ross%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20Text%20to%20Column%20actually%20uses%20the%20same%20engine%20as%20legacy%20wizard%2C%20just%20another%20entry%20into%20the%20it.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-85140%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-85140%22%20slang%3D%22en-US%22%3EA%20way%20to%20replicate%20the%20legacy%20wizard%20is%20to%20import%20the%20text%20file%20using%20the%20new%20wizard%20and%20formatting%20it%20to%20just%20one%20column%2C%20then%20use%20the%20Text%20to%20Columns%20feature%20to%20insert%20column%20breaks%20using%20mouse%20clicks.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-84433%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84433%22%20slang%3D%22en-US%22%3E%3CP%3ERoss%2C%20if%20you%20have%20some%20logic%20of%20how%20to%20split%20(e.g.%20based%20on%20width%20of%20the%20first%20three%20words%20in%20the%20column%20before%20the%20space%2C%20whatever)%20i%20guess%20it'll%20be%20possible%20to%20formalize%20it%20and%20automate.%20Query%20itself%20knows%20nothing%20about%20that%20logic%2C%20you%20have%20to%20define%20it%20somehow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepends%20on%20how%20complex%20is%20the%20logic%2C%20perhaps%20it%20could%20be%20possible%20to%20define%20query%20steps%20just%20from%20user%20interface%3B%20or%20some%20coding%20will%20be%20required.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-84430%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84430%22%20slang%3D%22en-US%22%3EThank%20you%2C%20that%20works%20perfectly%20for%20the%20files%20I%20know%20the%20column%20breaks%20for.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20the%20files%20I%20don't%20know%2C%20which%20have%20different%20widths%2C%20is%20there%20a%20way%20to%20easily%20identify%20the%20number%20of%20characters%20other%20than%20manually%20counting%20them%3F%20The%20lack%20of%20a%20ruler%2Findicator%20makes%20the%20process%20quite%20time%20consuming.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-84415%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84415%22%20slang%3D%22en-US%22%3E%3CP%3ERoss%2C%20in%20new%20wizard%20instead%20of%20click%26nbsp%3Bon%20Load%20after%20you%20connected%20the%20file%20use%20Edit.%20You'll%20be%20in%20Query%20Editor.%20In%20the%20Ribbon%20of%20the%20tool%20find%20Split%20Columns%20and%20here%20By%20Number%20of%20Characters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20choose%20form%20options%20to%20split%20on%20two%20columns%20or%20repeatedly%26nbsp%3Bby%20fixed%20width.%20If%20you%20have%20different%20widths%20you%20may%20split%20sequentially%26nbsp%3Beach%20next%20column%20or%20after%20first%20step%20in%20formula%20bar%20for%20this%20step%20modify%20it%20manually%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3D%20Table.SplitColumn(Source%2C%20%22Column1%22%2C%20Splitter.SplitTextByPositions(%7B0%2C%205%3CSTRONG%3E%2C%2010%2C%2012%3C%2FSTRONG%3E%7D%2C%20false)%2C%20%7B%22Column1.1%22%2C%20%22Column1.2%22%3CSTRONG%3E%2C%20%22Column1.3%22%2C%20%22Column1.4%22%3C%2FSTRONG%3E%7D)%3C%2FPRE%3E%3CP%3Eexpanding%20the%20list%20of%20positions%20where%20to%20split%20and%20adding%20columns%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESetting%20the%20columns%20by%20mouse%20clicks%20like%20in%20legacy%20wizard%20doesn't%20work%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-84403%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84403%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20the%20response%2C%20however%20I%20am%20wondering%20whether%20the%20same%20functionality%20is%20available%20in%20the%20new%20wizard.%20I%20have%20already%20enabled%20the%20legacy%20wizard%20but%20thought%20I%20could%20have%20missed%20something%20with%20the%20new%20wizard.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-84379%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20text%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-84379%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ross%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20File-%26gt%3BOptions-%26gt%3BData%20enable%20%22From%20Text%20(legacy)%22%20wizard%20and%20after%20that%20you%26nbsp%3Bmay%20use%20it%20from%20Ribbon%20menu%20Data%20section%3C%2FP%3E%3CP%3EGet%20Data-%26gt%3BLegacy%20wizards.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20yes%2C%20you%20may%20use%20new%20wizard%2C%20it's%20much%20more%20powerful.%20Check%20Power%20Query%20or%20Get%20%26amp%3B%20Transform%20for%20more%20details.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ross Evans
New Contributor

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?

8 Replies

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.

Hi 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.

 

 

Thank 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.

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. 

A way to replicate the legacy wizard is to import the text file using the new wizard and formatting it to just one column, then use the Text to Columns feature to insert column breaks using mouse clicks.

Hi Ross,

 

Yes, Text to Column actually uses the same engine as legacy wizard, just another entry into the it. 

Thanks @Sergei