I have tried to insert integer like 356361070084127 in mysql table named IMEI. However, while inserting it from excel file it convert these IMEI automatically to 356361070084120 changing last digit to 0. I have used PHPExccel to upload the excel file.
-
Check database column type and max integer that it can hold.T.Shah– T.Shah2017-05-12 07:34:21 +00:00Commented May 12, 2017 at 7:34
-
I have used the bigint in my mysql database too. But it didnot help either.Sushank Pokharel– Sushank Pokharel2017-05-12 07:58:07 +00:00Commented May 12, 2017 at 7:58
-
Make it unsigned and try.T.Shah– T.Shah2017-05-12 08:10:39 +00:00Commented May 12, 2017 at 8:10
-
making it unsigned also didn't solve the problem.Sushank Pokharel– Sushank Pokharel2017-05-12 08:24:55 +00:00Commented May 12, 2017 at 8:24
-
2"Actually I want these numbers to store IMEI no" - don't use a numeric data type for those - those aren't actual numbers. Store them as what they are - strings that happen to consist of numeric digits only.C3roe– C3roe2017-05-14 02:44:54 +00:00Commented May 14, 2017 at 2:44
|
Show 7 more comments
1 Answer
SUGGESTIONS:
Do a "select" in mysql to verify the "bigint" shows as expected in the database.
In "Excel":
a) select one of the integer values
b) Navigate to Home > Number
c) Increase Decimal
I suspect the actual problem is that Excel might be converting the integer to floating point, then rounding the floating point value.
One way to prevent this is to store the value as a "string" instead of "integer" - that way you can store a decimal value that's arbitrarily long.
Here is some additional information:
ADDENDUM: If you want to store an IMEI number ... THEN USE TYPE "STRING"! In the database, and ESPECIALLY in Excel!
1 Comment
Sushank Pokharel
Thanks paualsm4. Converting value to string in excel and bigint to varchat in mysql really solved the problem.