Re: DBF to MySQL
I appreciated your first email, regardless of whether or not I was able to make it work. Any response is welcome! I am having some trouble with the final result using the CVS file: some of the content uses commas i.e. Chinchilla Zúñiga, Guillermo and gets split up between two fields. This whole process is pretty messy, but I'm sure I will find a solution. The tools I was directed to below at freshmeat.net are a little out of my league. I am a Macintosh user that programs in applescript, a little obj-C, and SQL. I don't know if I can even implement those classes. Thanks! John on 7/8/04 6:11 AM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: I did it because I wrongly assummed this was obviuos, but then I resend to Mr. Mistler a more precisely email. I´m sorry. Cheers Guillermo -Mensaje original- De: Joshua J. Kugler [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 07 de Julio de 2004 05:37 p.m. Para: [EMAIL PROTECTED] Asunto: Re: DBF to MySQL I'm sure it did...DBF and XLS files are not plain text. What Chincilla gave your was a bit of code for importing CSV files, after they had been exported from Excel. I'm not sure *why* he gave you that code. You can do one of two things. 1) Open up those files in Excel (it will also open DBF files) and export them as CSV, or 2) find a class and programmatically import them (search freshmeat.net for 'dbf'). If you need more info, holler. j- k- On Tuesday 06 July 2004 07:55 pm, John Mistler said something like: 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? 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? 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]
Re: DBF to MySQL
Hmm...that seems like a bug...if those commas are inside quotes, they should not be interpreted as field delimiters. You might want to check if there are any stray quotes elsewhere on the line that are messing up the interpretation. If not, then I would think that to be a bug. j- k- On Thursday 08 July 2004 09:23 am, John Mistler said something like: I appreciated your first email, regardless of whether or not I was able to make it work. Any response is welcome! I am having some trouble with the final result using the CVS file: some of the content uses commas i.e. Chinchilla Zúñiga, Guillermo and gets split up between two fields. This whole process is pretty messy, but I'm sure I will find a solution. The tools I was directed to below at freshmeat.net are a little out of my league. I am a Macintosh user that programs in applescript, a little obj-C, and SQL. I don't know if I can even implement those classes. SNIP LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBF to MySQL
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 NumberDate Total Unit ID Unit Name 20039253 05/05/2003 -36.365,003202 Unidad Programación 20039030 06/03/2003 1 4201 Depto. Consumidores 20039055 14/03/2003 3.000,00 7020 Proyecto de Distribución 20039798 26/08/2003 3.000,00 3210Secció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 LINESTERMINATED 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]
Re: DBF to MySQL
At 20:55 -0700 7/6/04, John Mistler wrote: 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? LOAD DATA will not, I think, read Excel spreadsheets that are in native format. You'll need to extract the data in some form that LOAD DATA can use. There are modules available for this task for various programming languages. There is one for Perl, for example. (If you get the recipes distribution from http://www.kitebird.com/mysql-cookbook/ and look in the transfer directory, you'll find a from_excel.pl script that you can use to yank out the data in tab-delimited format.) 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? The aforementioned recipes distribution has a guesstable.pl script (also in the transfer directory) that might be useful for this. It requires input in tab-delimited format, reads the information, and displays a CREATE TABLE statement that fits the data. 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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBF to MySQL
I'm sure it did...DBF and XLS files are not plain text. What Chincilla gave your was a bit of code for importing CSV files, after they had been exported from Excel. I'm not sure *why* he gave you that code. You can do one of two things. 1) Open up those files in Excel (it will also open DBF files) and export them as CSV, or 2) find a class and programmatically import them (search freshmeat.net for 'dbf'). If you need more info, holler. j- k- On Tuesday 06 July 2004 07:55 pm, John Mistler said something like: 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? 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? 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 -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBF to MySQL
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]
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? 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? 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]
Re: DBF to MySQL Problem?
El Martes 21 Agosto 2001 15:23, escribiste: Hello, I have downloaded your dbf2mysql-1.14.tar file from the MySQL.com site. I read through the README and still cannot seem to get this to work. Here is the error I'm getting: [info:admin/Desktop/dbf2mysql-1.14] root# make /usr/bin/cc -O2 -Wall -DVERSION=\1.14\ -I/usr/local/ mysql -c -o dbf2mysql.o dbf2mysql.c dbf2mysql.c:19: header file 'mysql.h' not found Do you the mysql-devel package installed? -- Alfredo J. Cole [EMAIL PROTECTED] Tegucigalpa, D. C., Honduras - 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
