Sage 50 2016 Export to csv or open in excel data is not numeric

I'm using Excel 2016, and just upgraded to Sage 50 Pro Accounting 2016 because the Sage sales guy said that it was compatible with Excel 2016.  However, when doing "Open in Excel", it only shows the option of  Excel 97 - 2007.  After saving this .xls file and opening it in Excel 2016, none of the numeric data is in numeric format - it is in text format.  Also, the non-numeric data has a single quote mark at the start of every field.  If I try to click the "comma" number format, it says "Style "comma" not found".

I tried exporting to CSV instead, and this works a little better (no single quote marks at the start of every non-numeric data field), but all the zeroes are not numeric, and thus when copying this into another Excel worksheet, it causes #VALUE errors.

Is there a way to export to an Excel file so that all the numeric data is formatted as numeric data?

If anyone has an answer, I'll be very happy!!

  • 0

    Hi, it is possible you are on French operating windows ?  If yes check Article  10780

  • 0 in reply to Jason.
    No, not on French operating windows, but I still checked that article - didn't help. Thanks for the suggestion though. So are you saying that you can export to .xls file with Sage 2016 and open it in Excel 2016 and it works properly? Everything worked fine for me with Excel 2013.
  • 0 in reply to DJK
    try selecting the entire column and then change the column to numbers or text. There is an advance column layout in Excel as well. Play with those settings. Lastly can export to text then use Excel's text import wizard to configure the column's... this is a last resort though
  • 0 in reply to GwG
    Thanks for your suggestion. The numeric problem seems to be solved now - not sure how - perhaps a setting I changed in response to the above reply, which perhaps didn't work till I'd rebooted. However, there are still the single quote marks at the start of every text field. The GL account numbers are considered text fields, so they also have the single quote marks. I do realize that I can get rid of all of these by selecting the area, and replacing the quote marks with nothing, but would rather not have to do that. Of course, it would be nice if the reporting in Sage was better so I didn't have to export anything to do reports!
  • 0 in reply to DJK
    Data exported as 'Excel 2.1 format' may be difficult in newer versions of Excel:

    - The Microsoft Excel Trust Center settings may prevent you from opening it at all,
    - All the non-numeric fields are interpreted by Excel as a quote in front of text, instead of as quoted, text.
    - None of the report formatting is exported.

    The Excel 97-2007 export format is the only choice for 'Open in Excel', and usually works fine. (although for large sets of data it takes longer to run)
  • 0 in reply to RandyW
    Well, I've found the answer and all is well now - the problem was a COM add-in in Excel 2016. I've removed it and the problem is gone.