How to import CVS into MySQL when data contains special characters ?

I have tried to import but keep having the data get messed up.

The fields do not always match up to what the data is, i.e. the price moves to the next field and the next one after that moves to the next again and so on until the new line is reached. causing new fields to be created with COL (number) as its name.

My data has commas and this is what is causing the problems, so would like to know how i might be able to save my file in excel so it wraps the data in quotes but there is another problem my data also has the quote marks ! so this get worse still.

any ideas people ?

If your user has the FILE permission you'll want to look into using LOAD DATA INFILE with the ENCLOSED BY and ESCAPED BY bits.

Excel [and most other MS programs] can be a real bitch for formatting, so you might have to do some fudging to get it to work. For example you can replace all of the commas within Excel with & #44; [less the space] which will show a comma when printed in and web page, or can be replaced with a SQL query later.