List:General Discussion« Previous MessageNext Message »
From:Guillermo Chinchilla Zúñiga Date:July 7 2004 2:47pm
Subject:RE: DBF to MySQL
View as plain text  
John:

I´m new to MySQL but I hope this could help.
Please read below.

Regards from Costa Rica

-----Mensaje original-----
De: John Mistler [mailto:johnmistler@stripped] 
Enviado el: Martes, 06 de Julio de 2004 09:55 p.m.
Para: mysql@stripped
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?


A/
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;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 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)

Then

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
time?

A/

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:
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

Thanks,

John

on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at gchinchilla@stripped
wrote:

> 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
> welcome!
> 
> For what it is worth, I am a Mac OSX.3 user.
> 
> Thanks,
> 
> John
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
DBF to MySQLJohn Mistler7 Jul
RE: DBF to MySQLGuillermo Chinchilla Zúñiga7 Jul
  • Re: DBF to MySQLJohn Mistler7 Jul
    • Re: DBF to MySQLPaul DuBois7 Jul
    • Re: DBF to MySQLJoshua J. Kugler8 Jul
RE: DBF to MySQLGuillermo Chinchilla Zúñiga7 Jul
Re: DBF to MySQLJohn Mistler8 Jul
  • Re: DBF to MySQLJoshua J. Kugler8 Jul