I'm developing an application that needs to automatically export excel spreadsheets to the database. I'm using MySQL, but the spreadsheet data is not all well formatted the way the bank accepts it.
I have already set as utf8_general_ci , all tables with utf8 and did not solve. The problem is when I read a number of type 1.478,45 and it can not store in a variable of type double or float .
How to proceed?
In your favorite programming language, do something like:
var numero = foo; // onde "foo" é o número quebrado;
numero = (numero + '').replace('.', '').replace(',', '.');
Dude as our friend said before, in mysql he uses the American currency pattern that follows this pattern '15, 123.50 '.
Unlike the Brazilian standard that is '15 .123.50 'which is the opposite as you saw it.
But in BD mysql in addition to being American standard it does not count the houses of thousands thus getting '15123.50' .... so you should make a way to put this pattern in the language used by you !!
In PHP I do this as a friend:
valor = valor.replace(".", "") ; //IMPORTANTE - Tirar os pontos (a cada 3 dígitos, exemplo, 1.355.000,00)
valor = valor.replace(",", ".") ; // Depois remover a virgula da casa decimal por ponto, ficando no padrão d mysql