Importing data from excel sheet
I have created table in mysql with 12 fields Field1Field2 Field3 Field4 Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
Re: Importing data from excel sheet
Hi, sam You can try to export table to file from Excel in CSV format. And then import data from this file to mysql. Something like this should help you: LOAD DATA INFILE 'yourtabledata.txt' INTO TABLE yourtable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n'; sam rumaizan wrote: I have created table in mysql with 12 fields Field1Field2 Field3 Field4 ……… Field12 I have an excel sheet with 12 columns and 150 rows. My question is how can I import all of the columns from the excel sheet to my table without losing any information. - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends. -- Andrew Dashin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I do something like this in mySQL...
I have a query that looks something like this: SELECT (c_o_w_inst_rev - c_o_w_estcost)/c_o_w_inst_rev FROM tmpGovernmentSummaries The problem is that sometimes c_o_w_inst_rev is 0 and dividing by zero returns a NULL. If c_o_w_inst_rev == 0 how can I return 0 for the SELECT above instead of NULL? Can I test in the SELECT if c_o_w_inst_rev is 0 and return 0 and if not do the math? Insight would be greatly appreciated :-) -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I do something like this in mySQL...
John Kopanas wrote: I have a query that looks something like this: SELECT (c_o_w_inst_rev - c_o_w_estcost)/c_o_w_inst_rev FROM tmpGovernmentSummaries The problem is that sometimes c_o_w_inst_rev is 0 and dividing by zero returns a NULL. If c_o_w_inst_rev == 0 how can I return 0 for the SELECT above instead of NULL? Can I test in the SELECT if c_o_w_inst_rev is 0 and return 0 and if not do the math? Insight would be greatly appreciated :-) http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_ifnull HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Deleting pseudo-duplicates...
Hello, I have a table with this values: idvaluetime 112200704042112 212200704042120 314200704042125 414200704042131 517200704042140 614200704042143 720200704042145 820200704042148 I want to delete the rows with the same value of the preceding one, the spected result looks like this: idvaluetime 1 12200704042112 3 14200704042125 5 17200704042140 6 14200704042143 7 20200704042145 but I don't have any idea how make this select... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting pseudo-duplicates...
Vicente, A simple fast way is via an exclusion join: delete t1 from tbl t1 left join tbl t2 on t1.value=t2.value and t1.idt2.id where t2.id is not null; To understand how that works, notice that the query select t1.id from tbl t1 left join tbl t2 on t1.value=t2.value and t1.idt2.id where t2.id is null; returns row pairs matched on value values with minimum id values; therefore the query select t1.id from tbl t1 left join tbl t2 on t1.value=t2.value and t1.idt2.id where t2.id is not null; returns all remaining rows, and those are the ones deleted by the query given. PB Vicente Lopez wrote: Hello, I have a table with this values: idvaluetime 112200704042112 212200704042120 314200704042125 414200704042131 517200704042140 614200704042143 720200704042145 820200704042148 I want to delete the rows with the same value of the preceding one, the spected result looks like this: idvaluetime 1 12200704042112 3 14200704042125 5 17200704042140 6 14200704042143 7 20200704042145 but I don't have any idea how make this select... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I do something like this in mySQL...
Well, maybe you want to read up on isnull() and case (..) in the manual. It's in there, somewhere. mysql SELECT CASE 1 WHEN 1 THEN 'one' - WHEN 2 THEN 'two' ELSE 'more' END; - 'one' mysql SELECT CASE WHEN 10 THEN 'true' ELSE 'false' END; - 'true' mysql SELECT CASE BINARY 'B' - WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; - NULL IF(expr1,expr2,expr3) If expr1 is TRUE (expr1 0 and expr1 NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used. mysql SELECT IF(12,2,3); - 3 mysql SELECT IF(12,'yes','no'); - 'yes' mysql SELECT IF(STRCMP('test','test1'),'no','yes'); - 'no' IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. mysql SELECT IFNULL(1,0); - 1 mysql SELECT IFNULL(NULL,10); - 10 mysql SELECT IFNULL(1/0,10); - 10 mysql SELECT IFNULL(1/0,'yes'); - 'yes' -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 On Sat, April 7, 2007 20:19, John Kopanas wrote: I have a query that looks something like this: SELECT (c_o_w_inst_rev - c_o_w_estcost)/c_o_w_inst_rev FROM tmpGovernmentSummaries The problem is that sometimes c_o_w_inst_rev is 0 and dividing by zero returns a NULL. If c_o_w_inst_rev == 0 how can I return 0 for the SELECT above instead of NULL? Can I test in the SELECT if c_o_w_inst_rev is 0 and return 0 and if not do the math? Insight would be greatly appreciated :-) -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]