Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I am importing Excel spreadsheet data into Mathematica.

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

enter image description here

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.

share|improve this question
1  
Try for example {{71.9, 55.2, "", "", ""}} /. "" -> Sequence [] –  belisarius Jun 27 '13 at 3:54
    
Can you please put that as an answer, it works! Thank you very much :) –  WolframFan Jun 27 '13 at 3:55
    
Mmm ... I guess this question is a dup. I'm too lazy to check –  belisarius Jun 27 '13 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? –  WolframFan Jun 27 '13 at 3:56
1  

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks for your help, your answer did help and I have awarded you the points. Hopefully my answer helps others in the future. –  WolframFan Jul 8 '13 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]];

enter image description here

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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