I'm running mySQL 5.0 and am trying to LOAD DATA INFILE a database that got handed to me on a CD-ROM, with not much else information. The extension of the file is "dbf" and so I presume it's a dBase file. I can open the file in OpenOffice-Calc and see that the first line contains field-information, and the remaining lines contain the data.
Anyone have experience with the parameters for the LOAD DATA INFILE and dBase format? I think, specifically, I need to figure out how the lines are terminated. I've tried "\n", "\r\n", and "\t" so far.
I have finally been able to get this dbf file to a workable state. The data is sitting in a MySQL(3.x) database on a linux box. I access it via MS-Access97 and a DSN that points to the database on linux box.
To get to that point, I had to put the dbf file on the linux-box and use a script from anubisnet.sourceforge.net to convert the dbf file to two files: a file of SQL commands and a file of csv-formatted data. For some reason, the SQL-command file didn't work. However I stripped the "CREATE TABLE" statement out of the SQL file and used that to create the table(there were 100+ fields and I didn't want to manually type them in!). I then used the following LOAD command:
LOAD DATA INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Approximately 1.2Million+ records later, and I have my database.
Now that I think of it, I might have been able to simply LOAD DATA with the dbf file..? But this was a learning process for me and I probably chose the difficult way this time :-)