Preventing Excel from wrongly interpreting Strings in CSV files as Exponential numbers

MS Excel and Apple Numbers both attempt to guess the format of the contents of each cell when they open up a CSV file. Most of the time this is fine but in some cases it causes a big problem.

If you have a string with numbers and the letter E then both programs will treat this as a number in exponential notation.

For example, the string 229E10 will be converted to 2.29E+12

What makes it worse is that you cannot change it back to the original text using the cell format options.

I run into this problem with strings that represent microtiter plate and well assignments. For example, 229E10 refers to plate 229 row E column 10. 

I just want the spreadsheet program to take what I give it as text. Putting the string in single or double quotes can work but then the value remains in quotes in the spreadsheet.

The best way to do this seems to be to use double quotes around the string AND prefix that with an equals sign - like this:

="229E10"

For reasons that I do not understand, Excel will strip the extra formatting off the string and treat the contents as plain text. This also works for arbitrary strings, such as ="ABC"

Downloading CSV files in Rails

Providing a way to download data as a CSV file is a common feature in Rails applications.

There is a nice Railscasts episode on the topic here:  Exporting CSV and Excel

I prefer to use a view template to generate my CSV as it gives me a lot of control on the fields that go into the file. But the standard way of invoking this from the controller does not provide a way to specify the filename for the downloaded file


  respond_to do |format|
    format.html
    format.csv 
  end 

With a Show action, for example '/posts/25.csv', the downloaded file would be called '25.csv' which is not useful.

In a response to this Stackoverflow question, Clinton R. Nixon offers up a nice solution.

He has a method called render_csv in his application controller that takes an optional filename. Before calling regular render on your template, it sets several HTTP headers - most importantly a Content-Disposition header with the desired filename. It adds the '.csv' suffix for you and uses the action name as the default if no name if supplied.

With this in place you modify your controller like this


  respond_to do |format|
    format.html
    format.csv { render_csv('myfile') }
  end

Very nice and very useful...

Loading CSV data from Excel into MySQL on a Mac

Loading data from a CSV file into MySQL requires that your file has the same number of columns in the same order as the MySQL table.

That means that you need to populate any column with an auto incrementing primary key, as well as any timestamp column that might be generated by your application. This is particularly relevant if you want to populate a tables used in a Rails web application.

1: Build your table in Excel, adding a primary key column if necessary and populating it using the Edit->Fill->Series... feature in Excel.

2: Save it in CSV (Comma delimited) format - don't bother with the CSV Windows, etc options. Don't worry about Excel's warnings about losing formatting (unless of course you have important formatting...)

3: Optionally load it into an editor like TextMate (turn on 'show invisibles') and do any fine tuning you might want or need to do.

4: Run the Mysql client and from the mysql> prompt load in the data using a command like this:

mysql> load data local infile 'products.csv' into table products fields terminated by ',' lines terminated by '\r';

I needed the '\r' line termination loading the file from Mac OS X. It always takes me a couple of tries before I get the right \n and/or \r combination...