2
$\begingroup$

I guess this is really mundane question, but I can't find the answer anywhere. Having a rather complicated Excel that came from testing centre, I need to import specific data into Mathematica for further analysis, rows 129 to 159 from column G and M - this should be in format {{x1,y1},{x2,y2},...} - just to be really on point, x1:=G129, y1:=M129, x2:=G130 and so on.

So I tried many variations on

a = Import["....xlsx",{"Data",1,{129,...,159},{7,13}}]

where 7 and 13 are numbers of columns G and M respectively, but it obviously does not work.

Many thanks ahead,

G

TIP

The question is answered bellow, however, I stumbled upon the problem of numbering Excel sheets. Then I found out that there is a code for finding sheet number. It's =SHEET(123), if 123 is name of the sheet, or =SHEET("apple") if apple is name of the sheet.

Improve this question
$\endgroup$

3 Answers 3

Reset to default
2
$\begingroup$

Writing:

Import["C:\\Users\\TeM\\Desktop\\data.xlsx", {"Data"}][[1, Range[129, 159], {7, 13}]]

I get:

{{x1, y1}, {x2, y2}, ...}

which is what you want.

Improve this answer
$\endgroup$
3
  • $\begingroup$ That's the syntax I have been looking for - thank you! The output seems to be ok, but it's blank data - I get {{, },{, },{, },....}. The data I'm trying to export from Excel are numbers, but they are computed, not entered manually, maybe that could be the problem? Got an idea how to solve it? $\endgroup$
    – Ginny The Grey
    Commented Jul 3, 2017 at 9:51
  • $\begingroup$ It's numbers,I thought that it shouldn't matter how was the data generated, I just mentioned it. I tried the same code line with "Formulas" instead of "Data", and it extracted the formulas that are on those exact positions, so it is well written, it just does not give the numbers, I really don't know why. $\endgroup$
    – Ginny The Grey
    Commented Jul 3, 2017 at 11:13
  • $\begingroup$ Wow, I've just now mistyped the number 1 /that should stand for sheet identificator, I think, right?/ as 2 and it worked. No idea why... $\endgroup$
    – Ginny The Grey
    Commented Jul 3, 2017 at 11:24
3
$\begingroup$

You can also specify the sheets, rows and columns in Import (rather than importing the whole data and selecting specific rows and columns).

For example, for the following Excel workbook "data.xlsx" with two sheets

expdata1 = Table[{i, j}, {i, 1, 200}, {j, CharacterRange["A", "Z"]}];
Export["data.xlsx", {{"Experiment 1" -> expdata1,
     "Experiment 2" -> {RandomReal[1,10]}}}, {{"Sheets"}}];

To get rows 129-139 and columns 7 and 13 from the first sheet you can use

imported = Import["data.xlsx", {"Data", 1, Range[129, 139], {7,13}}]

or

imported = Import["data.xlsx", {"Sheets", "Experiment 1", Range[129, 139], {7, 13}}]

to get

{{"{129, \"G\"}", "{129, \"M\"}"}, {"{130, \"G\"}", "{130, \"M\"}"}, {"{131, \"G\"}", "{131, \"M\"}"}, {"{132, \"G\"}", "{132, \"M\"}"}, {"{133, \"G\"}", "{133, \"M\"}"}, {"{134, \"G\"}", "{134, \"M\"}"}, {"{135, \"G\"}", "{135, \"M\"}"}, {"{136, \"G\"}", "{136, \"M\"}"}, {"{137, \"G\"}", "{137, \"M\"}"}, {"{138, \"G\"}", "{138, \"M\"}"}, {"{139, \"G\"}", "{139, \"M\"}"}};

MatrixForm@imported

Mathematica graphics

Improve this answer
$\endgroup$
1
$\begingroup$

Can you maybe split it up in multiple parts? i.e.

a = Import["....xlsx",{"Data"}]
b = Transpose[a[[1,129;;159,{7,13}]]]

or something like that?

Improve this answer
$\endgroup$

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.