SOLVED

Need to convert a text file to Excel - not the typical comma delineated file

%3CLINGO-SUB%20id%3D%22lingo-sub-1527657%22%20slang%3D%22en-US%22%3ENeed%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1527657%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20text%20file%20that%20needs%20to%20be%20converted%20to%20Excel%20however%20the%20text%20is%20grouped%20in%20a%20fashion%20that%20Excel%20doesn't%20not%20convert%20into%20columns%20but%20exactly%20as%20it%20appears%20in%20the%20text%20file.%26nbsp%3B%20The%20text%20content%20looks%20like%20this.%26nbsp%3B%20I%20added%20an%20underscore%20before%20'name'%20to%20differenciate%20each%20group%20of%20text.%26nbsp%3B%20How%20can%20I%20import%20to%20Excel%20where%20the%20text%20before%20each%20%3A%20becomes%20the%20column%20heading%3F%3C%2FP%3E%3CP%3E_name%3A%20All%20Company%3CBR%20%2F%3Eid%3A%200%3CBR%20%2F%3Etype%3A%20internal%3CBR%20%2F%3Eprivacy_setting%3A%20public%3CBR%20%2F%3Estate%3A%20active%3CBR%20%2F%3Eapproximate_messages_count%3A%206%3CBR%20%2F%3Elast_message_at%3A%202014-06-12%2009%3A52%3A27.259000062%20%2B00%3A00%3CBR%20%2F%3Eo365_connected%3A%20false%3CBR%20%2F%3Egroup_admins%3A%20All%20Yammer%20Verified%20admins%3CBR%20%2F%3Emember_counts%3A%3CBR%20%2F%3Einternal%3A%2024565%3CBR%20%2F%3Eexternal%3A%200%3CBR%20%2F%3Euploaded_file_counts%3A%3CBR%20%2F%3Eyammer_files%3A%200%3CBR%20%2F%3Esharepoint_files%3A%200%3CBR%20%2F%3E66069%3A%3CBR%20%2F%3E__name%3A%20Marine%20Matter%3CBR%20%2F%3Eid%3A%2066069%3CBR%20%2F%3Etype%3A%20internal%3CBR%20%2F%3Eprivacy_setting%3A%20public%3CBR%20%2F%3Estate%3A%20deleted%3CBR%20%2F%3Eapproximate_messages_count%3A%2070%3CBR%20%2F%3Elast_message_at%3A%202015-10-30%2008%3A31%3A43.069999000%20Z%3CBR%20%2F%3Eo365_connected%3A%20false%3CBR%20%2F%3Egroup_admins%3A%3CBR%20%2F%3Ewith_o365_creation_rights%3A%20%5B%5D%3CBR%20%2F%3Ewithout_o365_creation_rights%3A%20%5B%5D%3CBR%20%2F%3Emember_counts%3A%3CBR%20%2F%3Einternal%3A%200%3CBR%20%2F%3Eexternal%3A%200%3CBR%20%2F%3Euploaded_file_counts%3A%3CBR%20%2F%3Eyammer_files%3A%200%3CBR%20%2F%3Esharepoint_files%3A%200%3CBR%20%2F%3E89528%3A%3CBR%20%2F%3E_name%3A%20Summers%20Lab%3CBR%20%2F%3Eid%3A%2089528%3CBR%20%2F%3Etype%3A%20internal%3CBR%20%2F%3Eprivacy_setting%3A%20public%3CBR%20%2F%3Estate%3A%20deleted%3CBR%20%2F%3Eapproximate_messages_count%3A%2010%3CBR%20%2F%3Elast_message_at%3A%202012-10-01%2011%3A00%3A22.418401000%20Z%3CBR%20%2F%3Eo365_connected%3A%20false%3CBR%20%2F%3Egroup_admins%3A%3CBR%20%2F%3Ewith_o365_creation_rights%3A%20%5B%5D%3CBR%20%2F%3Ewithout_o365_creation_rights%3A%20%5B%5D%3CBR%20%2F%3Emember_counts%3A%3CBR%20%2F%3Einternal%3A%200%3CBR%20%2F%3Eexternal%3A%200%3CBR%20%2F%3Euploaded_file_counts%3A%3CBR%20%2F%3Eyammer_files%3A%200%3CBR%20%2F%3Esharepoint_files%3A%200%3CBR%20%2F%3E89758%3A%3CBR%20%2F%3E_name%3A%20Pilot%20Group%3CBR%20%2F%3Eid%3A%2089758%3CBR%20%2F%3Etype%3A%20internal%3CBR%20%2F%3Eprivacy_setting%3A%20public%3CBR%20%2F%3Estate%3A%20active%3CBR%20%2F%3Eapproximate_messages_count%3A%2016%3CBR%20%2F%3Elast_message_at%3A%202011-07-04%2009%3A46%3A24.989397000%20Z%3CBR%20%2F%3Eo365_connected%3A%20false%3CBR%20%2F%3Egroup_admins%3A%3CBR%20%2F%3Ewith_o365_creation_rights%3A%20%5B%5D%3CBR%20%2F%3Ewithout_o365_creation_rights%3A%20%5B%5D%3CBR%20%2F%3Emember_counts%3A%3CBR%20%2F%3Einternal%3A%200%3CBR%20%2F%3Eexternal%3A%200%3CBR%20%2F%3Euploaded_file_counts%3A%3CBR%20%2F%3Eyammer_files%3A%201%3CBR%20%2F%3Esharepoint_files%3A%200%3CBR%20%2F%3E90435%3A%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1527657%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1527987%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1527987%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%2C%20I%20used%20Power%20Query%20to%20accomplish%20this.%20I%20copied%20your%20sample%20into%20a%20text%20file%20(attached).%20I%20changed%20the%20delimiter%20from%20%22%3A%22%20to%20%22%3B%22%20since%20there%20were%20dates%20in%20the%20data%20that%20used%20%22%3A%22.%20To%20use%20this%20change%20the%20location%20of%20the%20source%20text%20file%20in%20the%20source%20table.%20The%20processed%20data%20will%20be%20in%20the%20Output%20tab.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1529206%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1529206%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3BThank%20you.%26nbsp%3B%20I'm%20not%20familiar%20with%20power%20queries...so%20I'm%20stuck%20on%20this%20part%2C%20%22%3CSPAN%3Echange%20the%20location%20of%20the%20source%20text%20file%20in%20the%20source%20table.%3C%2FSPAN%3E%22%26nbsp%3B%20Where%20do%20I%20change%20this%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EMB%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1529591%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1529591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%20attached.%3C%2FP%3E%0A%3CP%3EFilenamePath%20is%20in%20the%20named%20cell%20within%20the%20sheet%2C%20same%20as%20in%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530484%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3E%26nbsp%3BDownload%20the%202%20files%20I%20uploaded%20to%20your%20local%20drive%3C%2FLI%3E%3CLI%3EOpen%20the%20Excel%20file%20-%20%22Test%20Power%20Query.xlsx%22%3C%2FLI%3E%3CLI%3EIt%20will%20open%20up%20in%20the%20tab%20named%20Source%3C%2FLI%3E%3CLI%3EChange%20the%20File%20Path%20in%20the%20cell%20A2%20from%20%22C%3A%5CUsers%5Cdgant%5CDownloads%5Ctest.csv%22%20to%20the%20file%20path%20of%20the%20test.csv%20file%20that%20you%20down%20loaded.%26nbsp%3B%3C%2FLI%3E%3CLI%3EGo%20to%20the%20tab%20named%20%22Output%22%20that%20shows%20you%20the%20output%20of%20PQ%3C%2FLI%3E%3CLI%3ESave%20the%20xlsx%20file%3C%2FLI%3E%3CLI%3EOpen%20test.csv%20and%20add%20more%20records%20(making%20the%20appropriate%20changes%20I%20mentioned%20-%20using%20%3B%20as%20the%20delimiter%20instead%20of%20%3A_%3C%2FLI%3E%3CLI%3ESave%20test.csv%3C%2FLI%3E%3CLI%3EHit%20Data-%26gt%3BRefresh%20All%20in%20the%20Excel%20file%20to%20see%20the%20output%20table%20updated%20with%20the%20info%20you%20put%20in%20the%20csv%20file%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20you%20can%20use%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B's%20solution.%20Let%20us%20know%20how%20it%20goes.%3C%2FP%3E%3CP%3E%26nbsp%3B%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-1536749%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3BFirst%20thank%20you%20very%20much%20for%20the%20assistance.%26nbsp%3B%20My%20apologies%20for%20being%20so%20thick......how%20did%20you%20change%20the%20delimiter%20from%20%3A%20to%20%3B%20in%20the%20text%20file%3F%20My%20original%20file%20has%2038000%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3EMB%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1536877%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1536877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20it's%20not%20necessary%20to%20change%20delimiters%2C%20in%20general%20it's%20not%20necessary%20to%20make%20any%20changes%20in%20your%20text%20file.%20For%20the%20delimiters%20you%20may%20use%20split%20only%20on%20left%20most%20delimiter%2C%20that%20won't%20affect%20the%20dates.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVariant%20of%20layout%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%20suggested%20is%20in%20TextTransformTwo%20query%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539011%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539011%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20import%20data%20from%20a%20text%20file%20into%20an%20existing%20worksheet.%3C%2FP%3E%3CP%3EOn%20the%20Data%20tab%2C%20in%20the%20Get%20%26amp%3B%20Transform%20Data%20group%2C%20click%20From%20Text%2FCSV.%3C%2FP%3E%3CP%3EIn%20the%20Import%20Data%20dialog%20box%2C%20locate%20and%20double-click%20the%20text%20file%20that%20you%20want%20to%20import%2C%20and%20click%20Import.%3C%2FP%3E%3CP%3EIn%20the%20preview%20dialog%20box%2C%20you%20have%20several%20options%3A%3C%2FP%3E%3CP%3ESelect%20Load%20if%20you%20want%20to%20load%20the%20data%20directly%20to%20a%20new%20worksheet.%3C%2FP%3E%3CP%3EAlternatively%2C%20select%20Load%20to%20if%20you%20want%20to%20load%20the%20data%20to%20a%20table%2C%20PivotTable%2FPivotChart%2C%20an%20existing%2Fnew%20Excel%20worksheet%2C%20or%20simply%20create%20a%20connection.%20You%20also%20have%20the%20choice%20of%20adding%20your%20data%20to%20the%20Data%20Model.%3C%2FP%3E%3CP%3ESelect%20Transform%20Data%20if%20you%20want%20to%20load%20the%20data%20to%20Power%20Query%2C%20and%20edit%20it%20before%20bringing%20it%20to%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540945%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F378366%22%20target%3D%22_blank%22%3E%40Smith_J%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20the%20input.%26nbsp%3B%20This%20is%20the%20process%20I%20started%20with%20-%20the%20issue%20I'm%20having%20is%20understanding%20how%20to%20manipulate%20the%20data%20in%20power%20query%26nbsp%3Binto%20multiple%20columns%20instead%20of%20one%20column.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541154%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541154%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20ask%20if%20any%20concrete%20questions%20while%20you%20check%20the%20query%20step%20by%20step.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541691%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Band%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B-%20thank%20you%20for%20the%20assistance%20on%20my%20first%20request%20for%20help.%26nbsp%3B%20I%20was%20able%20to%20get%20the%20data%20per%20Sergei%20Baklan's%20instructions%20%26amp%3B%20files.%3C%2FP%3E%3CP%3EI%20have%20another%20file%20that%20needs%20to%20be%20extracted%20from%20one%20column%20to%20multiple%20columns%20-%20for%20my%20yammer%20users%20which%20I've%20attached%20here.%3C%2FP%3E%3CP%3EI%20tried%20dissecting%20your%20files%20to%20understand%20the%20queries%20but%20that%20was%20a%20fail.%26nbsp%3B%20I%20would%20be%20grateful%20for%20your%20assistance%20again.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544528%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20bit%20another%20logic%20of%20this%20file.%20What%20to%20do%3C%2FP%3E%0A%3CP%3E-%20since%20now%20you%20have%20commas%20within%20texts%2C%20ignore%20them%20as%20default%20delimiter.%20That%20could%20be%20done%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3D%20Csv.Document(File.Contents(filePathName)%2C%7B%22Column1%22%7D%2C%7B0%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20says%20we%20split%20on%20one%20Column1%20starting%20from%20position%200.%20Other%20words%2C%20no%20split.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20transforming%20column%20we%20select%20as%20names%20not%20only%20field%20starts%20by%20_%2C%20but%20also%20equal%20to%20%3CEM%3Ename%3C%2FEM%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3ETable.AddColumn(Source%2C%20%22Names%22%2C%20each%20if%20Text.StartsWith(%5BColumn1.1%5D%2C%22_%22)%20or%20%5BColumn1.1%5D%20%3D%20%22name%22%20then%20%5BColumn1.2%5D%20else%20null)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20filter%20them%20as%20other%20fields%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3D%20Table.SelectRows(%23%22Filled%20Down%22%2C%20each%20not%20(%20Text.StartsWith(%5BColumn1.1%5D%2C%20%22_%22)%20or%20%5BColumn1.1%5D%3D%22name%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20since%20field%20%3CEM%3Erole%3C%2FEM%3E%26nbsp%3Bcould%20be%20repeated%20for%20same%20person%2C%20we%20created%20in%20the%20middle%20intermediate%20table%20with%20this%20field%20only%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20541px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207706iF5C0878984026A6E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Egroup%20it%20by%20names%20and%20field%20(Column1.1)%2C%20extract%20list%20with%20roles%20and%20convert%20it%20to%20text%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20551px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207708i4F891248AC48B3F2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eafter%20filter%20field%20%3CEM%3Erole%3C%2FEM%3E%20from%20main%20table%20and%20append%20to%20it%20above%20one%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20datetime%20text%20here%20is%20without%20separation%20of%20date%20and%20time%20in%20the%20text.%20Thus%20we%20remove%20comma%20on%20the%20end%20and%20insert%20T%20between%20date%20and%20time%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3E%3D%20Table.ReplaceValue(%23%22Extracted%20Text%20Before%20Delimiter%22%2Ceach%20%5Blast_date_accessed%5D%2C%20each%20Text.Insert(%5Blast_date_accessed%5D%2C10%2C%22T%22)%2CReplacer.ReplaceText%2C%7B%22last_date_accessed%22%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Econvert%20now%20this%20text%20on%20actual%20datetime.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-%20if%20in%20another%20file%20there%20is%20no%20field%20last_date_accessed%20(as%20in%20initial%20one)%20error%20appears.%20If%20so%20correct%20on%20proper%20field%20name%20or%20remove%20the%20step.%20That%20could%20be%20handled%20automatically%2C%20but%20better%20to%20know%20all%20possible%20datetime%20field%20names.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548190%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20YOU%20are%20a%20life%20saver!%26nbsp%3B%20Thank%20you%20so%20much%20for%20the%20detailed%20explanation%20and%20the%20file.%26nbsp%3B%20I'll%20work%20with%20this%20to%20better%20understand%20these%20queries.%3C%2FP%3E%3CP%3EI%20greatly%20appreciate%20your%20time%20%26amp%3B%20expertise!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1548697%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20convert%20a%20text%20file%20to%20Excel%20-%20not%20the%20typical%20comma%20delineated%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F11479%22%20target%3D%22_blank%22%3E%40Maria%20Baker%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome.%20Please%20ask%20if%20there%20are%20more%20questions.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a text file that needs to be converted to Excel however the text is grouped in a fashion that Excel doesn't not convert into columns but exactly as it appears in the text file.  The text content looks like this.  I added an underscore before 'name' to differenciate each group of text.  How can I import to Excel where the text before each : becomes the column heading?

_name: All Company
id: 0
type: internal
privacy_setting: public
state: active
approximate_messages_count: 6
last_message_at: 2014-06-12 09:52:27.259000062 +00:00
o365_connected: false
group_admins: All Yammer Verified admins
member_counts:
internal: 24565
external: 0
uploaded_file_counts:
yammer_files: 0
sharepoint_files: 0
66069:
__name: Marine Matter
id: 66069
type: internal
privacy_setting: public
state: deleted
approximate_messages_count: 70
last_message_at: 2015-10-30 08:31:43.069999000 Z
o365_connected: false
group_admins:
with_o365_creation_rights: []
without_o365_creation_rights: []
member_counts:
internal: 0
external: 0
uploaded_file_counts:
yammer_files: 0
sharepoint_files: 0
89528:
_name: Summers Lab
id: 89528
type: internal
privacy_setting: public
state: deleted
approximate_messages_count: 10
last_message_at: 2012-10-01 11:00:22.418401000 Z
o365_connected: false
group_admins:
with_o365_creation_rights: []
without_o365_creation_rights: []
member_counts:
internal: 0
external: 0
uploaded_file_counts:
yammer_files: 0
sharepoint_files: 0
89758:
_name: Pilot Group
id: 89758
type: internal
privacy_setting: public
state: active
approximate_messages_count: 16
last_message_at: 2011-07-04 09:46:24.989397000 Z
o365_connected: false
group_admins:
with_o365_creation_rights: []
without_o365_creation_rights: []
member_counts:
internal: 0
external: 0
uploaded_file_counts:
yammer_files: 1
sharepoint_files: 0
90435:

13 Replies

@Maria Baker , I used Power Query to accomplish this. I copied your sample into a text file (attached). I changed the delimiter from ":" to ";" since there were dates in the data that used ":". To use this change the location of the source text file in the source table. The processed data will be in the Output tab.

@TheAntony Thank you.  I'm not familiar with power queries...so I'm stuck on this part, "change the location of the source text file in the source table."  Where do I change this?

Thank you

MB

@Maria Baker 

Another variant is attached.

FilenamePath is in the named cell within the sheet, same as in @TheAntony  file.

@Maria Baker 

  •  Download the 2 files I uploaded to your local drive
  • Open the Excel file - "Test Power Query.xlsx"
  • It will open up in the tab named Source
  • Change the File Path in the cell A2 from "C:\Users\dgant\Downloads\test.csv" to the file path of the test.csv file that you down loaded. 
  • Go to the tab named "Output" that shows you the output of PQ
  • Save the xlsx file
  • Open test.csv and add more records (making the appropriate changes I mentioned - using ; as the delimiter instead of :_
  • Save test.csv
  • Hit Data->Refresh All in the Excel file to see the output table updated with the info you put in the csv file

 

Or you can use @Sergei Baklan 's solution. Let us know how it goes.

 

 

 

@TheAntony First thank you very much for the assistance.  My apologies for being so thick......how did you change the delimiter from : to ; in the text file? My original file has 38000 rows.

 

Best Regards

MB

Best Response confirmed by Maria Baker (Occasional Contributor)
Solution

@Maria Baker 

IMHO, it's not necessary to change delimiters, in general it's not necessary to make any changes in your text file. For the delimiters you may use split only on left most delimiter, that won't affect the dates. 

 

Variant of layout as @TheAntony  suggested is in TextTransformTwo query attached.

You can import data from a text file into an existing worksheet.

On the Data tab, in the Get & Transform Data group, click From Text/CSV.

In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.

In the preview dialog box, you have several options:

Select Load if you want to load the data directly to a new worksheet.

Alternatively, select Load to if you want to load the data to a table, PivotTable/PivotChart, an existing/new Excel worksheet, or simply create a connection. You also have the choice of adding your data to the Data Model.

Select Transform Data if you want to load the data to Power Query, and edit it before bringing it to Excel.

@Smith_J  Thank you for the input.  This is the process I started with - the issue I'm having is understanding how to manipulate the data in power query into multiple columns instead of one column.

Thank you

@Maria Baker 

Please ask if any concrete questions while you check the query step by step.

@Sergei Baklan and @TheAntony - thank you for the assistance on my first request for help.  I was able to get the data per Sergei Baklan's instructions & files.

I have another file that needs to be extracted from one column to multiple columns - for my yammer users which I've attached here.

I tried dissecting your files to understand the queries but that was a fail.  I would be grateful for your assistance again.

Thank you

@Maria Baker 

That's bit another logic of this file. What to do

- since now you have commas within texts, ignore them as default delimiter. That could be done as

= Csv.Document(File.Contents(filePathName),{"Column1"},{0})

which says we split on one Column1 starting from position 0. Other words, no split.

 

- transforming column we select as names not only field starts by _, but also equal to name

Table.AddColumn(Source, "Names", each if Text.StartsWith([Column1.1],"_") or [Column1.1] = "name" then [Column1.2] else null)

and filter them as other fields as

= Table.SelectRows(#"Filled Down", each not ( Text.StartsWith([Column1.1], "_") or [Column1.1]="name"))

 

- since field role could be repeated for same person, we created in the middle intermediate table with this field only

image.png

group it by names and field (Column1.1), extract list with roles and convert it to text:

image.png

after filter field role from main table and append to it above one

 

- datetime text here is without separation of date and time in the text. Thus we remove comma on the end and insert T between date and time

= Table.ReplaceValue(#"Extracted Text Before Delimiter",each [last_date_accessed], each Text.Insert([last_date_accessed],10,"T"),Replacer.ReplaceText,{"last_date_accessed"})

convert now this text on actual datetime.

 

- if in another file there is no field last_date_accessed (as in initial one) error appears. If so correct on proper field name or remove the step. That could be handled automatically, but better to know all possible datetime field names.

 

Please check in attached file.

@Sergei Baklan  YOU are a life saver!  Thank you so much for the detailed explanation and the file.  I'll work with this to better understand these queries.

I greatly appreciate your time & expertise!

@Maria Baker , you are welcome. Please ask if there are more questions.