Jump to content

Export csv files to Excel


DJB123
 Share

Recommended Posts

When exporting csv files (from a Macbook pro) and opening in Excel I get numeric data in string rather than numeric format. Thus a currency symbols (£ in my case) is included in the cell in Excel. This makes Excel data manipulation difficult without resort to string functions. Is there a way of ensuring data is exported as numbers without the currency symbol or indeed brackets indicating a negative number?

Link to comment
Share on other sites

Since posting this item I have found a workable solution. The problem seems to come from Excel (my Excel for Mac 2011 version at least) not liking the way ExpressAccounts encodes CSV files, despite choosing the preference for Excel coding of CSV files in ExpressAccounts preferences. Import into Excel was generating lots of £ symbols.

 

In ExpressAccounts Preferences / Regional I made sure the comma under thousands was deleted (decimal stop remained). Under Preferences/ Other / Advanced Settings I clicked “Save CSV files for Excel” (note UTF-8 encoding also seems to work). I came out of preferences. Select and view a report, select Save / Save to CSV and choose (and save) a file name and save location.

 

Go to Excel. Under the Data tab select Text (import data from a text file) and select the CSV file saved above. A Wizzard come up. Under File origin after trial and error I selected Unicode 6.1, then clicked next. Untick Tab and click Comma, then click Next. I left column data format as General and clicked Finish and data was loaded correctly onto the selected sheet.

 

Maybe the Excel CSV encoding in ExpressAccounts is faulty?

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...