At 9:05 -0900 2/15/03, Tim Johnson wrote:
Hello All:
using MYSQL Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu
(i386) (RH 7.2)
I have data exported from an excel spreadsheet as tab delimited text.
Certain columns need to go to mysql table columns with a type of int(11).
(alternative datatypes will be considered)
The data from the spreadsheet have formats that include
enclosed quotes, dollar signs commas and are of a decimal
format. Using vim, I can safely remove the quotes and the
dollar signs:
as in "$2,456.12345" -> 2,456.12345
I am afraid that attempting to use vim to remove the
commas will have unpredictable side effects in other
columns.
1)Will 2,456.12345 import safely into an int(11) column?
If not, may an alternative datatype be recomended?
No, values with commas will not import correctly into any numeric column.
NOTE: the number that I want to have (using the example above)
would be 2456 rounded to the nearest 'whole number'.
Any help is appreciated. Pointers to documentation is welcome
as well. I hope I've provided enough info here.
Import the values into a character column, then use the REPLACE()
function to eliminate the unwanted characters. You can do this with the
dollar signs as well if you like.
mysql> CREATE TABLE t (num CHAR(30));
mysql> INSERT INTO t (num) VALUES('$2,456.12345');
mysql> SELECT num FROM t;
+--------------+
| num |
+--------------+
| $2,456.12345 |
+--------------+
mysql> UPDATE t SET num = REPLACE(num,'$','');
mysql> SELECT num FROM t;
+-------------+
| num |
+-------------+
| 2,456.12345 |
+-------------+
mysql> UPDATE t SET num = REPLACE(num,',','');
mysql> SELECT num FROM t;
+------------+
| num |
+------------+
| 2456.12345 |
+------------+
mysql> ALTER TABLE t MODIFY num INT;
mysql> SELECT num FROM t;
+------+
| num |
+------+
| 2456 |
+------+
If you're using LOAD DATA to import the file, you can probably
add a FIELDS ENCLOSED BY '"' clause, and then you won't even
need to strip the double quotes.
regards
--
Tim Johnson <[EMAIL PROTECTED]>
http://www.alaska-internet-solutions.com
http://www.johnsons-web.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
