On Mon, 28 May 2001, Pat Suwalski wrote:
> I'm doing a school project for which I'm to write a PHP script with MySQL
> that searches a dBase database.
> I've got my code planned out, but the problem right now seems to be that
> mySQL can't directly read dBase databases.
> It's a fairly large database (9 megs), and so I ask: do I need to convert it
> somehow or can I just read it directly?
Pat
I recently hacked some C code to convert dbase data into sql. I can't even
remember for which version of dbase it works, but you may use this as a
starting point. It translates character sets as well.
Thomas
---- snip ----
/* dbase2sql.c
* acts as a filter
* takes a dbase file from stdin
* and outputs sql code to stdout
* a tablename may be given as an argument (optional)
* compile with: me@box> gcc -o dbase2sql dbase2sql.c
* use as: me@box> cat dbasefile.dbf | dbase2sql > dbasefile.sql
* [ this hack is in the public domain ]
*/
#include <stdio.h>
#include <stdlib.h>
#define VERSION 1.0
#define HEADERLENGTH 32
#define BUFSIZE 4096
#define LINEBUFFER 4096
#define NCOLUMNS 200
const char *defaulttable="noname";
int offset[NCOLUMNS];
int fieldsize[NCOLUMNS];
unsigned char columntype[NCOLUMNS];
/*
* this is basically the file
* '/usr/lib/kbd/consoletrans/cp850_to_iso01.trans'
* as found on most Linux systems.
* The first value of each code pair is a character in the iso_8859-1
* character set. The second value is the corresponding code
* in the cp850 character set as used on many Windoze boxes.
* Initialization inittr(void) will bring this into a more useable form.
*/
int trtable[192] = {
0xa0, 0xff, 0xa1, 0xad, 0xa2, 0xbd, 0xa3, 0x9c, 0xa4, 0xcf,
0xa5, 0xbe, 0xa6, 0xdd, 0xa7, 0x15, 0xa8, 0xf9, 0xa9, 0xb8,
0xaa, 0xa6, 0xab, 0xae, 0xac, 0xaa, 0xad, 0xf0, 0xae, 0xa9,
0xaf, 0xee, 0xb0, 0xf8, 0xb1, 0xf1, 0xb2, 0xfd, 0xb3, 0xfc,
0xb4, 0xef, 0xb5, 0xe6, 0xb6, 0x14, 0xb7, 0xfa, 0xb8, 0xf7,
0xb9, 0xfb, 0xba, 0xa7, 0xbb, 0xaf, 0xbc, 0xac, 0xbd, 0xab,
0xbe, 0xf3, 0xbf, 0xa8, 0xc0, 0xb7, 0xc1, 0xb5, 0xc2, 0xb6,
0xc3, 0xc7, 0xc4, 0x8e, 0xc5, 0x8f, 0xc6, 0x92, 0xc7, 0x80,
0xc8, 0xd4, 0xc9, 0x90, 0xca, 0xd2, 0xcb, 0xd3, 0xcc, 0xde,
0xcd, 0xd6, 0xce, 0xd7, 0xcf, 0xd8, 0xd0, 0xd1, 0xd1, 0xa5,
0xd2, 0xe3, 0xd3, 0xe0, 0xd4, 0xe2, 0xd5, 0xe5, 0xd6, 0x99,
0xd7, 0x9e, 0xd8, 0x9d, 0xd9, 0xeb, 0xda, 0xe9, 0xdb, 0xea,
0xdc, 0x9a, 0xdd, 0xed, 0xde, 0xe8, 0xdf, 0xe1, 0xe0, 0x85,
0xe1, 0xa0, 0xe2, 0x83, 0xe3, 0xc6, 0xe4, 0x84, 0xe5, 0x86,
0xe6, 0x91, 0xe7, 0x87, 0xe8, 0x8a, 0xe9, 0x82, 0xea, 0x88,
0xeb, 0x89, 0xec, 0x8d, 0xed, 0xa1, 0xee, 0x8c, 0xef, 0x8b,
0xf0, 0xd0, 0xf1, 0xa4, 0xf2, 0x95, 0xf3, 0xa2, 0xf4, 0x93,
0xf5, 0xe4, 0xf6, 0x94, 0xf7, 0xf6, 0xf8, 0x9b, 0xf9, 0x97,
0xfa, 0xa3, 0xfb, 0x96, 0xfc, 0x81, 0xfd, 0xec, 0xfe, 0xe7,
0xff, 0x98};
int trlist[256];
void inittr(void) {
int i, k;
for(i=0; i<256; i++) {
trlist[i] = i;
for(k=1; k<192; k+=2) {
if(i == trtable[k])
trlist[i] = trtable[k-1];
}
}
}
int translate(int c) {
return(trlist[c]);
}
void stripdata(unsigned char *lbuf, int columns) {
int i, k;
unsigned char *begincol, *endcol, *colptr, *prptr;
for(i=1; i<columns; i++) {
switch(columntype[i]) {
case 'C':
begincol = lbuf + offset[i];
endcol = begincol + fieldsize[i];
colptr = endcol -1;
/* trailing blanks must be removed */
while((colptr > begincol) && (*colptr == ' ')) {
colptr--;
}
k = colptr - begincol + 1;
if(*begincol == ' ') {
printf("''");
} else {
printf("'%*.*s'", k, k, begincol);
}
if(i < (columns-1)) {
printf(",");
}
break;
case 'N':
begincol = lbuf + offset[i];
endcol = (begincol + fieldsize[i])-1;
colptr = begincol;
/* leading blanks must be removed */
while((colptr < endcol) && (*colptr == ' ')) {
colptr++;
}
k = (endcol - colptr) + 1;
if(*endcol == ' ') {
printf("NULL");
} else {
printf("%*.*s", k, k, colptr);
}
if(i < (columns-1)) {
printf(",");
}
break;
case 'D':
begincol = lbuf + offset[i];
endcol = begincol + fieldsize[i];
colptr = endcol -1;
/* trailing blanks must be removed */
while((colptr > begincol) && (*colptr == ' ')) {
colptr--;
}
k = colptr - begincol + 1;
if(*begincol == ' ') {
printf("NULL");
} else {
printf("'%*.*s'", k, k, begincol);
}
if(i < (columns-1)) {
printf(",");
}
break;
default:
break;
}
}
}
/* ---------------- Main begins here ----------------------- */
int main (int argc, char *argv[]) {
unsigned char *cbptr, *bufspace, *lsptr, *linespace ;
unsigned char *columnname[NCOLUMNS];
int count, columns, i;
int inchar;
int recordlength;
float ver;
const char *table;
count = 0;
columns = 0;
ver = VERSION;
inittr();
/* checking: */
/* for(i=1; i<256; i++) printf("%#2.2x\t%#2.2x\n",trlist[i],i); */
/* reserve some space */
if((bufspace = malloc(BUFSIZE)) == NULL) {
fprintf(stderr, "%s\n",
"Could not allocate requested columnname buffer space");
exit(1);
}
cbptr = bufspace;
if((linespace = malloc(LINEBUFFER)) == NULL) {
fprintf(stderr, "%s\n",
"Could not allocate requested linebuffer space");
exit(1);
}
/* first argument may be a tablename */
if(argc > 1) {
table = argv[1];
} else {
table = defaulttable;
}
while ((inchar = getchar()) != EOF) {
count++;
if((count == 1) && (inchar == 13))
break;
if(count == 1)
columnname[columns] = cbptr;
if(columns > 0) /* ignore first block */ {
/* copy column name to buffer */
if((count < 12) && (inchar != 0)) {
*cbptr++ = (unsigned char) inchar;
if((cbptr-bufspace)>(BUFSIZE-1)) {
fprintf(stderr, "%s\n",
"buffer overflow, increase BUFSIZE");
exit(1);
}
}
if(count == 12) {
*cbptr++ = '\0';
columntype[columns] = (unsigned char) inchar;
}
if(count == 17) {
fieldsize[columns] = inchar;
}
}
if (count == HEADERLENGTH) {
count = 0;
columns++;
if(columns > NCOLUMNS) {
fprintf(stderr, "%s\n",
"Sorry, number of columns exceeds maximum");
exit(1);
}
}
}
printf("# %s Version %2.1f\n", argv[0], ver);
printf("# SQL output generated from dbase input\n\n");
printf("DROP TABLE IF EXISTS %s;\n\n", table);
printf("CREATE TABLE %s (\n", table);
for(i=1; i<(columns); i++) {
printf(" %s", columnname[i]);
if(columntype[i] == 'C') {
printf(" varchar(%d) NOT NULL default ''",
fieldsize[i]);
}
if(columntype[i] == 'D') {
printf(" date default NULL");
}
if(columntype[i] == 'N') {
printf(" int default NULL");
}
if(i == (columns-1)) {
printf("\n");
} else {
printf(",\n");
}
}
printf(") TYPE=MYISAM PACK_KEYS=0 COMMENT='';\n\n");
/*
for(i=1; i<columns; i++) {
printf("%s \tType %1.1s\tLength %d\n",
columnname[i], &columntype[i], fieldsize[i]);
}
*/
recordlength = 0;
offset[0] = 1;
fieldsize[0] = 0;
for(i=1; i<columns; i++) {
offset[i] = offset[i-1] + fieldsize[i-1];
recordlength += fieldsize[i];
}
/* printf("%d\n", recordlength); */
/* Now output the data part */
count = 0;
lsptr = linespace;
while ((inchar = getchar()) != EOF) {
count++;
if(inchar != 26) /* ^Z */
*lsptr++ = (unsigned char) (translate(inchar));
if(count == (recordlength+1)) {
*lsptr = '\0';
count = 0;
lsptr = linespace;
printf("INSERT INTO %s VALUES (", table);
stripdata(linespace, columns);
printf(");\n");
}
}
free(bufspace);
free(linespace);
return 0;
}
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
