Import .txt to Excel?

[Thomas79]

Hey! I have enormous amount of data in .txt which I need to be converted to Excel. Data looks like this:

AhAs3s2s@100,AdAs3s2s@100,AcAs3s2s@100,AhKh3s2s@100,AdAh3s2s@100,AcAh3s2s@100,AdKd3s2s@100,AcAd3s2s@100,AcKc3s2s@100,AhAs4s2s@100,AdAs4s2s@100,AcAs4s2s@100,AhKh4s2s@100, (...)

Outcome has to be like this:

[ COLUMN 1 ][ COLUMN 2 ]
[ AhAs3s2s ][ 100 ]
[ AdAs3s2s ][ 100 ]
[ AcAs3s2s ][ 100 ]
[ AdAh3s2s ][ 100 ]
etc.

I tried so many things and came nowhere close to the final result. Is there anyone able to help me?

 

[sandy666]

you can try PowerQuery

[SIZE=1]let
    Source = Table.ToColumns( Csv.Document(File.Contents("Z:\TestFolder\test.txt"),[Delimiter=",", Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Column1", "Column1", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AhAs3s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AdAs3s2s[/td][td]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AcAs3s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AhKh3s2s[/td][td]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AdAh3s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AcAh3s2s[/td][td]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AdKd3s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AcAd3s2s[/td][td]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AcKc3s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AhA s4s2s[/td][td]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AdAs4s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td]AcAs4s2s[/td][td]100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]AhKh4s2s[/td][td=bgcolor:#E2EFDA]100[/td][/tr]
[/table]