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]
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]
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: 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
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
