Import data from an Excel file?

Questions
,
1

Hi,

I have an Excel file like this:

5327Untitled.png
5327Untitled.png481Ă—253 4.43 KB

How do I import the data to Mathematica so that I have a list like below:

Ukraine={x1,x2,x3,x4,x5,x6,x7,x8,x9}

Best regards,

2

You can programmatically get to all the elements in an excel sheet.

sheet = Import["test.xlsx", "Rules"];

This gives rules for everything (data, sheets, number of sheets, etc)

You can programmatically assign the sheet names by converting them to symbols (from strings) and assigning the data:

MapThread[
     Set[Evaluate[Symbol[#1]], #2] &, {"Sheets" /. sheet, 
      "Data" /. sheet}]

Add Flatten if you just want a plain list and not respect the rows and columns

MapThread[
     Set[Evaluate[Symbol[#1]], Flatten[#2]] &, {"Sheets" /. sheet, 
      "Data" /. sheet}]

Regards,

Neil

3

Thanks a lot Neil.

I considered an excel file (the attached file) with two sheets named “Ukraine” and “Istanbul” . When I use the names of the sheets, I can not get the expected output.

In[12]:= Import["C:\\Users\\agri-ghorbani\\Desktop\\data.xlsx"][[1]]

Out[12]= {{51.3, 44.3}, {29.2, 102.8}, {76.9, 66.7}}

In[13]:= Import["C:\\Users\\agri-ghorbani\\Desktop\\data.xlsx"][[
  Ukraine]];

During evaluation of In[13]:= Part::pkspec1: The expression Ukraine cannot be used as a part specification.

In[14]:= Import["C:\\Users\\agri-ghorbani\\Desktop\\data.xlsx"][[2]]

Out[14]= {{45.2, 39.7, 38.9}, {79.8, 65.6, 65.4}, {109.8, 24.6, 35.4}}

In[15]:= Import["C:\\Users\\agri-ghorbani\\Desktop\\data.xlsx"][[
  Istanbul]];

During evaluation of In[15]:= Part::pkspec1: The expression Istanbul cannot be used as a part specification.

data.xlsx (8.38 KB)

4

Try this-

Import["C:\\Users\\agri-ghorbani\\Desktop\\data.xlsx", {"Data","Ukraine"}]
5

Thank you so much, Girish.

6

You should read the documentation for excel import.

The code I posted works perfectly on the data file you uploaded. It assigned the variable ukraine and Istanbul as you requested. I do not understand why you changed the code. The option “Rules” returns all of the file information as Rules and then you can apply them. Your second post removed that part of the import. If you know in advance the names of the sheets, you can also use Girish’s post as an alternative. I assumed you had many sheets and did not necessarily have all the names in advance of the import.

I hope this helps.

Regards,

Neil

7

Dear Neil,

You are right. I have about 17 sheets in one excel file. I tried to apply your code for my data. Maybe I didn’t get it right.

If possible, could you please perform your code for the attached data.xlsx file?

I appreciate your help and time.

8

There is no file attached

9

Here is the file.

Thank you so much, Neil.

You helped me every time.

data.xlsx (8.36 KB)

10

The posted code works as long as your kernel is cleared. If the variables (ie Istanbul) are defined it will fail. One option is to handle that.

sheet = Import["data-2.xlsx", "Rules"];
Map[Remove, "Sheets" /. sheet]; MapThread[
 Set[Evaluate[Symbol[#1]], #2] &, {"Sheets" /. sheet, 
  "Data" /. sheet}]

By doing Remove, the symbol is erased so it can be used in the assignment.

Regards,

Neil

11

Thanks a lot, Neil.