Home / ubuntu / Questions / 74863
Accepted
maniat1k
Asked: 2011-11-03 09:32:16 +0800 CST

Is an easy way to import .dbf file contents into MySQL in terminal

  • 772

I've got this files:

/referencias$ ls | grep .dbf
avenidas.dbf
barrios.dbf
borde.dbf
construcciones.dbf
espejo_de_agua.dbf
manchas_urbanas.dbf
manzanas.dbf
plazas.dbf

I want to do but in the terminal. any idea?

thanks to roadmr: this is how I did it.

dbf2mysql -vv -q -h localhost -P my.password -U root avenidas.dbf -d avenidas -c

dbf-file: avenidas.dbf - dBASE III w/o memo file, MySQL-dbase: avenidas, MySQL-table: test
Number of records: 60
Name             Length Display  Type
-------------------------------------
NMBRE_COMP          150     0     C
TIPO                  4     0     C
CODIGO               16     0     N
LOAD DATA LOCAL INFILE '/tmp/d2myCO7f7O' REPLACE INTO table test fields terminated by ',' enclosed by ''''

after that checked on MySQL.

# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.1.58-1ubuntu1 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE avenidas;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW tables;
+--------------------+
| Tables_in_avenidas |
+--------------------+
| test               |
+--------------------+
1 row in set (0.01 sec)

mysql> DESCRIBE test;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| NMBRE_COMP | varchar(150) | NO   |     | NULL    |       |
| TIPO       | varchar(4)   | NO   |     | NULL    |       |
| CODIGO     | int(11)      | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> \q
Bye
command-line

3 Answers

  1. Best Answer

    I found a "dbf2mysql" package:

    This program takes an xBase file and sends queries to an MySQL server to insert it into an MySQL table and vice versa.

    I haven't tried it myself but it looks like it will do what you need. To install (and since you seem to be handy with the terminal):

    sudo apt-get install dbf2mysql

    this is the manpage.

    • 7

  2. There is a dbf2mysql tool in the Ubuntu software repositories. I never used it, but from the description it seems to do what you want?

    • 1

  3. An important thing to note is that, as stated in the man page:

    mysql2dbf can't write MEMO files at this time.

    The other day, I converted one file using dbf2mysql and thought my problems with this were over. Unfortunately, I then spent the next 6 hours trying to resolve the issue of one of my largest files failing to be written into MySQL.

    There were no errors thrown at all, it simply wouldn't write. I went through a plethora of steps, including changing the buffer size, etc... to no avail, before realising that it was a MEMO file.

    I'm new to all of this, so in order to help other newbies out I'll let you in on the secret. The only way to know that you are dealing with a MEMO file, as produced by Visual FoxPro (as far as I know, anyway) is that it has 3 companion files ending with .cdx (which they all have), .dbt, and .fpt.

    • .dbf = The database file you are trying to convert, of course.
    • .cdx = A type of compound index file.
    • .dbt = Contains the MEMO text itself, but could not be opened with a text editor.
    • .fpt = Contains the MEMO header record.

    I was fortunate enough to have one with only 2 of the 3 companion files, .fpt and .cdx... which DID convert. So, through the process of elimination, it is the .fpt file, or MEMO header record, which is the offending file. Simply moving that file out of the directory doesn't work, unfortunately.

    I'm sure there is a way around it, unfortunately I don't know which yet. However, when (and I do mean 'when') I resolve this, I'll post it here.

    • 1