I spent a few days to figure out how to make a simple conversion of a DBF file into a plain text file as comma separated values (CSV) or tab separated values (TSV) in a batch/command line way. I was almost setting up an OpenOffice.org server because it seamed to be the only packaged solution to read and convert DBFs.
Well, it easier than that. The secret lives in the XBase drivers for Perl Data Base Interface project. To install it on Fedora or Red Hat use this command:
yum install perl-DBD-XBase
If you use other Linux distributions, you may look for a package with a similar name.
For a simple file conversion, you don’t need to program in Perl because the perl-DBD-XBase package contains a command line tool for that: dbftool.pl. So to convert the mytable.dbf file into a more standard CSV file, do this:
dbfdump.pl --fs "," mytable.dbf > mytable.csv
If you want to use TAB instead of a comma to separate fields, while editing the command put the cursor between the double quotes, press Ctrl+V and then press the TAB key on your keyboard. This will fill the double quotes with a real (but invisible) TAB char.
9 thoughts on “Convert DBF to CSV on Linux with Perl and XBase”
I have just happened to work on some dbf dbase files too recently, and find out your post is very helpful (I plan to use perl dbi at the beginning). Here are some extra works I have done if you run windows, or other distributions of linux, such as opensuse, debian, ubuntu… hope is helpful for anyone who meet the same problem.
ATTENTION: make sure you use –nomemo switch when you run dbfdump, otherwirse you will get error message (missing dbt).
WINDOWS XP: install activeperl, and then install dbd-xbase package under ppm-shell. then run dbfdump.bat (mine is under c:\perl\site\bin)
dbfdump.bat –fs=:|” –nomemo mydebasefile.dbf
LINUX, install perl-dbd-xbase (suse), libdbd-xbase-perl (debian or ubuntu), and run
dbfdump.bat –fs=:|” –nomemo mydebasefile.dbf
for some version incompatible reason, you may get the error message under linux
prototype mismatch: sub Xbase::Base::O_BINARY () vs none at (eval 4). what I did is find the Fcntl.pm file for perl, and comment out the O_BINARY line, it works. If you need more information, google could help you more.
Commented out O_BINARY as suggested
[mlapier@mushroom]$ dbfdump.pl –fs “,” 20110205dailydat.dbf > 20110205dailydat.csv
Possible attempt to put comments in qw() list at /usr/lib/perl5/Fcntl.pm line 140.
Prototype mismatch: sub XBase::Base::O_BINARY () vs none at (eval 5) line 1.
Constant subroutine O_BINARY redefined at (eval 5) line 1.
I’m open for suggestions? 😉
I have no idea. I used it only once or twice.
I would guess that your DBF has binary fields which are incompatible with such conversion because CSV is text only.
If so, I would guess you need to use the XBase Perl API instead of using the vanilla dump tool.
Another guess is the encoding of text on your DBF file is incompatible with your environment so the tool is interpreting it as binary.
But again, these are only guesses.
Thanks, I have been wondering for years how to insert data from a .dbf into samba and linux server, this has helped considerably. Next step, using diff to check for changes and then to use sed or gawk to take certain fields from the changes for input into a lazy-admin-tools input file. I might need to use read/readline too.
Learning is fun.
Some work still needs to be done on the intelligent checking of input fields but so far I have tested the following and it worked.
Thanks again to this site, and linuxcommand.org, and man and info pages
Thanks wintel. Used the dbf_dump command from the libdbd-xbase-perl package on Ubuntu to good effect.
Hi. Thank you very much!! it’s helped me alot!
I’m on Ubuntu 14.04 LTS. I installed libdbd-xbase-perl via Synaptic. Then trying to do dbfdump, I got the following:
sudo apt-get install shapelib
snoopie2@snoopie2-Compaq-Presario-CQ40-Notebook-PC:~/special-tools/sage-6.2-i686-Linux$ sudo apt-get install shapelib[sudo] password for snoopie2:
升级了 0 个软件包，新安装了 2 个软件包，要卸载 0 个软件包，有 30 个软件包未被升级。
需要下载 59.2 kB 的软件包。
解压缩后会消耗掉 217 kB 的额外空间。
您希望继续执行吗？ [Y/n] y
获取：1 http://hk.archive.ubuntu.com/ubuntu/ trusty/universe libshp1 i386 1.2.10-7 [22.8 kB]
获取：2 http://hk.archive.ubuntu.com/ubuntu/ trusty/universe shapelib i386 1.2.10-7 [36.4 kB]
下载 59.2 kB，耗时 1秒 (40.1 kB/s)
Selecting previously unselected package libshp1:i386.
(正在读取数据库 … 系统当前共安装有 237520 个文件和目录。)
Preparing to unpack …/libshp1_1.2.10-7_i386.deb …
Unpacking libshp1:i386 (1.2.10-7) …
Selecting previously unselected package shapelib.
Preparing to unpack …/shapelib_1.2.10-7_i386.deb …
Unpacking shapelib (1.2.10-7) …
Processing triggers for man-db (188.8.131.52-1) …
正在设置 libshp1:i386 (1.2.10-7) …
正在设置 shapelib (1.2.10-7) …
Processing triggers for libc-bin (2.19-0ubuntu6) …
dbfdump [-h] [-r] [-m] xbase_file
-h: Write header info (field descriptions)
-r: Write raw field info, numeric values not reformatted
-m: Multiline, one line per field.
snoopie2@snoopie2-Compaq-Presario-CQ40-Notebook-PC:~/special-tools/sage-6.2-i686-Linux$ man dbfdump
It turned out you need to install shapelib to get dbfdump in Ubuntu 14.04.
I installed and used this but the problem I am facing it is that even though the dbf file is converted to csv file it does it without having the header. Meaning the csv file does not have the header that the dbf file was having. How can we resolve this?