Import .txt to Excel?

[SanSebastian66]

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

AhAs3s2s(at)100,AdAs3s2s(at)100,AcAs3s2s(at)100,AhKh3s2s(at)100,AdAh3s2s(at)100,AcAh3s2s(at)100,AdKd3s2s(at)100,AcAd3s2s(at)100,AcKc3s2s(at)100,AhA s4s2s(at)100,AdAs4s2s(at)100,AcAs4s2s(at)100,AhKh4s2s(at)100, (...) (I didn't use (at) sign because then I get errors which don't let me post a thread)

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?

[AliGW]

As suggested in your other thread, will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[SanSebastian66]

Ok I did it in Excel (added attachment)

[mohan.r1980]

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

Are you tried "Text to Columns" option with Delimited="@"?

[AliGW]

Done in two steps using Power Query (Get & Transform), which you should have on your Data ribbon. Here's the M code:

Please Login or Register  to view this content.

[mohan.r1980]

or you may import text file from Data>>From Text >>Delimited>>Other=@

[AliGW]

I think most people are missing the fact that the data is all in one cell and there is the comma to deal with first to get the data into rows.

[SanSebastian66]

mohan.r1980 Yes, I tried that way, it didn't work tho, I couldn't make it into rows

AliGW That's amazing! I'm truly impressed. Is there any tutorial on how to use your code?

[AliGW]

Who are you talking to? Have you tried my suggestion???

[SanSebastian66]

Yes, I'm sorry it's hard to reply here without getting an error. I answered to your reply in edit.

[AliGW]

I will write a quick tutorial and post it here. Give me fifteen minutes and then check back.

[AliGW]

Power Query: Data from One Cell to Rows & Columns

1. With your data all in cell A1, select cell A1 and go to the Data ribbon – click on From Table/Range in the Get & Transform section – do NOT tick My table has headers. This will load your data into Power Query.
2. In the PQ window, select the drop-down under Split Column on the Home ribbon and choose By Delimiter. In the dialog, select comma from the drop-down and in the advanced section, select Rows – leave everything else as it is and click OK.
3. Again select the drop-down under Split Column on the Home ribbon and choose By Delimiter. In the dialog, select custom from the drop-down and type @ into the box, then click OK.
4. Choose Close & Load from the Home ribbon. Your new table will now appear in the Excel workbook.

The code I posted before is generated by taking these steps.

[SanSebastian66]

Thank you very much! However I somehow can't find "Rows" in advanced section. It's just not there (probably different Office version). I attach screenshot from my "split column by delimiter" window:

[AliGW]

Ah! This means you do not have the latest version of Power Query and it's a new option. Never mind - let me see if I can find a workaround for you. Give me ten minutes!

[AliGW]

Try this instead:

Power Query: Data from One Cell to Rows & Columns

1. With your data all in cell A1, select cell A1 and go to the Data ribbon – click on From Table/Range in the Get & Transform section – do NOT tick My table has headers. This will load your data into Power Query.
2. In the PQ window, select the drop-down under Split Column on the Home ribbon and choose By Delimiter. In the dialog, select comma from the drop-down and click OK.
3. On the Transform ribbon, select Transpose.
4. Again select the drop-down under Split Column on the Home ribbon and choose By Delimiter. In the dialog, select custom from the drop-down and type @ into the box, then click OK.
5. Choose Close & Load from the Home ribbon. Your new table will now appear in the Excel workbook.

[SanSebastian66]

That's incredible. Thank you very much! Yet for some reason it shows only 2.5k rows and I expect somewhere between 20-30k, do u have any idea why would that be? If not, I'm just gonna divide .txt into 10 smaller pieces and work with that. Thank you again!

[AliGW]

It may be a limitation in PQ - not sure, as I have not worked with such large data myself. Splitting it into manageable chunks will work. PQ is a really great tool - have a play with it to see what else it can do.

[SanSebastian66]

I'm forever grateful

[AliGW]

Glad to help!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.