# Import Spreadsheet Data and DeleteCases of empty cells &/or choosing a Range to import

I am importing Excel spreadsheet data into Mathematica.

Here is what the data looks like, notice the cell Q1 with "sdf":

I import this data by specifying the Spreadsheet location, sheet name, top 9, left 7, drop the Header Row and delete empty values:

Dataset1 =
DeleteCases[
Drop[Import[
"c:\\GraphingSoftware\\Book2.xlsx", {"Sheets", "Sheet2"}][[9 ;;, 7 ;;]], 1], ""];

FullForm[Dataset1]

List[List[126.7, 55.3, 113.8, 96.9, 47.8, 74.6, 71.9, 55.2, "", "", ""]


The results contain the emtpy cell values upto the "sdf" column.

I want to be able to remove empty values with the DeleteCases function but it doesn't work.

The documentation doesn't indicate if you can specify the number of columns and that would be really useful.

To save the day I simply want to delete empty cells or even better specify the number of columns so empty columns aren't included. Help as always is much appreciated.

• Try for example {{71.9, 55.2, "", "", ""}} /. "" -> Sequence []  Jun 27, 2013 at 3:54
• Can you please put that as an answer, it works! Thank you very much :) Jun 27, 2013 at 3:55
• Mmm ... I guess this question is a dup. I'm too lazy to check Jun 27, 2013 at 3:56
• Could be but this is a bit unique. While I have your time, do you know if there is anyway to specify the explicit number of columns? Jun 27, 2013 at 3:56
• forums.wolfram.com/mathgroup/archive/2007/Oct/msg00506.html Jun 27, 2013 at 14:09

## 2 Answers

Save your import result to a variable and check its structure. You will notice that importresult[[1]] is a List (representing the first row, the header in this case). Thus, if you use "" as the pattern for DeleteCases, it will match nothing because in level 1 of importresult there cannot be an empty string anywhere.

You could tell DeleteCases to operate on level 2: DeleteCases[importresult, "", {2}]. This will delete empty cells.

However, I would suggest not to do it this way as it will not be very robust in my experience. When importing external data, there can always be empty or faulty values and you should not let them break your code. Instead of deleting the cells which do not match, use Cases to select the rows which do match. You could do it like this:

1. Find out the number of columns that need to have values. You might take the number of strings in the header for this (but different methods might be more appropriate):

columns = LengthWhile[importresult[[1]], StringQ]

2. Pick all rows with Cases that have as many Reals:

Cases[importresult, {values:Repeated[_Real, {columns}], ___}:>{values}]

___ is BlankNullSequence, so anything (or nothing) can follow the repeated Reals. Finally we name the matched Reals values and use a replacement rule in Cases to just obtain this part of the matched row.

I believe you could even extend this and find the position of your data table in the excel sheet automatically, so you will not have to specify your starting position {9,7} at the time of import.

• Thanks for your help, your answer did help and I have awarded you the points. Hopefully my answer helps others in the future. Jul 8, 2013 at 0:31

The documentation doesn't indicate if you can specify the number of rows/columns and that would be really useful.

I was wrong! Its a simple case of the Excel spreadsheet import info not having an example using spans.

To clarify, here is how I let users pick a top left cell to get the data in Row 3 to 28, Column D to L:

d = Import["C:\\MMA\\D13~136587.xlsx", {"Sheets", "Sheet1"}][[3 ;; 28 ;; 1, 4 ;; 12 ;; 1]];


Now I've got the exact region I'm interested in its easy to handle Nulls, #N/A's, Empty cells & etc using either the:

1) DeleteCases,
2) /. "" -> Sequence []
3) Cases[importresult, {values:Repeated[_Real, {columns}], ___}:>{values}]

Thanks!