[ excel to mysql ]
Hi! How i do for transfer data from excel file to mysql? Some example? Thanks
Re: [ excel to mysql ]
Save the excel file as a CSV text file, then use LOAD DATA INFILE command to import. See the mysql manual for usage and example. On May 8, 2008, at 4:49 AM, Lord Gustavo Miguel Angel wrote: Hi! How i do for transfer data from excel file to mysql? Some example? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
excel and Mysql?!
hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers
Re: excel and Mysql?!
Sure, it's usually fairly straightforward to transfer data. You can export to a CSV or other delimited file, then use MySQL's LOAD DATA INFILE command to read it in. See http://dev.mysql.com/doc/refman/5.0/en/load-data.html I haven't ever tried creating tables from something in Excel automagically; I have always created the tables by hand. Dan On 10/18/06, Roberto William Aranda-W Roman [EMAIL PROTECTED] wrote: hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: excel and Mysql?!
If you have the Max Binaries of MySQL, do the following: 1) Export Excel Data to a CSV file (mydata.csv) 2) Goto MySQL and create table, without indexes to accept the data (CREATE TABLE myImportCSV) 3) Change the table layout to accept CSV i.e., ALTER TABLE myImport ENGINE = CSV; Please note that CSV tables in MySQL do not support indexes. At this point myImport.csv in the datadir of MySQL is a zero-length file. 4) Goto datadir of MySQL installation and put the contents of mydata.csv into myImport.csv. 5) Run this SQL statement: ALTER TABLE myImport ENGINE = MyISAM; 6) Create any necessary indexes for the MyISAM table myImport. If you do not have Max Binaries, I hope you have Microsoft Access. Then, you can try the following: 1) Download MyODBC onto your PC and install it. 2) Goto Administrative Tasks and goto 32-bit ODBC in Control Panel 3) Set up ODBC entry to point to your MySQL database of choice 4) Goto Microsoft Access and create a new table. 5) Create a link table entry to the Excel spreadsheet 6) Create a link table entry to MySQL table Make sure the MySQL userid and password has full rights to insert data Make sure the MySQL table has a primary key. If it does not have one, make one. Microsoft Access requires all outside databases (i.e., MySQL, Oracle, SQL Server, etc) to have a primary key. 7) Create an Append Query to the MySQL table from the Excel Spreadsheet. If you do not have Microsoft Access, here is a last resort: 1) Export Spreadsheet Pipe Delimited to myImport.txt 2) Create a table to load on MySQL (with indexes if necessary) 3) Use LOAD DATA INFILE if myImport.txt is to be on the server or LOAD DATA LOCAL INFILE if myImport.txt is loaded from your PC Note: If using LOAD DATA LOCAL INFILE, make sure you use forward slashes i.e., C:/data/myImport.txt I hope this helps !!! - Original Message - From: Roberto William Aranda-W Roman [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 8:36:41 AM GMT-0500 US/Eastern Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: excel and Mysql?!
I do it all of the time, and once you get the hang of it this is quite easy. The simple-minded way is to generate one SQL statement per row of the original spreadsheet (assuming that each row of the spreadsheet corresponds to a row in the data base table). 1. Create a new worksheet, if needed. Position yourself at cell A1. 2. Using Excel functions, build an entry like =CONCATENATE(INSERT INTO mytable SET col1 = ,'Sheet1'!A1,, col2 = ,'Sheet1'!A2, ...) You can use Excel's click to build the cell links to the other spreadsheet, you don't have to type them by hand. 3. Copy the formula down the range you need. 4. Save the worksheet containing the MySQL commands as a text file. 5. From the mysql command line interface, simply USE the right data base and then source the text file you created. Alternatively, you can insert the USE statement into your spreadsheet (adjusting the row numbers accordingly) or directly into your text file, and redirect the input file for mysql on the command line. Once you get the hang of it, this is all simpler than it sounds. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: excel and Mysql?!
As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: ... SET col1 = ', 'Sheet1'!A1, ', ...) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: excel and Mysql?!
Always being the last to input, there are lots of other database tools out there that let you do this. One in particular is DBTools Professional (which is what I use). You can ADO IMPORT Excel, MS-Access, and others like FoxPro and PostgreSQL. Another is Database Workbench, which one of the guys on this list works on. However, if free is the way to go, then MyODBC and the format below (which I will start using now =) ) are the ways to do it. My 0.02... J.R. cheap is good, free is better From: Jerry Schwartz [EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 10:17 AM To: 'Roberto William Aranda-W Roman' [EMAIL PROTECTED], 'MySQL List' mysql@lists.mysql.com Subject: RE: excel and Mysql?! As usual, Dan's suggestion is better than mine, especially for large amounts of data (I usually work with perhaps 100 rows, at most). When you save a spreadsheet as a text file, the columns will be delimited by tabs by default. Also, if you go with my suggestion you should probably surround any text values with an extra pair of single quotes: ... SET col1 = ', 'Sheet1'!A1, ', ...) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Roberto William Aranda-W Roman [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 18, 2006 8:37 AM To: MySQL List Subject: excel and Mysql?! hello . anybody knows how to export from excel to mysql in order to create or fill a database table?? tanks for your help and patience cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test thats what i did ... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+-+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+-+-++ || NULL | NULL| NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL| NULL| NULL| NULL | ++--+-+-+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. cheers thats what i did ... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
Looks like i have solved the issue, thanks guys for the help this did the trick mysql load data infile '/home/dlubowa/test' into table bush fields terminated by ',' (clients,location,service,PhoneNo,contact,emails); -Z On Thursday 09 December 2004 15:54, David Ziggy Lubowa wrote: On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+ -+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+ -+-++ || NULL | NULL| NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL| NULL| NULL| NULL | ++--+-+ -+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. cheers thats what i did ... Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David Ziggy Lubowa wrote: On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+-+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+-+-++ || NULL | NULL| NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL| NULL| NULL| NULL | ++--+-+-+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. - add FIELDS TERMINATED BY ',' ENCLOSED BY '' to your LOAD DATA INFILE - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David, Ian's response looks fine, I would just add 'OPTIONALLY' to the 'ENCLOSED ' parameter: mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' . This may not work if your entire record is wrapped in quotes as indicated in your email. In this case you are going to need to cure your import file by removing the first and last quote that wraps your record. I hope this helps. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP there are just two issues that I would look at if his solution - Original Message - From: Ian Sales [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Patrick Sherrill [EMAIL PROTECTED] Sent: Thursday, December 09, 2004 9:53 AM Subject: Re: Excel 2 mysql David Ziggy Lubowa wrote: On Thursday 09 December 2004 14:33, David Ziggy Lubowa wrote: On Wednesday 08 December 2004 05:07, Patrick Sherrill wrote: David, Please provide the complete LOAD DATA INFILE command you used. mysql -e LOAD DATA INFILE '/home/dlubowa/clients.csv' INTO TABLE bush -p test this is test output that is in my test table, this is how my info is mysql select * from bush; ++--+-+-+-++ | clients| location | service | PhoneNo | contact | emails | ++--+-+-+-++ || NULL | NULL | NULL| NULL| NULL | | CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 | NULL | NULL | NULL| NULL| NULL | ++--+-+-+-++ sorry for the distorted info, but basically the CRS, Nsambya, HSDN 350 , 041-267733 / 077-7 are all under clients field, Yet the info is supposed to be distributed to all the columns of the table. What could i be doing wrong here. - add FIELDS TERMINATED BY ',' ENCLOSED BY '' to your LOAD DATA INFILE - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- 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: Excel 2 mysql
Ziggy, I'm not sure what you need help with. If you want to get data into MySQL from Excel, consider saving the data (I assume it is a table) as a CSV then using the LOAD DATA command to bring the data into a temporary table in MySQL. Take a look at the command in the help pages. Regards, Adam David Ziggy Lubowa Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z Regards, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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: Excel 2 mysql
David, In the load data infile command you can specify the delimiter character (see manual). It is a comma in CSV files, but I think it defaults to the TAB character in load data infile. You can specify the delimiter to be comma in the load command. However, I prefer to export from Excel as TAB separated, as you don't have to mess with the load data command. Also, more importantly, data fields often contain commas which screws everything up, whereas TABS are much less common in Excell data fields. Hope this helps, Andy -Original Message- From: David Ziggy Lubowa [mailto:[EMAIL PROTECTED] Sent: 08 December 2004 23:55 To: Eric Bergen Cc: [EMAIL PROTECTED] Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David, Please provide the complete LOAD DATA INFILE command you used. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: David Ziggy Lubowa [EMAIL PROTECTED] To: Eric Bergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 08, 2004 6:54 PM Subject: Re: Excel 2 mysql On Tuesday 07 December 2004 20:02, Eric Bergen wrote: The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . All help is highly appreciated. -Z MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
David Ziggy Lubowa wrote: cheers guys, i have managed to change my excel file into a .csv and done a LOAD DATA INFILE and dumped the data in the mysql db the only problem though that all the data is going into one field in the table yet it is comma separated with the exact columns. How can i solve this . - if you're copying the csv file between a Windows and a *NIX box, you might have issues with carriage-return characters. Try running dos2unix against the file to convert it. - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Excel 2 mysql
Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Excel 2 mysql
[snip] i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. [/snip] Want simple? Look at the SELECT INTO OUTFILE syntax. This will give you a tab delimited file that you can then open in Excel. Quick it is, pretty it isn't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
Jay Blanchard wrote: [snip] i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. [/snip] Want simple? Look at the SELECT INTO OUTFILE syntax. This will give you a tab delimited file that you can then open in Excel. Quick it is, pretty it isn't. - alternatively, from the command line run a SQL script containing SELECT CONCAT_WS(',', ...column names...) etc., and pipe the output into a .csv file. Excel will happily import the data. There'll be no column headings, though. - ian -- +---+ | Ian Sales Database Administrator | | | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
The easiest way to turn excel into MySQL is to have excel save the file as a .csv (comma separated values) file and use the mysqlimport utility or a load data infile query to insert it into a table in MySQL. -Eric On Tue, 7 Dec 2004 16:02:23 -0800, David Ziggy Lubowa [EMAIL PROTECTED] wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.bleated.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Excel 2 mysql
From Excel, save the file as a CSV (comma separated values) use either mysql load data infile in an insert query or use phpmyadmin to import the csv into a table in your database. Regards Assey On Tue, 7 Dec 2004, David Ziggy Lubowa wrote: Hey guys , i know this has been discussed but looking through the archives i have more less hit a stand still , i have one big excel file which has data i would like to extract., Now i am no expert in perl neither am i in php , i have looked at some tools like navicat but i dont see where you actually start the app , if that is what happens. Any help is highly appreciated. cheers -Z -- 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: Import from excel to MYSQL
I save it as a CSV and stick it through phpmyadmin...it can be a bit clumsy (getting the delimiters to agree, mostly, which isn't exactly a monumental hurdle) but it works great unless your sheet is bloody massive and it runs past the timeout. I guess it'd be equally simple to do a LOAD DATA INFILE on the aforementioned CSV...again, make sure the delimiters are what's expected and it should work perfectly. On Tue, 2003-11-11 at 21:20, Lists - Dustin Krysak wrote: Is there an easy way to get an excel spread sheet imported into a MYSQL database? Any links to a tutorial? Thanks in advance! Dustin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import from excel to MYSQL
if you dont want to write program: use MsAccess. thx öö - Original Message - From: Lists - Dustin Krysak [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 7:20 AM Subject: Import from excel to MYSQL Is there an easy way to get an excel spread sheet imported into a MYSQL database? Any links to a tutorial? Thanks in advance! Dustin -- 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]
Import from excel to MYSQL
Is there an easy way to get an excel spread sheet imported into a MYSQL database? Any links to a tutorial? Thanks in advance! Dustin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import from Excel to MySQL
I'm a relative newbie to MySql, and I've got a small project I'm working on and hopefully you folks can either point me in the proper direction or give me a little help. I have multiple spreadsheets in Excel format and in .csv format too, that I would like to possibly import to a mysql database. All of the excel and csv files have is one huge colum and they are only 1 cell on each line. An estimate of 7k-8k domain names I need to run a whois on. Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name. Any help would be greatly appreciated! ~Phil
RE: Import from Excel to MySQL
[snip] Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name. [/snip] Export the Excel to csv, then import the csv to MySQL. There are classes for PHP that will read the binary data directly from Excel, but they are costly...i.e. http://www.web-aware.com/biff/ HTH! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import from Excel to MySQL
I am also working on something like this but am taking a different approach. Basically I'm creating an Excell Add-in in VBA that adds a button to the Excel toolbar. When a user clicks it I take the highlighted rows and columns and verify that they are in the proper format and everything looks good then import them into the database with the MySQL ODBC driver and VBA. It's more work up front than your solution but for the PHB it's a lot more comfortable than learning a new custom application and process. To them it's just Office ;) -Original Message- From: Jackson Miller [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 12:16 PM To: Jay Blanchard; Phil Perrin; [EMAIL PROTECTED] Subject: Re: Import from Excel to MySQL I just had to solve this problem. I needed to be able to have users load an excel file from a browser and have it import. To do this I used perl to have the excel convert to csv on the server. This was easy to accomplish using the CPAN module ParseExcel. The module comes with a sample script that basically does the trick. I am planning to use perl2exe to create an executable of my xls2csv perl script. But the perl script is working fine. I use exec() to call the perl script. -Jackson On Tuesday 19 August 2003 10:49, Jay Blanchard wrote: [snip] Basically I need a way to import them from the Excel sheet to the database so at that point I can manipulate the data and use a php script of some sort to run a whois after extracting the domain name, and then return the results to the database and have it attached to the domain name. [/snip] Export the Excel to csv, then import the csv to MySQL. There are classes for PHP that will read the binary data directly from Excel, but they are costly...i.e. http://www.web-aware.com/biff/ HTH! -- jackson miller cold feet creative 615.321.3300 / 800.595.4401 [EMAIL PROTECTED] cold feet presents Emma the world's easiest email marketing Learn more @ http://www.myemma.com -- 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: excel to mysql
to load files into mysql: create a table: create table newtable (column1 int(10),column2 varchar(250)); import the data: load data infile '/path/to/file/file.csv' into table newtable fields terminated by ',' lines terminated by '\n' Alia Mikati wrote: Hello everybody I hope u can help me with this. I want to export tables from excel to mysql and i saved the file as .csv then clicked on browse to get Location of the textfile. But it doesnt work. I get for expl: SQL-query : 3,Technical 4,Technical 7,Technical 15,Technical 20,Technical 26,Technical 32,Technical MySQL said: You have an error in your SQL syntax near '3,Technical 4,Technical 7,Technical 15,Technical 20,Techn' at line 1 Plz how can I do it? Thx - 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 -- Richard Dobson Database Administrator MRC Bright Study Clinical Pharmacolgy St Bartholomew's and the Royal London School of Medicine and Dentistry Charterhouse Square London EC1M 6BQ Tel: 020-7882-5670 Mob: 07711-522926 Fax: 020-7882-5672 [EMAIL PROTECTED] - 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
excel to mysql
Hello everybody I hope u can help me with this. I want to export tables from excel to mysql and i saved the file as .csv then clicked on browse to get Location of the textfile. But it doesnt work. I get for expl: SQL-query : 3,Technical 4,Technical 7,Technical 15,Technical 20,Technical 26,Technical 32,Technical MySQL said: You have an error in your SQL syntax near '3,Technical 4,Technical 7,Technical 15,Technical 20,Techn' at line 1 Plz how can I do it? Thx - 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
Excel to MySQL
How do i import MS Excel Data into MySQL Tables?? T. Edison jr. = Rahul S. Johari (Director) ** Abraxas Technologies Inc. Homepage : http://www.abraxastech.com Email : [EMAIL PROTECTED] Tel : 91-4546512/4522124 *** __ Do You Yahoo!? Yahoo! Movies - coverage of the 74th Academy Awards® http://movies.yahoo.com/ - 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
Re: Excel to MySQL
At 3:58 -0800 3/27/02, Thomas Edison Jr. wrote: How do i import MS Excel Data into MySQL Tables?? One way: save it as a tab-delimited file, then import it with mysqlimport. If you don't want to mess around pointing and clicking to save an Excel file as a tab-delimited file, try using this Perl script: #! /usr/bin/perl -w # from_excel.pl - read Excel spreadsheet, write tab-delimited, # linefeed-terminated output to the standard output. use strict; use Spreadsheet::ParseExcel::Simple; @ARGV or die Usage: $0 excel-file\n; my $xls = Spreadsheet::ParseExcel::Simple-read ($ARGV[0]); foreach my $sheet ($xls-sheets ()) { while ($sheet-has_data ()) { my @data = $sheet-next_row (); print join (\t, @data) . \n; } } exit (0); Run it like this: from_excel.pl excel_file.xls excel_file.txt Spreadsheet::ParseExcel::Simple requires a few other modules that'll need to be installed first. T. Edison jr. = Rahul S. Johari (Director) ** Abraxas Technologies Inc. Homepage : http://www.abraxastech.com Email : [EMAIL PROTECTED] Tel : 91-4546512/4522124 *** - 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
Sending data from Excel to MySQL
I was wondering if someone could direct me to some resources where I could teach myself how to export data from Excel to a MySQL database. I have a number of spreadsheets I use for data collection, and have recently set up a MySQL database to stash the data in. I've managed to figure out how to copy/paste blocks of data into an HTML form and have PHP stick the data into the database. I'm looking for a way I can do it from within Excel now, since there are more blocks of data I need to store which are scattered throughout the spreadsheet. I'd like to automate the process a little more and go beyond the copy/paste thing. I've looked at MyODBC a little bit, but not enough to see how I can use it for this task (one of the things on my to do list). I expect I'll end up learning some VB for doing this. Many Thanks Eugene -- To put my contact info into your Palm device, click here: http://signature.coola.com/?[EMAIL PROTECTED] Personal Signature Coolet - Eugene Mah, M.Sc., DABR [EMAIL PROTECTED] Medical Physicist/Misplaced Canuck[EMAIL PROTECTED] Department of Radiology For I am a Bear of Very Little Medical University of South Carolina Brain, and long words Bother Charleston, South Carolina me. Winnie the Pooh http://home.netcom.com/~eugenem/ PGP KeyID = 0x1F9779FD, 0x319393F4 PGP keys available on request ICQ 3113529 O- - - 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
Re: Sending data from Excel to MySQL
At 3:31 PM -0400 9/11/01, Eugene Mah wrote: I was wondering if someone could direct me to some resources where I could teach myself how to export data from Excel to a MySQL database. I have a number of spreadsheets I use for data collection, and have recently set up a MySQL database to stash the data in. I've managed to figure out how to copy/paste blocks of data into an HTML form and have PHP stick the data into the database. I'm looking for a way I can do it from within Excel now, since there are more blocks of data I need to store which are scattered throughout the spreadsheet. I'd like to automate the process a little more and go beyond the copy/paste thing. I've looked at MyODBC a little bit, but not enough to see how I can use it for this task (one of the things on my to do list). I expect I'll end up learning some VB for doing this. You might want to have a look at DBTools, which can read Excel files and dump the contents into MySQL for you. http://dbtools.vila.bol.com.br/ Many Thanks Eugene -- Paul DuBois, [EMAIL PROTECTED] - 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
Re: MS Excel to MySQL upload macro?
At http://www.icarz.com/mysql/index.html you can download a VB API that you can also include in excell. It's an API that connects directly to mysql. The API (MyVbQL.dll) is free and comes with an example program in VB. Regards, Tadej - Original Message - From: Matt Wagner [EMAIL PROTECTED] To: Craig Westerman [EMAIL PROTECTED] Cc: MySQL-List [EMAIL PROTECTED] Sent: Saturday, August 18, 2001 6:17 AM Subject: Re: MS Excel to MySQL upload macro? Craig Westerman writes: I have data I create daily in MS Excel. I need to insert this data into MySQL data base on my web server every night. Does anyone here have a VB technique to upload to MySQL from Excel via a macro? Hi, There is an 'excel2mysql' perl script at the bottom of the 'Converters' section of our contributed software page: http://www.mysql.com/downloads/contrib.html Regards, Matt -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Matt Wagner [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Herr Direktor /_/ /_/\_, /___/\___\_\___/ Hopkins, Minnesota USA ___/ www.mysql.com - 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 - 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
MS Excel to MySQL upload macro?
I have data I create daily in MS Excel. I need to insert this data into MySQL data base on my web server every night. Does anyone here have a VB technique to upload to MySQL from Excel via a macro? Thanks Craig [EMAIL PROTECTED]