Import variables from excel

Question

I have an excel file I am trying to import into Mathematica. It is in the format of variables names in the first row of each column and then the data underneath. One of the columns specifies hospitals, so each row has observations for each hospital. There are approximately 800 columns and 400 hospitals. Some of the variables are strings, such as hospital names. But I could delete those in Excel if I had to. I imported it and the text filled the screen. I saw there was a program called Link for Excel, but $250 is a lot of money for a student. I then tried ListView and the table was all wrong. The variable names were in the first column. It seems like it was displaying rows as columns. I want to make sure what is imported is a matrix so we can run some linear programming. Thanks so much for your help in advance!

Answer 1

How about:

 data = Import[file.xls, {"Data", 1}]

Answer 2

First things first, I'm certain you do not need a new expensive add-on. Excel files can be tricky as people often save their files with all sorts of different settings.

1. When you import a file using Import["path_to_file"], it is good to play around a bit with the import settings. For excel you might want to use either:

    data = Import["path_to_file","CSV"];

or

    data = Import["path_to_file","Table"];

Note how in both cases I added a semicolon ";" at the end (as @R Hall mentioned). This is to ensure that the screen does not output what you have just imported. It will store the value and assign it, but not print it out. Once imported, write the following on a new line: data[[;;10]]. This will show you the first 10 rows of your import and you can use this as a quick guide to checking whether the import was performed correctly.

The above will give an output in terms of a Mathematica 'list', so it will be in the form of {{hospital,'st johns','canterbury',...},{12,35,63463,...},{...}} etc. To have them as a more 'human-readable' form, simply add //TableForm at the end:

data[[;;10]]//TableForm

It might help if you could provide a snippet of your excel sheet (no need for the entire 400x800, just the first few elements would do).

PS: You might also want to consider creating a Dataset out of your data.