Getting information from a source file and displaying in an easy to use table

[FootyMan]

Hi,

Many years ago I requested help on a very basic table I had that was made much better by users here, and that I still use to this day. I was hoping to see if the following is possible in excel (I'm not sure if it's actually too complex).

I am trying to get excel to pull information from a source document (but that isn't formatted in excel), and then display it in an easier to read table (in excel) - which I can then manipulate as required. The issues are that the source document isn't really uniform, so I have to search out the items required, and that it's very manual, and I'd like to be able to add some customisability. The actual document is longer and has many ids and variables (not helpfully named - hence requiring customisability to add these but also output these usefully) - In the example I've provided I've tried to change the variables to provide a game style example.

Really appreciate any help on this, or letting me know if it's even possible.

I couldn't find how to add the workbook (I assume for security) so I have just added images.

 

[alansidman]

You might consider using Power Query to get the data into excel. Click on the link in my signature for more information on PQ

 

[FootyMan]

Hi, Thanks for your response.

I've had look through the Power Query guide you mentioned, it does look like it would be useful. I'm struggling on how to get my data to look like theirs though, if my source data looked like that I don't think I'd need guidance.

The issue I have is that I can't seem to figure out how to get the source to output to something like what they start with, they have columns etc labelled correctly, but I don't know how to get a long list like my source to sort to something like that. It's worth mentioning my example is just a game example using variables like strength or health, but the real data would have just a long list, with anywhere between 200-300 lines of variables - I'm just trying to be able to extract a list of around 20 of the variables, for each requested "hero" (which in the real data is just an id, but again a long list of these). In that guide it does mention it can be used for things over 1 million lines, in the end mine is only around 30,000 so I'm sure it's possible, but not sure how to start to go about it.

 

[felixstraube]

Can you upload the source file or bigger sample (than the image)?

 

[felixstraube]

Would something like this work?:
Here you can download the working file:
txtload.zip

from the data you provided:

ID	Variable	Value
id8	name	"Hero"
id8	willpower	52
id8	int	28
id8	melee	25
id8	ranged	15
id8	str	30
id8	maxhealth	50
id8	temphealth	50
id10	busy	1
id18	name	"Hero2"
id18	willpower	74
id18	int	35
id18	melee	40
id18	ranged	40
id18	dex	75
id18	str	20
id18	maxhealth	50
id18	temphealth	50

Power query code:

let
    Origen = Csv.Document(File.Contents("C:\Users\fstraube\Desktop\data.txt"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Filas filtradas" = Table.SelectRows(Origen, each ([Column1] <> "" and [Column1] <> "}")),
    #"Personalizada agregada" = Table.AddColumn(#"Filas filtradas", "ID", each if(Text.Contains([Column1], "{")) then Text.BeforeDelimiter([Column1],":") else null),
    #"Rellenar hacia abajo" = Table.FillDown(#"Personalizada agregada",{"ID"}),
    #"Filas filtradas1" = Table.SelectRows(#"Rellenar hacia abajo", each not Text.Contains([Column1], "{")),
    #"Personalizada agregada1" = Table.AddColumn(#"Filas filtradas1", "Personalizado", each Text.Trim(Text.AfterDelimiter([Column1],":"))),
    #"Texto extraído antes del delimitador" = Table.TransformColumns(#"Personalizada agregada1", {{"Column1", each Text.BeforeDelimiter(_, ":"), type text}}),
    #"Columnas reordenadas" = Table.ReorderColumns(#"Texto extraído antes del delimitador",{"ID", "Column1", "Personalizado"}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Column1", "Variable"}, {"Personalizado", "Value"}})
in
    #"Columnas con nombre cambiado"

Let me know if this works for you.