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]

Column1.1	Column1.2
AhAs3s2s	100
AdAs3s2s	100
AcAs3s2s	100
AhKh3s2s	100
AdAh3s2s	100
AcAh3s2s	100
AdKd3s2s	100
AcAd3s2s	100
AcKc3s2s	100
AhA s4s2s	100
AdAs4s2s	100
AcAs4s2s	100
AhKh4s2s	100