I´m new to MySQL but I hope this could help.
Please read below.
Regards from Costa Rica
De: John Mistler [mailto:johnmistler@stripped]
Enviado el: Martes, 06 de Julio de 2004 09:55 p.m.
Asunto: Re: DBF to MySQL
For some reason, the imported information showed up as garbled nonsense.
The file I was importing was an .xls file. Do you know if there is another
"TERMINATED BY" I should be using? If not, I wonder how I can find out?
I think you could export your xls file into a comma separated value file (CSV). In excel
File/Save as/CSV then with the Notepad you can delete the first line of the csv file (if
there are column headers in your xls file).
For example if your xls file is like
Year Number Date Total Unit ID Unit Name
2003 9253 05/05/2003 -36.365,00 3202 Unidad Programación
2003 9030 06/03/2003 1 4201 Depto. Consumidores
2003 9055 14/03/2003 3.000,00 7020 Proyecto de Distribución
2003 9798 26/08/2003 3.000,00 3210 Sección Transportes y Taller
your csv file will be:
Year;Number;Date;Total;Unit ID;Unit Name
2003;9055;14/03/2003;3.000,00;7020;Proyecto de Distribución Subterránea
2003;9798;26/08/2003;3.000,00;3210;Sección Transportes y Taller
If you delete the first row (headers) you will have only the raw data
Then you could save your file as a txt (but I think any extension should work)
LOAD DATA LOCAL INFILE 'myfile.txt' INTO TABLE mytable
FIELDS TERMINATED BY ';' ENCLOSED BY ''
LINES TERMINATED BY '\n';
I think you could left out the clause ENCLOSED BY '' because this is the default
Also, please note:
1) If LOCAL is specified, the file is read by the client program on the client host
and sent to the server.
If LOCAL is not specified, the file must be located on the server host and is read
directly by the server.
2) If you have generated the text file on a Windows system, you might have to use LINES
TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use
two characters as a line terminator. Some programs, such as WordPad, might use \r as a
line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.
The other question I have is: do I have to create a table within the MySQL
database with exactly the right number of columns ahead of time for the
import to work? - this is what I did. If so, is there a way to import info
from a .dbf or .xls file without knowing the structure of the table ahead of
As far as I know you need to know the table´s structure in advance.
From the mysql manual:
By default, when no column list is provided at the end of the LOAD DATA INFILE statement,
input lines are expected to contain a field for each table column. If you want to load
only some of a table's columns, specify a column list:
mysql> LOAD DATA INFILE 'persondata.txt'
The whole history is in:
on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at gchinchilla@stripped
> Try, for example:
> LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable
> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
> LINES TERMINATED BY '\n';
> -----Mensaje original-----
> De: John Mistler [mailto:johnmistler@stripped]
> Enviado el: Martes, 06 de Julio de 2004 04:51 p.m.
> Para: mysql@stripped
> Asunto: DBF to MySQL
> I am wanting to parse the info in a .dbf file (or .xls file for that matter)
> and place it in a table in a MySQL database. Is this something that I can
> do with the server side MySQL application, or do I need to figure out a way
> to do it on the client side? Any description of the method would be very
> For what it is worth, I am a Mac OSX.3 user.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1