CSV compabtility issue in excel

Question

I download a .csv from database and use the file to generate a PDF using a PDF conversion service. However, for specific files, the conversion fails and the request times out. I checked the file type in properties and got this 'Microsoft Excel Comma Separated Values File (.csv)'.

I opened the csv file and saved it in CSV(comma delimited) format in Excel 2010. It asked me the csv may contain features that are not compatible with CSV(comma delimited) and remove all incompabilites while saving. After this step, I was able to generate PDF from the CSV.

I think some white spaces, special characters should have been removed while I did the save. Since this is intermittent, I cannot change the format of the CSV we get from the DB. Also, the data in CSV is huge,

1) Is there any method to keep the (Microsoft Excel Comma Separated Values File (.csv)) CSV format and remove all the special characters, white spaces?

Answer 1

This should work for whitespace. I'm not sure about 'special characters'.

Open your CSV file in Excel and click the new sheet button (or hit <Shift>-<F11>.

In cell A1 of the new sheet, insert the following formula:

= TRIM(Sheet1!A1)

Copy/down and copy/across this formula so that it covers the same number of columns and rows as in your CSV data.

You should have a second sheet filled with data

Make sure you are stil in Sheet2 in the workbook and then File/Save As. Ensure the type is 'CSV (Comma delimited) (*.csv)'. Give it a different name to the original file for sanity reasons.

The saved CSV file should contain all your data without leading or trailing whitespace. Whitespace within a field (eg "this         that") will remain.