DBFRead
Signatures
DBFRead(VARCHAR path);
DBFRead(VARCHAR path, BOOLEAN deleteTable);
DBFRead(VARCHAR path, VARCHAR tableName);
DBFRead(VARCHAR path, VARCHAR tableName, BOOLEAN deleteTable);
DBFRead(VARCHAR path, VARCHAR tableName, VARCHAR fileEncoding);
DBFRead(VARCHAR path, VARCHAR tableName, VARCHAR fileEncoding, BOOLEAN deleteTable);
Description
Reads the file specified by path as a dBase file and copies its contents into a new table tableName in the database.
A new column named PK, storing a primary key (INT value), is added. If the input .dbf has already a PK column then the added column is named PK2 (and so on).
Define fileEncoding to force encoding (useful when the header is missing encoding information) (default value is ISO-8859-1).
If:
the
tablenameparameter is not specified, then the resulting table has the same name as the dBase file.the
deleteTableparameter istrueand tabletableNamealready exists in the database, then tabletableNamewill be removed / replaced by the new one. Else (nodeleteTableparameter ordeleteTableequal tofalse), an error indicating that the tabletableNamealready exists will be throwned.
Warning on the input file name
When a tablename is not specified, special caracters in the input file name are not allowed. The possible caracters are as follow: A to Z, _ and 0 to 9.
Examples
In following example, we have a DBF file, which is stored here : /home/user/city.dbf. This file is structured as follow.
NAME ID
Vannes 56260
Theix 56251
Bréhan 56206
1. Case with path
CALL DBFRead('/home/user/city.dbf');
Returns the following table CITY. A column PK has been added.
PK |
NAME |
ID |
|---|---|---|
1 |
Vannes |
56260 |
2 |
Theix |
56251 |
3 |
Bréhan |
56206 |
2. Case with tableName
CALL DBFRead('/home/user/city.dbf', 'MYCITY');
Returns the table MYCITY
3. Case with fileEncoding
In the next two examples, we show what happens when we attempt to read a DBF file with the wrong encoding, and how to fix it.
Here UTF-8 doesn’t understand accented characters, so “Bréhan” is displayed as “Br”.
CALL DBFRead('/home/user/city.dbf', 'CITY', 'utf-8');
PK |
NAME |
ID |
|---|---|---|
1 |
Vannes |
56260 |
2 |
Theix |
56251 |
3 |
Br |
56206 |
To fix this problem, we specify the right encoding (iso-8859-1):
CALL DBFRead('/home/user/city.dbf', 'CITY', 'iso-8859-1');
PK |
NAME |
ID |
|---|---|---|
1 |
Vannes |
56260 |
2 |
Theix |
56251 |
3 |
Bréhan |
56206 |
4. Case with deleteTable
Load the city.dbf file
CALL DBFRead('/home/user/city.dbf');
→ the table CITY is created.
Now, load once again, using deleteTable = true
CALL DBFRead('/home/user/city.dbf', true);
→ the already existing CITY table is removed / replaced.
Now, load once again, using deleteTable = false
CALL DBFRead('/home/user/city.dbf', false);
→ Error message: The table "CITY" already exists.