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 <tim@stripped>
> http://www.alaska-internet-solutions.com
> http://www.johnsons-web.com
