# Cut and paste data from a spreadsheet

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

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?

-
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
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 '14 at 13:15

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.

-
Perfect answer, just what I was hoping for :) –  Lucy Brennan Aug 2 '12 at 22:24

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.

-

This imports a whole sheet:

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


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}}
]


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

-

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.

-
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

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

-

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!

-
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. –  Oleksandr R. Feb 17 at 10:28