CODEWITHSUNDEEP
phpmysql
RuFFCuT
RuFFCuT

Reputation: 317

Keep Decimal Place When Importing CSV to MySQL

I'm importing a CSV directly into my database via PHPmyadmin. Everything has gone smoothly until I checked a column that has decimal spaces. Instead of keeping the decimal places in tact it simply removed everything after the ', & .'.

I tried removing the separators before uploading and then it just shows the full number i.e. 125657458 and doesn't add in the decimals.

I have set the Datatype to 'DECIMAL' for the column.

Row From CSV:

Ricky Manager Standard Normal 156,200

Results with decimal place in-tact on upload:

Ricky Manager Standard Normal 156

Results with decimal place not present on upload:

Ricky Manager Standard Normal 156200

Upvotes: 1

Views: 1783

Answers (1)

Rick James
Rick James

Reputation: 142278

DECIMAL(10,0) says to round to an integer. So, 156.200 becomes 156 and 156.9 becomes 160. Do you have any examples to verify the latter?

I notice you used "," as the decimal point; have you configure things to the call "," a decimal point and "." a 'thousands separator'? That is not the default.

DECIMAL(10,3) will take in 156.789 exactly.

No datatype will accept input that has a 'thousands separator'. That is, nothing will correctly accept 123,4567,789.12 (English) or 123.456.789,12 (some European) or 12,34,567.89 (Indian).

Upvotes: 2

Related Questions