Create multiple columns from text file list with over 57 million values.

**Water Doc** · 12-15-2012, 01:20 PM

I have a text file that contains 57,600,000 million lines of values. These values are in a single column in the text file. They represent a 150(column) x 160(row) matrix repeated 2400 times. I wish to complete 2 different operations, each with 3 objectives.

Operation 1 (column)

Open the text file
Select every nth data point
Save the selected values to another .csv or xlsx

Operation 2 (row)

Open the text file
Select a range of values (150) starting every nth line
Save the selected values to another .csv or xlsx

If this cannot be done alternative 2 would be to break the list into 2400 columns by 24000 rows

Thank you very much for looking and any assistance you can give.

I would post the file but it is around 100 mb

**mrice** · 12-15-2012, 04:04 PM

Please try these two

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**Water Doc** · 12-15-2012, 05:18 PM

Martin,
Thank you very much for your assistance. I have tried to run the script but am not getting the correct number of values. I feel this is due to my lack of problem explanation or improperly modifying the code you supplied. To ensure I have explained it correctly I have attached a file above for your review and comment.
Thank you

**mrice** · 12-15-2012, 05:44 PM

Can you try this. It compiles a 160 x 150 matrix in memory and then selectively reads rows and columns.

Please Login or Register  to view this content.

You will need to change at least one of the For rows as indicated.

**Water Doc** · 12-16-2012, 12:35 AM

Hi Martin,
I ran the script and got 24,000 values, however when selecting the column data I should get 160*2400=384,000 and for row data I should get 150*2400=360,000 any thoughts?

The other alternative is to create an array of 24,000 values in a column (vertically) by 2,400 values in a row (horizontally) then I could work with this file in Excel.

So the first 24,000 values would become column 1, the next 24,000 values would become column 2, and so on repeated 2,400 times.

Thanks Again!