List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 15 2003 6:14pm
Subject:Re: import 'decimal' data to int(11)
View as plain text  
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

Thread
import 'decimal' data to int(11)Tim Johnson15 Feb
  • Re: import 'decimal' data to int(11)Paul DuBois15 Feb