Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I have a set of data points in two columns in a spreadsheet (OpenOffice Calc):

enter image description here

I would like to get these into Mathematica in this format:

data = {{1, 3.3}, {2, 5.6}, {3, 7.1}, {4, 11.4}, {5, 14.8}, {6, 18.3}}

I have Googled for this, but what I find is about importing the entire document, which seems like overkill. Is there a way to kind of cut and paste those two columns into Mathematica?

share|improve this question
1  
If you could save as Excel file and have Excel available on your PC this answer could help you. –  Sjoerd C. de Vries Aug 1 '12 at 18:43
1  
Your title deals with Excel but your example mentions OpenOffice Calc. For which format do you want a solution? –  Sjoerd C. de Vries Aug 2 '12 at 5:25
    
I would prefer a solution that worked independent. A cut'n'paste solution would hopefully work with both excel and openoffice. I have changed the title from "excel" to "spreadsheet". –  Lucy Brennan Aug 2 '12 at 22:21
    
This related question includes a solution implemented in the spreadsheet (VBA Macro) mathematica.stackexchange.com/questions/14658/… –  Gustavo Delfino Nov 7 at 13:15

4 Answers 4

up vote 21 down vote accepted

Here is a manual method using copy-and-paste that is suitable for small volumes of data on an ad hoc basis...

1) Enter the following expression into a notebook, but don't evaluate it:

data = ImportString["", "TSV"]

2) Copy the cells from the source spreadsheet onto the clipboard.

3) Paste the contents of the clipboard between the empty pair of quotes in the expression from 1). If Mathematica brings up a dialog asking whether you want "escapes inserted", select Yes. The result should look something like this:

data = ImportString["1.0\t2.0\t3.0
0.4\t0.5\t0.6
7.0\t8.0\t9.0
","TSV"]

4) Press SHIFT-ENTER to evaluate the expression.

5) data now contains the pasted cell data, interpreted as Tab-Separated Values (TSV).

Alternate Approach

Follows steps 1) through 3) as before, then...

4a) Triple-click on ImportString in the expression from 1).

5a) Press CTRL-SHIFT-ENTER to evaluate the highlighted expression in place.

6a) The notebook now contains an expression that looks like this:

data = {{1., 2., 3.}, {0.4, 0.5, 0.6}, {7., 8., 9.}}

... but that expression has not been evaluated yet.

share|improve this answer
1  
Perfect answer, just what I was hoping for :) –  Lucy Brennan Aug 2 '12 at 22:24

This imports a whole sheet:

Grid[#, Dividers -> All] &@
 Import["http://joliclic.free.fr/html/object-tag/en/data/test.sxc", {"Data", 1}]

Mathematica graphics

And this imports three contiguous rows and two non-contiguous columns:

Grid[#, Dividers -> All] &@
 Import["http://joliclic.free.fr/html/object-tag/en/data/test.sxc", 
        {"Data", 1, Range[1, 3], {1, 3}}
 ]

Mathematica graphics

Details can be found on the SXC Import doc page (the range selections themselves are actually not documented there).

share|improve this answer

Notice that you can use Import["file.xlsx", {"Data",k,m,n}] to import data at cell {m,n} on k-th sheet in the file. To import a range of data, simply replace number m and n by a range list, e.g.

Import["file.xlsx", {"Data",1,Table[i,{i,3,8}],{1,2}}]

will import data from row 3 to 8, columes 1 and 2 from 1st sheet in your data file, giving you a 6*2 list. I assume this also works for openoffice file.

share|improve this answer
1  
This is correct but essentially replicates Sjoerd's answer. Nonetheless welcome to Mathematica.SE and thank you for your answer. Interestingly both Table and Range work in that third argument, but not Span. –  Verbeia Aug 3 '12 at 5:28
    
@verbeia I noticed that too when I was trying to get more than what the documentation promised. I reasoned that getting either a row or a single cell would be too restrictive, and indeed it was. –  Sjoerd C. de Vries Aug 3 '12 at 6:16

Copying my answer from SuperUser:

I am not familiar with the Excel clipboard format, but there is a lovely suite of tools for pasting tabular data in the form of a Palette. See this page for the code. When you evaluate that block of code, you will get a Palette with three buttons for different formats. I think there is a good probability that one of the three will do what you want.

You can save the Palette to your user Mathematica\SystemFiles\FrontEnd\Palettes directory and it will appear in the Palettes menu.


How to paste from Excel in practice

An important thing to know about the Windows clipboard is that it can hold data in several formats simultaneously. When you copy from Excel, the data gets copies in several formats, so it can be pasted into many different applications. Unfortunately, when you paste into Mathematica, the wrong format gets automatically chosen. It is not possible to remedy this from Mathematica directly.

The workaround is to first paste into Notepad, select all the text again (CTRL-A), the re-copy it as plain text format only. Now you can paste it into Mathematica using the palette's TSV or Table buttons.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.