Excel formatting issue UTF-8 £ showing as £

[MartinCroft]

We have a data extract to a csv file from SQL server 2008 that when viewed in Excel changes all £ symbols to £ ( A with hat symbol and a £ just in case it does not display correctly) This was shown in Excel 2002 which I was able to replicate with against the user experiencing this issue. I also had Excel 2007 which was not showing the issue. The user now has excel 2007 installed but when double clicking on the file it opens and shows the same issue. If you go into Excel and open the file, the import wizard starts and shows it as UDF-8, which is what SQL saved it as, with no issues, i.e a £ shows as a £. What I want to know is there are a way of setting the default encoding when opening up a file in Excel or why this does this to try and get a workaround, open all files from Excel by opening Excel first isn't really an option .

 

[SkipVought]

Hi,

Rather than OPENing the .csv file, IMPORT into an Excel workbook, using Data > Import External Data > IMPORT File.... You will have much more control over your data as you PARSE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MartinCroft]

We have many users that run lots ad-hoc queries through SQL server and save out as CSV files, its not ideal to go through this process if there is some way of setting a default. They tend to currently just open the file validate the data from a folder. Excel doesn't seem to workout its UTF-8 format. If opening the file with notepad ++ it shows its UTF-8 which is the SQL 2008 default.

 

[SkipVought]

We have many users that run lots ad-hoc queries through SQL server and save out as CSV files...

This QUERY could be done in a worksheet, saving your users the interim step and headache! I've taught many of my users how to query data from many other sources in Excel.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MartinCroft]

Hi thanks for your help, but we are looking to a fix or easier workaround than users using excel to run query s. We want them to still run through management studio it will cause more issues than resolve if they start using excel.