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:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 09:55 p.m. Para: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] > Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. > Para: [EMAIL PROTECTED] > 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
