How do i convert my notepad file which is in Row format to a Excel of Column format

[littleman2000]

Hi,
I want to convert a notepad file(.txt format) to a excel(.xlsx format)

The format which i have in my notepad(text file) as below,
[User]
uid={moderator removed}
last_name={moderator removed}
first_name=Gerald
email_address={moderator removed}
accessibility=0
SAPME:DEFAULT SITE=XX92
role=SAP_XMII_User;SAP_ME_USER;
[User]
uid={moderator removed}
last_name={moderator removed}
first_name=Rob
email_address={moderator removed}
accessibility=0
role=SAP_ME_USER;SAP_XMII_User;
[User]
uid={moderator removed}
last_name={moderator removed}
first_name=Arun
email_address={moderator reomved}
accessibility=0
role=SAP_XMII_User;SAP_ME_INTEGRATOR;SAP_XMII_DynamicQuery;SAP_XMII_Developer;
group=Administrators;


The format which i need to change to in Excel is as below,

I have tried by enabling a "Record Macro" and then by using a Delimiter and Transpose functionalities from the Excel
But i couldn't get the end result as Field Names are not common to a particular User Row-sets.

Thanks!
Shaan.

 

[Toadstool]

Hi ShaanPrakash,

I'm sure the VBA experts will be here soon to give you a more elegant solution, but in the meanwhile here's a solution using functions which just needs a little manual intervention.

Paste your Notepad into column A, then go to the last data row and add another "[User]" just to simplify the formula finding the last range.

Copy the formulae in columns C to L down as many rows as the maximum number of [User]s

ShaanPrakash.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	[User]		User Row	Range User	uid=	last_name=	first_name=	email_address=	accessibility=	SAPME:DEFAULT SITE=	role=	group=
2	uid={moderator removed}		1	$A$1:$A$8	{moderator removed}	{moderator removed}	Gerald	Gerald.{moderator removed}@{moderator removed}.com	0	XX92	SAP_XMII_User;SAP_ME_USER;	Nil
3	last_name={moderator removed}		9	$A$9:$A$15	123547698	{moderator removed}	Rob	rob.{moderator removed}@{moderator removed}.com	0	Nil	SAP_ME_USER;SAP_XMII_User;	Nil
4	first_name=Gerald		16	$A$16:$A$23	129876543	{moderator removed}	Arun	arun.{moderator removed}@{moderator removed}.com	0	Nil	SAP_XMII_User;SAP_ME_INTEGRATOR;SAP_XMII_DynamicQuery;SAP_XMII_Developer;	Administrators;
5	email_address=Gerald.{moderator removed}@{moderator removed}.com		24
6	accessibility=0
7	SAPME:DEFAULT SITE=XX92
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(COUNTIF(A:A,"[User]")<ROW()-1,"",AGGREGATE(15,6,ROW(A:A)/(A:A="[User]"),ROW()-1))
D2:D7	D2	=IFERROR(ADDRESS(C2,1)&":"&ADDRESS(C3-1,1),"")
E2:E7	E2	=IFERROR(RIGHT(INDEX(INDIRECT($D2),MATCH(E$1&"*",INDIRECT($D2),0)),LEN(INDEX(INDIRECT($D2),MATCH(E$1&"*",INDIRECT($D2),0)))-LEN(E$1)),"")
F2:L7	F2	=IF($E2="","",IFERROR(RIGHT(INDEX(INDIRECT($D2),MATCH(F$1&"*",INDIRECT($D2),0)),LEN(INDEX(INDIRECT($D2),MATCH(F$1&"*",INDIRECT($D2),0)))-LEN(F$1)),"Nil"))

 

[littleman2000]

Thanks Toadstool for this wonderful knowledge!
This works absolutely fine

 

[Toadstool]

You're welcome!

 

[sandy666]

Power Query as alternative:

[User]	uid	last_name	first_name	email_address	accessibility	SAPME:DEFAULT SITE	role	group
	{moderator removed}	{moderator removed}	Gerald	{moderator removed}	0	XX92	SAP_XMII_User;SAP_ME_USER;	Administrators;
			Rob	{moderator reomved}			SAP_ME_USER;SAP_XMII_User;
			Arun				SAP_XMII_User;SAP_ME_INTEGRATOR;SAP_XMII_DynamicQuery;SAP_XMII_Developer;

// data
let
    Source = Csv.Document(File.Contents("path_to_the-file\data.txt"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    SplitE = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    USC = Table.RemoveColumns(Table.Unpivot(Table.ReplaceValue(SplitE,null,"User",Replacer.ReplaceValue,{"Column1.2"}), {"Column1.2"}, "Attribute", "Value"),{"Attribute"}),
    Group = Table.AddColumn(Table.Group(USC, {"Column1.1"}, {{"Count", each _, type table}}), "Custom", each List.Distinct(Table.Column([Count],"Value"))),
    Extract = Table.RemoveColumns(Table.TransformColumns(Group, {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),{"Count"}),
    SplitComma = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    ReplaceBack = Table.ReplaceValue(Table.PromoteHeaders(Table.Transpose(SplitComma), [PromoteAllScalars=true]),"User",null,Replacer.ReplaceValue,{"[User]"})
in
    ReplaceBack