Open text file and store specific columns using arrays

Hello everyone
I have Sample text file (in fact I would deal with large number of text files ) >>
Manually I copy the text file data and put it into sheet then select column A (Where I put this data) >> go to Data tab >> Click "Text to Columns" and split data by comma
Then delete the first four columns and the header row...
There are four columns remaining //


After that I am using this code to have these results in specific way

Please Login or Register  to view this content.

** I need not to deal with the excel file when manipulating data .. I just need to deal directly with the text file and do all these tasks using arrays
Mr. Karedog has helped me in similar issue .. but this is different a little

Thanks advanced for help

Hi Yasser,

Power Query will do this split and remove columns before you get it into Excel. I just don't know if PQ is available/allowed in Egypt yet.
https://www.microsoft.com/en-us/down...4-42a65920901d

Thanks a lot for reply
I don't have enough knowledge with Power Query and I need to do that as it is part of large project .. and I have about 2000 text files
When I post the thread I just take the idea of how to solve it with one text file .. After that I try to adopt it to the other text files using loops
Best Regards

Hey Yasser,

Find attached your data, I typed in, and did what I suggested. It took about 3 minutes to build this PQ answer. Here is what I did:
1. Pulled in your data into Power Query. PQ looks like Excel in rows and columns but is a pre-process before it gets into real Excel
2. Added a Index column, so I could UnPivot all your data.
3. Filled in all the nulls in your table with zero instead of null.
4. Unpivoted the data in PQ
5. Load and Save it back to column K of the workbook.

NOW - If you were to give me a new workbook OR Text file, I'd simply change the first step of where the source .txt file is located and it would do the above steps.
AGAIN - If I told it to do all text files in a folder (your 2000) it would do it for all 2000 files all at one time.

See my PQ steps in the attached (You will need to have installed the add-in. Microsoft has a tool in Excel to do all your work. You simply need to learn it. It is much easier than VBA.
PQ Transpose for YasserK.xlsx

Thank you very much Mr. Marvin for your great efforts
It didn't work for me. Incompatible warning with current version and even if it worked it won't suit my needs as I have told you that it is just a part of another large code ...
So All what I need to get the results you got in J & K in an array .. Not even need to populate it as it will be related to another part of code
Thanks a lot for your great help. I appreciate that a lot

Maybe :

Please Login or Register  to view this content.

Thank you very very much Mr. Karedog for this awesome solution
Just little point : In the first column of results I noticed the last is 235 and I need it to be 365 ... I tried to change that part

Please Login or Register  to view this content.

to be

Please Login or Register  to view this content.

But I got the same results
What could I change to fix that part?

235 is a correct number.
If you open Sample.txt and cut paste to Excel, you will notice that the last row is 48.
We discard first row (header) by deleting this row, the result is 47.
Number of columns want to retrieved is 5.

So total = 47 * 5 = 235 (which is the result of macro).

From what calculation you come to this 365 number ?

Anyway, if you want to force the output to a certain number of rows, the function is added with one extra parameter : nRowsOutput

Please Login or Register  to view this content.

Thank you very very much Mr. Karedog for these charming solutions. You are life savior
Saying thank you is NOT ENOUGH at all ...
Best and kind regards

Hello Mr. Karedog (I am so sorry for disturbing you in these similar issues)
I have different structure in the following sample text file >>
What I am doing manually here is to copy the text file to worksheet and I got 16 columns
I want first to skip the first two rows (headers) and keep only four columns of these 16 columns (column A & E & I & M)
Look at this snapshot to see green parts are what I want to keep .. in one array with four columns

I don't want public procedure for that.. Just regular macro
Thanks a lot for great and wonderful help in that complicated issue
Best Regards

Hello
After studying your awesome code I could figure it out to suit my new sample text file
I welcome any fixes

Please Login or Register  to view this content.