CSV IMPORT error: Invalid value for field (INT)

[konfuzion]

The CSV file has a number field which displays 2,222,333,444
The original value in the cell is 2222333444

When I try to import, PHPMAKER throws error:
Invalid value for field (field name LARGENUMBER / INT ): 2,222,333,444

So I think PHPMAKER is trying to import "2,222,333,444" into the LARGENUMBER / INT field in the database which will fail.

How can I force PHPMAKER to read original cell value "2222333444" in page_importing server events?

[arbei]

What is the data type and database type? If INT of MySQL, the maximum value signed is 2147483647.

[konfuzion]

Thanks for reminder, the large number passed the SIGNED INT limit. I changed to UNSIGNED INT and no errors now.

[konfuzion]

Not solved, errors reappear again.

The number is "21,123,123" or in cell value "21123123", doesn't pass the SIGNED INT limit yet

I think the commas are causing the problem because the error displays Invalid field value "21,123,123"

My proposed 2 methods:

1. Either turn off the commas in EXCEL
2. Or in PHPMAKER page-importing , use a function, for every row, use regex to check if it is a number with commas, and replace "," with "", then save number to to db INT field

[konfuzion]

I checked the CSV and sometimes it is
...,"21,123,123",...
...,21123123,...
...,5000000,...
...,"1,231,234",...

so it doesn't make sense to read from cell value as some are enclosed in quotes

Best way is to select the entire number column in EXCEL and remove number formatting which removes commas.

[mobhar]

You may actually use Row_Import server event to check whether the value of the certain field/column in your .csv file contains comma character, and then remove it before importing it to your database.