CSV comma issue - please give suggestions!

[Kane250]

Hi all,

 

I have a CSV file that was given to me with 3 columns, one of which contains numbers in the "21,587" format.  I am trying to import this CSV into a MySQL database but since the file is a comma separated file, it is importing only number values before the comma.  21,587 becomes 21. 

 

Excel for some reason cannot search and delete the commas out of the cells, that was my first thought of what to do, although I'd rather keep the commas and use the field as a varchar rather than an int anyway.  Then I tried changing the "fields terminated by" option to something else, like "/r".  No dice.  Then I tried exporting the file as a text file and found that I could only import a CSV file or a SQL file anyway.

 

So, I'm stuck.  I would imagine this is something that has happened to people before, but despite all my searching, I have been unable to find a solution.

 

Any help would be truly appreciated!  Thanks!

[Kane250]

Well, I got excel to just remove the commas and then used php's number_format to add them back in when echoing.  Would still be curious to know if there were any other ways around this, but for now I am all set.  Thanks anyway!

[PFMaBiSmAd]

Commas only exist in numbers to make them more human friendly. You will find in programming (mysql and php...) that you cannot even add numbers that contain things like $ signs and commas. You should store numbers as numbers and only add any human aids to them when they are displayed.