very big text file.. how do I break it up for excel? **PICS!** *Confirmed hard!*

[Keego]

It has row, column, and a number (0-720) for each row & column, so is there any way to segment the data into an even number of die (row, column) so I can manage it in excel?

 

[Ameesh]

excel wont open it?


post part of the text file so we understand what the format is, its not very clear from decription.

 

[dullard]

I don't quite follow your file logic. Does it look like this?

Row, Column, Data
1 1 ###
1 2 ###
1 3 ###
.
.
.
1 720 ###
2 1 ###
2 2 ###
2 3 ###
.
.
.
720 719 ###
720 720 ###

Where ### stands for your data. If not how does it look? What separates the numbers (commas, spaces, quotation marks, etc)?

 

[Keego]

it looks like this: picture

I need to segment it, then average the z-height column, but it's a 55mb txt file right now!

 

[notfred]

that's why I'm switching all my data from flat files to SQL databases... it's easier to manage.

 

[Keego]

Originally posted by: notfred
that's why I'm switching all my data from flat files to SQL databases... it's easier to manage.

great to know

 

[bunker]

Originally posted by: Kyguy
it looks like this: picture

I need to segment it, then average the z-height column, but it's a 55mb txt file right now!

If it looks like that pic then why do you need any help? We need to know what the text file looks like or is that it already in Excel?

 

[Keego]

That's what it looks like in the text file too, it's tab delimited

The index goes from 0-719, and for that section I need to average zheight for each 0-719 segment. There are 772 0-719 segments that I need to average!

here's the txt file: txt.jpg

Ignore everything except the row, col, index, and zheight

 

[Anubis]

hate to say this but i think your gonna have to copy and paste

 

[BooneRebel]

First I'd break it into separate files. Say, sections 1-325 (with everything below that deleted), saved as text1.txt. Then save the second half (sections 326-end) as text2.txt. Once you get it down into chunks small enough for Excel to import then you can work with the data. I believe it's limited to 65,000 rows (somewhere in there)

Anyway, if the z-column is all you care about I'd then extract just that column into a new spreadsheet and do your average from there.

 

[HomeBrewerDude]

Originally posted by: BooneRebel
First I'd break it into separate files. Say, sections 1-325 (with everything below that deleted), saved as text1.txt. Then save the second half (sections 326-end) as text2.txt. Once you get it down into chunks small enough for Excel to import then you can work with the data. I believe it's limited to 65,000 rows (somewhere in there)

Anyway, if the z-column is all you care about I'd then extract just that column into a new spreadsheet and do your average from there.

yep, thats excel's record limit.

Field limit is 256, though you can skirt that if you use the import wizard to selectively import just the fields you need.


Import it, Excel will truncate at its limit. Then go to that position in the text file and delete everthing above it. save as text2.txt. repeat until you get the thing parsed out. Average each part, then average the average. Hopefully, each case will have the same number of data points, otherwise you will have to do a weighted average.

EDIT if you don't have more than 65000 rows then just point excel to open the file, select the delimited option, select the appropriate delimiter, and on the import data wizard screen 3 of 3 you can choose to NOT import any given field buy clicking that column, and choosing "do not import"

 

[dullard]

Sounds like a perfect job for some programming. Do you have any programming experience, especially with working with files? I have tons of projects that result in output like yours, and I find it easier to write a short program than to find ways to get something like Excel to do the work. Plus even if you could get it into Excel, you'd have to manually tell it to average all those columns.

Basically do this:
1) Open the text file.
2) Read in the labels at the top.
3) Read the data in the row, storing only the data you need (row, column, index, zheight).
4) I'd have an array that looked like this: Zheight(row, column, index).
5) Open an output file.
6) Make a loop to average the Zheight for each index.
7) Save the average to the output file.

If you are an experienced programmer, this should take roughly 15 minutes to program. This is far less time that what it would take to manually force Excel to open the file and then do the averages. And if you ever have to do this again, you already have the program sitting there ready.