34
$\begingroup$

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?

Improve this question
$\endgroup$
4
  • 1
    $\begingroup$ If you could save as Excel file and have Excel available on your PC this answer could help you. $\endgroup$
    – Sjoerd C. de Vries
    Commented Aug 1, 2012 at 18:43
  • 1
    $\begingroup$ Your title deals with Excel but your example mentions OpenOffice Calc. For which format do you want a solution? $\endgroup$
    – Sjoerd C. de Vries
    Commented Aug 2, 2012 at 5:25
  • $\begingroup$ 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". $\endgroup$
    – Lucy Brennan
    Commented Aug 2, 2012 at 22:21
  • $\begingroup$ This related question includes a solution implemented in the spreadsheet (VBA Macro) mathematica.stackexchange.com/questions/14658/… $\endgroup$
    – Gustavo Delfino
    Commented Nov 7, 2014 at 13:15

6 Answers 6

Reset to default
48
$\begingroup$

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.

Improve this answer
$\endgroup$
1
  • 1
    $\begingroup$ Perfect answer, just what I was hoping for :) $\endgroup$
    – Lucy Brennan
    Commented Aug 2, 2012 at 22:24
7
$\begingroup$

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).

Improve this answer
$\endgroup$
7
$\begingroup$

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.

Improve this answer
$\endgroup$
4
$\begingroup$

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.

Improve this answer
$\endgroup$
2
  • 1
    $\begingroup$ 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. $\endgroup$
    – Verbeia
    Commented Aug 3, 2012 at 5:28
  • $\begingroup$ @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. $\endgroup$
    – Sjoerd C. de Vries
    Commented Aug 3, 2012 at 6:16
1
$\begingroup$

Here is an Excel VBA function that will generate the list in an excel cell. Copy the cell and paste directly into mma:

Function ToMathematicaList(Y_Values, X_Values)

N1 = Y_Values.Count

stout = "{"

For J = 1 To (N1 - 1)

stout = stout & "{" & X_Values(J) & ", " & Y_Values(J) & "},"

Next J

stout = stout & "{" & X_Values(N1) & ", " & Y_Values(N1) & "}}"

ToMathematicaList = stout

End Function

Improve this answer
$\endgroup$
0
$\begingroup$

I don't have 50 reputation points so I'm submitting this as an answer when it is really a comment, a small addition. After doing the first approach given by WReach, Wrap the result of your evaluation between theses two brackets -->"Grid[]" and add "Grid[ ,Frame->All]". Enjoy!

Improve this answer
$\endgroup$
1
  • $\begingroup$ Well, that is not part of the OP's question. She just wanted to copy the data, not make it look like it was a spreadsheet. Putting it in a grid makes it more difficult to manipulate. $\endgroup$
    – Oleksandr R.
    Commented Feb 17, 2015 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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