CODEWITHSUNDEEP
mysqlsql
Jacek Francuz
Jacek Francuz

Reputation: 2498

MySQL: Numbers format

I want to use mysql LOAD DATA to import CSV file. Numbers in my CSV have thousands separated by space, ie. 14 987 000. MySQL truncates it to 14 while importing. Is there any way to overcome this limitation without altering CSV source file?

P.S. I don't want to change field type from INT to VARCHAR.

Thank you

Upvotes: 2

Views: 123

Answers (2)

Shef
Shef

Reputation: 45589

Try:

REPLACE('14 987 000', ' ', '');

E.g.

LOAD DATA INFILE 'file.csv'
INTO TABLE t1(column1, column2, @number_column_value)
SET number_column = REPLACE(@number_column_value, ' ', '')

Upvotes: 3

bash-
bash-

Reputation: 6304

I don't believe so... You can write a script and to it for you.

If there is a way for MySQL to do it directly I'd like to know as well!

Upvotes: 1

Related Questions