Excel Formatting of Ruby CSV Data

You’re outputting a CSV file of data from your application (Rails in our case) and no matter how you try to render and contort a specific column, it always appears different when the user opens the file in Excel. Excel always seems to decide that it should format a specific column as a date or something similar and applies its own data formatting to show it to the user in a localised manner.

Excel makes many assumptions about your data by default when you open a CSV file and displays a more “user friendly” version of the data. A common issue I notice is when exporting merchandise sales for a club. Users select a size from a dropdown (i.e. 11-12 for kids aged 11-12) and we wish to display that size to the club administrator so that they can fulfil the order appropriately.

Excel, however, will insist that this piece of data (size) is a date and should be displayed in a localised date format as shown below. This is the exact same .csv file which has been opened with Excel. It thinks that 11-12 is December 11th. As you can imagine, this is particularly confusing for end users and trying to explain to them that 11-Dec actually means size 11-12 just won’t fly.

After battling many variations of this issue, we have come upon a consistent approach to control the output format of the data in Excel. By outputting a string of data that starts with an = sign and then contains the eventual data within quotes, excel interprets the cell as a string value and does not try to make any further assumptions about the display format.

Here is exactly how we render the values into the .csv file from our ruby code. Notice how we use escaped double quotes inside the string. We found that single quotes did not work.

"=\"#{data_value}\""

So the resulting output .csv file will look like this.

And when we open it in Excel, voila, we get the exact data we were hoping for.

It is a common requirement to output application data to a .csv file. At ClubZap we allow clubs to extract member, purchase and transaction data for both upload to other tools (accounting software, governing body databases) as well as for their own internal processing. .csv is the cleanest format to extract data from our PostgreSQL database and make it consumable by potentially non-tech customers. For the most part, people will open the downloaded .csv file in a spreadsheet application which, for the vast majority or people, means Microsoft Excel.

Excel is an incredible tool for both basic data review as well as complex data manipulation. We could never accommodate all of the data-specific requirements of our customers within our application. Accessing data in Excel allows our customers to dream up an endless variation of potential data views and manipulations. By controlling the formatting of that data in a spreadsheet we can ensure that the end user will see the expected data and avoid any over optimistic assumptions by Excel.