Automatically (almost) importing and updating data from an external CSV file in LibreOffice Calc

How import a CSV file into a LibreOffice (or OpenOffice) spreadsheet?
The easiest/most practical way I found (if you want an easy way to update the data when the CSV file changes) is described below.

Importing a CSV file as new sheet

• Click [Insert] -> [Sheet from file...]
• Select the file you want to import and click [Insert].
• The dialog box about importing CSV files will open. Make the settings to define the file format and click [OK]
• The Insert Sheet dialog box should now be visible.
• If you want the data to update when the CSV file changes, tick the box in front of "Link"
• Click OK.
• The data does not update itself, but it is fairly easy to do so, see below.

Updating the data

Unfortunately, the data is not reloaded when the CSV file changes, but it is easy to do this manually by going to  -> [Links...] and then clicking [Update]. Also, when you save your spreadsheet and click File -> Reload, it will ask you if the "links to other files should be updated". Click "yes" to re-read the CSV data. The same will happen if you close and re-open the spreadsheet.

It is important to note that the the tab (sheet) that contains the data from the file can ONLY contain these data. Other columns may be deleted when the data is updated. Also, I think it is good practice to keep the original data in unmodified form in their own sheet and the analysis and graphs in a new one.

To improve this a little, let's put an [Update] button on the spreadsheet that re-reads the CSV file when clicked. All the file-format settings you made earlier when importing the file will automatically be applied. To do this, we first need a macro that updates the link. Go to [Tools] -> [Macros] -> [Organise Macros] -> [Openoffice.org Basic] -> . You need to make a new Sub procedure that does the updating. The code is given below (from the OpenOffice forum) (Note that the "Sub Main" part is probably already there).

Sub refreshAllSheetLinks() oEnum = thisComponent.SheetLinks.createEnumeration while oEnum.hasMoreElements oLink = oEnum.NextElement oLink.refresh wend End Sub   Sub Main end sub

Now we can add the button.

• Save the macro and exit the macro editor
• Click [View] -> [Toolbars] -> [Form Controls]
• Make sure the [Design Mode] button (top right) is active
• Use this toolbar to add a "Push Button"
• Right click on the button and select [Control...]
• Change [Label] in the [General] tab to "Update"
• Add the macro you just created to the [Mouse Button Pressed] event in the [Events] tab. (click [...] and browse for your macro)
• Turn the [Design Mode] off.
• Close the toolbar. Done!

Final notes

NOTE 1: The [Insert] -> [Link to External Data...] option may also be useful, but does not seem to work with CSV files.

NOTE 2: It may be possible to set up an external data source via [Tools] -> [Options] -> [Databases] but I find that hard to use and have not yet looked into using that option.

Posted in libreoffice, openoffice

Defining your own functions in libreoffice calc

This post will explain how to define your own function in LibreOffice Calc. Your own function can then be used in the same way as the standard functions that LibreOffice comes with.

• Go to Tools->Macros->Organise Macros->Libreoffice Basic
• Select Module1 (it is usually already selected)
• Click Edit

You now have an editor that says something like:

Sub Main   End Sub

Let's define a function called "area" that multiplies two numbers to calculate an area. Type the following below End Sub.

Function Area(width, height) Area = width * height End Function

That's it! You can now use the function Area in your spreadheet!
for example: =AREA(A1,B1)

Here is another example of a function that converts degrees to radians. (Note that a function already exists to do this conversion, it is called RADIANS. But that makes it easy to check that your function works as intended.)

Function Deg2Rad(degrees) Deg2Rad = degrees / 180.0 * pi() End Function

Brilliantly easy!

Just remember the following:

• Your function cannot refer to specific cells. If you want to give it values to calculate with, they need to be specified between the brackets
• Do not use the name of a function that already exists.
• The function always needs a line that assigns a value to the function name. So a function called ABC must have a line that says ABC = x, where x is the value that contains the result of the calculation.

Posted in libreoffice, openoffice

Mounting a remote directory using ssh

In the checklist below, the "local" pc is the one you are working on, and the "remote" pc is the one that has the directory you want to access.

• local: run ssh-keygen, do not add a pass phrase
• local: copy .ssh/id_rsa.pub to the remote host
• remote: cat id_rsa.pub >> .ssh/authorized_keys
• local: install sshfs
• local: sshfs remote:/directory /mnt/local_mountpoint

To unmount, use:

• fusermount -u /mnt/local_mountpoint

Posted in linux

Compiling Aria2, CNS, procheck and aqua for NMR structure calculation

A quick guide to compiling ARIA 2.2, CNS 1.21, AQUA 3.2 and PROCHECK, under Ubuntu Karmic 9.10 (32-bit)

If you use Gentoo linux, you can find aria under sci-chemistry (masked by ~x86). Procheck is in the science overlay (see the layman and overlays documentation). Aqua has to be installed manually at the time of writing this post.

In Ubuntu, the following packages are needed:

• numpy
• scipy
• python-scientific
• tcl 8.5
• tk 8.5
• tix
• matplotlib
• cns (to be compiled manually, see below)
• the intel fortran compiler (to compile cns, gfortran does not seem to work)
• tcsh
• openjdk6 (needed for intel fortran compiler)
• libstdc++5 (for cns)
• flex (for cns)

Some files need to be copied from ARIA to CNS before it is compiled, this can be found in the ARIA instructions. Both CNS and ARIA can be compiled according to the instructions, but during the compilation of CNS with gfortran 4.3 I got a Segmentation fault. Using the Intel fortran compiler (ifort, free for academic use), there were no problems. For the installation of the Intel compiler, I needed to have openjdk6, which I installed using the Synaptic "default_jre" package.

Procheck and aqua can be installed according to the instructions. I used ifort to compile procheck, and g++ for aqua.

And here are the lines to include in .tcshrc (of course substituting the directories for the ones that are used on your system):

# for cns: source /home/louic/software/cns_solve_1.21/cns_solve_env   # for aria: setenv ARIA2 '/home/louic/software/aria2.2' alias aria2 '/usr/bin/python -O $ARIA2/aria2.py' # for procheck and aqua: setenv prodir '/home/louic/software/procheck' setenv aquaroot '/home/louic/software/aqua3.2' source$aquaroot/aqsetup

There seemed to be some problems on a 64-bit system, which is why I switched back to using 32-bit for now. I did not fully investigate this issue and do not know what the exact problem was.

Posted in linux, nmr, science, ubuntu

Turn off auto complete in libreoffice or openoffice calc and writer

Assuming you already found the Tools -> Autocorrect options (which is exactly where you'd expect it to be), that still leaves the (very annoying) autocomplete function enabled. To turn it off, you need to disable:

Tools -> Cell Contents -> AutoInput

This will turn it off until you turn it back on. (I found it thanks to this weblog)

Unfortunately, when editing a table in Writer, some auto-correct options still remain: numbers are automatically "corrected", +1 and 1.0 for example change into "1". This can be changed by

setting the "Number format" (in right-click menu) of all cells to "Text".

You can probably change the default table style to fix this forever, but I have not tried yet.

(Why do the bloody programmers always think they know better what I want than myself?)

Posted in libreoffice, openoffice

Using procheck_nmr on a large number of structures

After a structure calculation with the aria 2.2 software I used aqua and procheck_nmr to assess the result. Although procheck_nmr worked fine on my 20 refined structures (in aria's refine directory), it ran into trouble with the 100 structures after iteration 8 (directory it8):

 * Restraints read in from file: * allpdb.nrv * Warning. Error reading restraint on line 34 * Warning. Error reading restraint on line 35 * Warning. Error reading restraint on line 36 * Warning. Error reading restraint on line 37 * Warning. Error reading restraint on line 38 * Warning. Error reading restraint on line 39 * Warning. Error reading restraint on line 40 * Warning. Error reading restraint on line 41 * Warning. Error reading restraint on line 42 (... and so on...)

A look at the allpdb.nrv file indicated that nothing was wrong with it, and as I mentioned the analysis worked fine when a lower number of structures was analysed.

A quick look at the procheck_nmr script indicated that these error messages were produced by vplot, which has it's source code in the vplot.f file. The source-code defines a string called IREC, that will hold the record with all the relevant distances in all the structures, but IREC is defined as:

 CHARACTER*512 IREC

...which is not long enough to hold all the distances that are read from the .nrv file. A similar problem occurs somewhere else in the code, where a format string is used on the record that is read from the .nrv file.

By increasing the size of the relevant variables and recompiling vplot.f, the analysis runs smoothly. Here is the diff-file that may be used as a patch:

louic@picadilly:~/software/procheck$diff -u ../old/procheck/vplot.f vplot.f --- ../old/procheck/vplot.f 2010-01-19 13:37:32.000000000 +0000 +++ vplot.f 2010-01-21 18:46:38.000000000 +0000 @@ -2141,7 +2141,7 @@ CHARACTER*4 ATTYP(2) CHARACTER*9 RESDET(2) CHARACTER*80 FNAME - CHARACTER*512 IREC + CHARACTER*1024 IREC INTEGER IATNO(2), IATTYP, ICONST, ICTYPE, IERR, IFILE, - IMODEL, IRES, IRESNO(2), ITYPE, JERR, LCOUNT, LINE, - NFILE, MAXCON, UCOUNT @@ -2292,7 +2292,7 @@ C---- Read in the restraint violations for all the models READ(IREC,460,IOSTAT=IERR) - (ACDIST(IFILE,ICONST), IFILE = 1, NFILE) - 460 FORMAT(57X,60F7.2) + 460 FORMAT(57X,100F7.2) C---- Extract the data for just those models that C have been selected by the user To apply this patch, go to the directory where vplot.f is located, save the above patch as vplot.f.patch, and run the following commands: patch < vplot.f.patch make Note that although this modification makes the analysis run on 100 structures, the length of IREC and the float in the format string are still limited. They may need to be increased if you want to analyse more structures at the same time. Posted in nmr, science Compiling and installing MolMol under Linux The molmol software that is available from the website of ETH Zurich does not compile on Ubuntu Linux without some changes. It seems that it is no longer supported by ETH Zurich, but can be downloaded from several other websites (just google it). I describe below what I needed to do to get it to compile and run on Ubuntu Linux. A summary of the installation procedure as described in the readme file follows for reference, but it does not seem to work without the changes mentioned below: tar xvzf molmol-2k.2.0-doc.tar.gz tar xfzf molmol-2k.2.0-src.tar.gz cd tiff-v3.4 ./configure make sudo make install cd .. cp makedef.lnx makedef ./INSTALL make sed -i 's/ksh/sh/g' molmol cd src/main strip molmol cp molmol ../../molmol.lnx cd ../.. ./molmol First, some additional packages are needed in Ubuntu: sudo apt-get install libmotif-dev x11proto-print-dev libxpm-dev libxt-dev (depending on your current ubuntu installation, you may need some other packages as well. Some error messages during the installation may give you an indication of what else is needed.) However, when I tried to compile molmol, the following error occured: /usr/bin/gcc -I../../tools/include -Dsqrtf=sqrt -Dexpf=exp -Dlogf=log -Dpowf=pow -Dsinf=sin -Dcosf=cos -Dtanf=tan -Dasinf=asin -Dacosf=acos -Datanf=atan -Datan2f=atan2 -Dfabsf=fabs -Dceilf=ceil -O2 -I../../tools/include -Dsqrtf=sqrt -Dexpf=exp -Dlogf=log -Dpowf=pow -Dsinf=sin -Dcosf=cos -Dtanf=tan -Dasinf=asin -Dacosf=acos -Datanf=atan -Datan2f=atan2 -Dfabsf=fabs -Dceilf=ceil -c -o RandNum.o RandNum.c In file included from /usr/include/math.h:94, from RandNum.c:29: /usr/include/bits/mathcalls.h:55: error: conflicting types for ‘acos’ /usr/include/bits/mathcalls.h:57: error: conflicting types for ‘asin’ /usr/include/bits/mathcalls.h:59: error: conflicting types for ‘atan’ /usr/include/bits/mathcalls.h:61: error: conflicting types for ‘atan2’ /usr/include/bits/mathcalls.h:64: error: conflicting types for ‘cos’ /usr/include/bits/mathcalls.h:66: error: conflicting types for ‘sin’ /usr/include/bits/mathcalls.h:68: error: conflicting types for ‘tan’ /usr/include/bits/mathcalls.h:101: error: conflicting types for ‘exp’ /usr/include/bits/mathcalls.h:110: error: conflicting types for ‘log’ /usr/include/bits/mathcalls.h:154: error: conflicting types for ‘pow’ /usr/include/bits/mathcalls.h:157: error: conflicting types for ‘sqrt’ /usr/include/bits/mathcalls.h:179: error: conflicting types for ‘ceil’ /usr/include/bits/mathcalls.h:182: error: conflicting types for ‘fabs’ make[4]: *** [RandNum.o] Error 1 This was easily solved by editing the makedef file as follows: change MISSFUNC = -Dsqrtf=sqrt -Dexpf=exp -Dlogf=log -Dpowf=pow \ -Dsinf=sin -Dcosf=cos -Dtanf=tan \ -Dasinf=asin -Dacosf=acos -Datanf=atan -Datan2f=atan2 \ -Dfabsf=fabs -Dceilf=ceil to # MISSFUNC = -Dsqrtf=sqrt -Dexpf=exp -Dlogf=log -Dpowf=pow \ # -Dsinf=sin -Dcosf=cos -Dtanf=tan \ # -Dasinf=asin -Dacosf=acos -Datanf=atan -Datan2f=atan2 \ # -Dfabsf=fabs -Dceilf=ceil At this point, I got a second error /usr/bin/sleep 2 make[6]: /usr/bin/sleep: Command not found This one is even easier to solve, by again editing makedef: change WAIT = /usr/bin/sleep 2 to WAIT = /bin/sleep 2 Another error occurs that requires us to make some changes in the source code. The error message is: /usr/bin/gcc -o molmol -I../../tools/include -I../../sg/include -I../../include -O2 MolMol.o MolInit.o ../../lib/libcip.a ../../lib/libcmd.a ../../lib/libui.a ../../lib/libgraph.a ../../lib/libio.a ../../lib/libpu.a ../../lib/libcalc.a ../../lib/libprim.a ../../lib/libdata.a ../../lib/libattr.a ../../lib/libfileio.a ../../lib/libos.a ../../sg/lib/libsg.a ../../tools/lib/libtools.a -L/usr/X11R6/lib -lXm -lXt -lX11 -lm -lc -lieee ../../lib/libos.a(GFile.o): In function raiseError': GFile.c:(.text+0x37): warning: sys_errlist' is deprecated; use strerror' or strerror_r' instead /usr/bin/ld: errno: TLS definition in /lib/libc.so.6 section .tbss mismatches non-TLS reference in ../../lib/libos.a(GFile.o) /lib/libc.so.6: could not read symbols: Bad value collect2: ld returned 1 exit status make[4]: *** [molmol] Error 1 The following changes to the source-code solved the problem. Add the following line to the file ./src/os/GFile.c #include <errno.h> add it for example under the line that says #include <linlist.h> Also change the following line (line 85 after the previous edit): msg = sys_errlist[errno]; to msg = strerror(errno); Molmol shoud compile now, but unfortunately the problems are not finished yet. After having stripped and copied molmol from ./src/main to molmol.lnx as in the original instructions, when we try to run it, it says: MOLMOL 2K.2 Version 2.1-2.6: Copyright (c) 1994-98 by Institut fuer Molekularbiologie und Biophysik, ETH Zurich Spectrospin AG, Faellanden, Switzerland Version 2K.2: Custom version by Reto Koradi, 1999-2003 using Motif/OpenGL unknown IO device This can be solved by editing the startup script (the file called molmol): Comment out the following lines (line 192-209): #if [ -n "$nograph" ]; then # MOLMOLDEV=TTY/NO #elif [ -n "$MOLMOLDEV" ]; then # true # already set #elif [ -n "$localdev" -a $display = ":0" -o display = ":0.0"$ ]; then # MOLMOLDEV=$localdev #elif [ -x$xdpy ]; then # xdpyout=$xdpy -d$display 2>&1 | egrep 'GLX|unable' # case $xdpyout in # *unable*) MOLMOLDEV=TTY/NO # nograph=y # continue;; # *GLX*) if [ -n "$glxdev" ]; then # MOLMOLDEV=\$glxdev # fi # continue;; # esac #fi

I hope this helps!

Posted in linux, science, ubuntu

Vertically align a table (or something) in vim

Just a short note to myself how to align a table in vim. I found the answer here. We start with the following example, a part of a LateX table (that was generated with an openoffice plugin). But the columns ("&" characters) are not aligned vertically, which is ugly.

      C1 & c3 & 0.30593 & C1 \\
C1X & c3 & 0.13418 & C1X \\
C2 & c3 & 0.08862 & C2 \\
C2X & c3 & -0.08100 & C2X \\


Of course, aligning the & characters can easily be done automagically (and this time I did not even have to write the script myself!)

• install it. details on how to install are on the plugin's page
• use it: select a block and type (in this case, we want to align the & character):
:Align &
• watch the magic
      C1   & c3 & 0.30593  & C1 \\
C1X  & c3 & 0.13418  & C1X \\
C2   & c3 & 0.08862  & C2 \\
C2X  & c3 & -0.08100 & C2X \\


Actually, this was just an example. The Align package has a special command to align LaTeX tables, which also aligns the newine commands \\. Just type \tt instead of :Align &.

(And in case you want to know: the table shows AMBER ff03 atom types and partial charges of an octylglucoside molecule, as calculated by AM1-BCC. But you are probably not interested :) )

Posted in latex, vim

Drawing structures of organic molecules in (Gentoo) Linux

In gentoo portage, there are several programs to draw structures of organic molecules. The best one is gchempaint. Not in portage, but also very interesting is bkchem. A Gentoo ebuild is available, but needs to be installed from a portage overlay. To install it, just use the commands below (where I assume you have a portage overlay correctly set up in /usr/local/portage).

# mkdir -p /usr/local/portage/sci-chemistry/bkchem
# cd /usr/local/portage/sci-chemistry/bkchem
# ebuild bkchem-0.12.5.ebuild digest
# emerge -va bkchem


If you want to make some final adjustments to the structure of the molecule, I suggest saving the molecule as an svg image, and making the adjustments with inkscape.

Posted in gentoo, linux, science

Some xmgrace tips

Xmgrace is a very nice piece of software to create publication quality figures. Even better is that it stores your data and graph layout options as plain text (which is especially nice in combination with subversion or perl scripts).

But some things are not very intuitive, which is why I keep a list of some useful possibilities below:

• Subscript, superscript
x-squared: x\S2\N
subscript: 3\s10\N
• Greek letters, example: theta
\f{Symbol}q\f{}
• Special symbols, example: Angstrom symbol
\cE\C
For other characters, look at this list: ascii table with low and high characters. Just use the character from the left column between \c and \C to produce the one from the right column. I highlighted the most interesting characters (for a scientist). The \c and \C option are listed as deprecated in the xmgrace manual., but what is the new way?. The new method to insert special characters in xmgrace is:
• Press ctrl-e while positioned in a text-edit field to bring up the font dialog box.
• Select the desired font from the drop-down list. You probably want to use Symbol because it contains many of the commonly used special characters.
• Click on the character you want to insert
• Saving the default settings for new graphs:
open xmgrace, make the desired settings, save them as:
~/.grace/templates/Default.agr
Unfortunately, this does not save the "print" settings, but see below.
• Setting the default printer to print to .png files with 300dpi:
create the file ~/.grace/gracerc.user and enter the following text:
HARDCOPY DEVICE "PNG" DEVICE "PNG" DPI 300
• Changing the definition of the default colors:
Just edit the lines that say
@map color 7 to (220, 220, 220), "grey"
in the saved file. Edit the default file (see above) if you wish to use the new colors everywhere from now on.

If you have more helpful hints, please post them in the comments, so that this blog post will become an interesting collection of tips that can be turned into a useful "cheat sheet".

Posted in linux, science