Saw the first one and it reminded me I have a question too.

Have a text file to import with lots of X Y data points but the sampling rate was about 10x higher than needed so would like to delete 9 rows then leave a row and delete the next 9 and so on. Easily done by hand but the text file is about 76mb so lots and lots and lots and lots of lines.

Before I settle in for the night doing it by hand is there any way of automating such a cull?

macro

import then copy to next sheet using a x+10 loop in VB

either make a macro to delete 9 rows and just hold down your shortcut key you give the macro,
or
insert a column on the left of your data.
Index your rows of data (i.e. row 1 is 1, 2 is 2)
on a second sheet create formula to return only every tenth row of data, copy and pasete values, then use a sort on column a ascending to compress the data into so that the index goes 1, 11, 21, 31 etc.

crap, the 65,536 line limit doesnt even get 1/2 of the number of lines so either going to have to split up the text file or write some sort of script or code to process the text file. Not done any C++ or VB for about 8 years so this is going to be a learning experience.

do it in access which can handle much bigger tables?

don’t know if this would work but if you add a column and in that column you put number 1 to 10 or 1 to 9, repeating over and over, you could then use pivot tables to extract just the rows with 1’s I think… maybe…

Excel2007 does bigger tables too.

Alternatively, create a new worksheet, and then in cell a1 do something like

=indirect(“‘Sheet1’!A”&(Rows(a1)*10))

I’m on my phone at the moment, so this might not be entirely accurate, but this is the general approach I’d do if I wasn’t writing a macro.

If I was writing a macro, it would simply be an if statement which would delete the current row unless mod10 of the row number were zero. However I’d start at the bottom unless I could think of a way of keeping row number constant otherwise it would delete everything.

Don’t have access but I can switch to a PC with a newer version of Excel so might do that.

Currently setting up an Excel macro that copies a line from one sheet to another and am just trying to get it to jump 10 rows down on the source sheet and copy and paste one row down on the destination sheet.

Just having difficulty getting it to change the rows it copies and pastes from.

eg I have

Rows(“1:1”).Select

for the 1st row but want to replace the 1:1 with x which I defined as an integer variable earlier on and want to do an x = x+10 for the source and so on.

Thought of pivot tables but couldnt get 1-10 in the 65k columns

Thought of pivot tables but couldnt get 1-10 in the 65k columns

what do you mean couldnt get it in?

76 Mb of “pure text” is a lot of data. Is it a one off? I’d write some code to do it directly rather than use Excel for a job its not really been designed for and will be very inefficient at. Excel is usually slow enough without opening massive files in it, just to delete 90% of it.

Think I sorted it.

Well it’s doing something and seems to be working it’s way down the page. Will try it on the faster PC with the whole file in a bit if it works.

My 1st Excel Macro!

yeah I will continue trying to write a proper program as I may have to do it again. At the moment it’s just 2 76mb text files and once they are done that is it. Well I then have to do a load of other calcs with the data. But first I need to reduce the data by at least 9/10ths as the sample rate was too high.

Don’t have any C++ or VB software on this PC as it’s been a looooong time since I used either of them.

Andy..

I think you have already done this..
But, i am sat at home with a broken collarbone, so can’t sleep!!

Just done exactly what you wanted in a macro in Excel. And just emailed it over to you..

God… I must be bored.

Anyway, hope it helps.
Josh..

yeah I will continue trying to write a proper program as I may have to do it again. At the moment it’s just 2 76mb text files and once they are done that is it. Well I then have to do a load of other calcs with the data. But first I need to reduce the data by at least 9/10ths as the sample rate was too high.

Don’t have any C++ or VB software on this PC as it’s been a looooong time since I used either of them.

I’d use Ruby or Perl, be quicker and less crap to install.

Learning Ruby at the moment, so the following probably isn’t very good Ruby programming. Works, though 🙂

1. Save to a text file (e.g. skip_lines.rb)
2. Run as skip_lines <inputfile> <outputfile> <lines to skip>


def foo(infile, outfile, lines)
f = File.open(infile, "r")
f2 = File.open(outfile, "w")
counter = 0
f.each_line do|line|
f2.write(line) if (counter % lines.to_i == 0)
counter = counter +1
end
f.close
f2.close
end

foo ARGV[0], ARGV[1], ARGV[2]