Monthly Archives: October 2008

Import .dbf File Contents into MySql

I had a bunch of .dbf (source was Lotus Approach) files, at which I wanted to shoot SQL-like queries to generate some meaningful reports. So there were 2 tasks to be accomplished:

  1. Read the data into a tool or database and
  2. formulate and perform the queries to generate results to base your reports on.

As of opening and saving, I first tried MS Access. But some of the tables could not be imported (reasons unknown). Then I tried to open those files with MS Excel. It worked, but I had more than 65536 records per table, so I could not actually use Excel. After some investigation I came across dbftools which promised to help me converting the data into a text-based format.

I simply unpacked the C source and typed ‘make’ from the console and I had a working dbf2txt binary. I created a directory with all the dbf-files in it and hacked together the following simple shell script: convert.sh

#!/bin/bash
for i in data/*.dbf
do
dbf/src/dbf2txt -v "$i" > "$i.txt";
done

It supplied me with all txt-files each containing a table with fieldnames in line 1 and the data in tab-separated format. Nice!

To get this data into MySql, I hacked this little tab2sql script which converts the txt-files into SQL statements. It is called like this:

$ php tab2sql.php data/my_table.dbf.txt > sql/my_table.dbf.txt.sql;

You can then dump all sql-files in the sql-dir into MySql via:

$ for dir in data/*.sql; do mysql dbf_import -h localhost -u root < “${dir}”; done;

Thanks Frank Koormann for the dbftools!

Sidenote: If you have big tables and you plan to work with joins… Do not forget to set field-types and indices properly after the import! This could otherwise stress your machine.

Subversion: Revert to a Previous Revision

On one of my current projects, I had the problem that I had done several commits to the repository and suddenly changed my mind and wanted to go back to a certain point in the revison history where I started with my changes. How would you do that?

Let’s say you started your changes at revision 901 with your first commit and the current revision (HEAD) is 910.

To your working-copy you could just do:

svn up -r 900 .

But that would bring back the unwanted changes to you as soon as you update again and would not affect other people working on the code when they update their workig copies to the current revision. What you really have to do in this case is to ‘merge back’ your working-copy and then commit the changes with a comment:

svn up;
svn merge -r HEAD:900 .;
svn commit -m "Reverted back to rev 900. Sorry, changed my mind.";

Be careful! This only works if ALL revisions (901-910) have been committed by yourself.

Useful Linux Commands 10/2008

(1) Finds files in generated documentation, containing <span class=”field”>webservice:</span> and writes a file containing a clickable list of links to those pages:
~/sites/html/phpdoc_all_global$ find -type f -print0 | xargs -0 grep -li ‘<span class=”field”>webservice:</span>’ | while read in; do echo “<a href=”$in”>$in</a>”; done > clickable_list_page.html

(2) A for-loop on the commandline. The * equals all files and dirs in current directory (try “echo *”). Of course you can replace the ‘echo $dir’ with whatever command you like using $dir as loop-variable.:
for dir in *; do echo $dir; done

(Thanks Alexander)

(3) A simlpe piped grep, which displays the files (-l) containing the string “<FormularObjectGenerator”. And since I was searching recureively (-r) in a svn working copy, I did not want to see all the double filepaths containing “.svn” I filtered by excluding (-v) this pattern from the output.

grep -lr “<FormularObjectGenerator” ./ | grep -v “.svn”