Message Boards

WOLFRAM COMMUNITY

7515 Views

10 Replies

6 Total Likes

View groups...

Follow this post

Share this post:

GROUPS:

Import data from an Excel file?

M.A. Ghorbani

M.A. Ghorbani, University of Tabriz

Posted 5 years ago

Hi, I have an Excel file like this: 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,

POSTED BY: M.A. Ghorbani

10 Replies

Sort By:

M.A. Ghorbani

M.A. Ghorbani, University of Tabriz

Posted 5 years ago

Thanks a lot, Neil.

POSTED BY: M.A. Ghorbani

Neil Singer

Neil Singer, AC Kinetics, Inc.

Posted 5 years ago

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

POSTED BY: Neil Singer

Neil Singer

Neil Singer, AC Kinetics, Inc.

Posted 5 years ago

There is no file attached

POSTED BY: Neil Singer

M.A. Ghorbani

M.A. Ghorbani, University of Tabriz

Posted 5 years ago

Here is the file. Thank you so much, Neil. You helped me every time. Attachments: data.xlsx

POSTED BY: M.A. Ghorbani

Neil Singer

Neil Singer, AC Kinetics, Inc.

Posted 5 years ago

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

POSTED BY: Neil Singer

M.A. Ghorbani

M.A. Ghorbani, University of Tabriz

Posted 5 years ago

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.

POSTED BY: M.A. Ghorbani

Neil Singer

Neil Singer, AC Kinetics, Inc.

Posted 5 years ago

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

POSTED BY: Neil Singer

M.A. Ghorbani

M.A. Ghorbani, University of Tabriz

Posted 5 years ago

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. Attachments: data.xlsx

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.