Instantly share code, notes, and snippets.

Embed
What would you like to do?
himalayandatabase - from dbf to json api

himalayandatabase

from a Visual FoxPro GUI to json api with datasette

http://himalayandatabase.com

The Himalayan Database is a compilation of records for all expeditions that have climbed in the Nepalese Himalaya. The database is based on the expedition archives of Elizabeth Hawley, a longtime journalist based in Kathmandu, and it is supplemented by information gathered from books, alpine journals and correspondence with Himalayan climbers.

The Himalayan Database is a Microsoft Visual Foxpro 9 program.


DISCLAIMER:
the license doesn't allow to redistribute the data. so run the following tutorial only locally for your personal use.


how to convert the data and publish with datasette

download the macos package

$ wget http://himalayandatabase.com/downloads/Himalayan%20Database%20Wine.zip
$ unzip Himalayan\ Database\ Wine.zip
$ cd Himalayan\ Database\ Wine

locate the DBF files

$ find . -name "*.DBF"
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/exped.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/filters.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/members.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/peaks.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/refer.DBF
./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/SETUP.DBF

identify the DBF version

sf is siegfried

$ sf "./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/exped.DBF"
---
siegfried   : 1.7.6
scandate    : 2018-01-06T11:28:15+01:00
signature   : default.sig
created     : 2017-10-03T15:30:10+11:00
identifiers :
  - name    : 'pronom'
    details : 'DROID_SignatureFile_V92.xml; container-signature-20170920.xml'
---
filename : './Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/exped.DBF'
filesize : 19662159
modified : 2017-12-03T22:12:04+01:00
errors   :
matches  :
  - ns      : 'pronom'
    id      : 'fmt/373'
    format  : 'FoxPro Database'
    version : '2.x'
    mime    :
    basis   : 'extension match dbf; byte match at 0, 44'
    warning :

convert from DBF to csv

Altough tika can be used to extract data from FoxPro Database, i got a cleaner result with dbfcsv

How to install:

$ go get -u github.com/gogap/godbf
$ git clone https://github.com/lancecarlson/dbfcsv.git
$ cd dbfcsv

Fix the import:

$ sed -i -s 's=code.google.com/p/go-dbf/godbf=github.com/gogap/godbf=' dbfcsv.go	

Build

$ go build dbfcsv.go
$ cp dbfcsv ~/bin
$ dbfcsv -h
-d string
	delimiter used to separate fields (default "|")
-h	display headers

Go to HIMDATA directory:

$ cd "./Himal 2.0.app/Contents/Resources/wineprefix/drive_c/winebottler/HIMDATA/"	

Convert DBF to csv

$ parallel "dbfcsv -h {}.DBF > {}.csv" ::: exped members peaks refer

$ du -h *.csv
3.4M	exped.csv
 15M	members.csv
 84K	peaks.csv
752K	refer.csv

Convert csv to sqlite with csvs-to-sqlite (field separator is |)

$ pip3 install csvs-to-sqlite
$ csvs-to-sqlite -s "|" *.csv himalayandatabase.db

install and run datasette

$ pip3 install datasette

$ datasette himalayandatabase.db
Serve! files=('himalayandatabase.db',) on port 8001
[2018-01-07 12:51:40 +0100] [3461] [INFO] Goin' Fast @ http://127.0.0.1:8001
[2018-01-07 12:51:40 +0100] [3461] [INFO] Starting worker [3461]

Example queries:

list of all expeditions with Renato Casarotto (json)
peaks higher than 8000mt

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment