1
$\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.

$\endgroup$
1
$\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.

$\endgroup$
  • $\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 Jul 3 '17 at 9:51
  • $\begingroup$ You should better specify your problem, whether it is handwritten or calculated using formulas makes no difference, since "Data" imports numeric results, "Formulas" imports formulas. $\endgroup$ – TeM Jul 3 '17 at 10:09
  • $\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 Jul 3 '17 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 Jul 3 '17 at 11:24
  • $\begingroup$ Probably the "Sheet 1" has been deleted from the Excel workbook and you wrote it in "Sheet 2". $\endgroup$ – TeM Jul 3 '17 at 12:18
2
$\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

$\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?

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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