In Excel, the maximum worksheet size is 1048576 rows by 16384 columns. It means that large files, typically gridded files are hard to manipulate.
A possible solution is to use SURFER, particularly the worksheet. SURFER is not free and other solutions will be presented in future publications. Let’s follow the guide:
Open your file, typically a txt file containing 3 columns for XYZ
In the window that pops up, several options are requested (delimited, first line, Tab etc…). If options are correctly set, the preview displays columns as they are in the file. Click ok
The file I’m using here contains more than 3 million lines. If you want to make a formula, click on Data–>Transform
The transform option can be set up by column. For instance, you want to transform positive values of Z into negative values. Just write the equation by column, meaning that column E will be the result of -D. However, you can use more complex formula using functions.
Once you click ok, just wait as more than 3 millions lines have to be calculated ! Check the progression bar at the bottom:
Some operations can be very long, up to minutes, sometimes more. Be patient.
In my case I have transformed C (Depth from sealevel) into D (Time from sealevel) using a simple equation described in this article:
Once it’s done, you must save the file as a txt file
ENJOY !
