1
$\begingroup$

I have data in an Excel spreadsheet that has 4 rows and 1,000 columns. I am using Import as follows: 

fittedMatrix  = Table[
     Import["sheet.xlsx", {"Sheets", "MC", #, i}] & /@ Range[2, 5], 
     {i, 2, 1001}
]; // AbsoluteTiming

which is importing on a column by column basis -- a thousand imports per spreadsheet. Is there a way to import row by row basis? Hopefully, this would improve the speed of import. Or would it be better to transpose my data within Excel first?

EDIT: I tried transposing within Excel first to get dimensions of 1,000 x 4 and tried again using: 

fittedMatrix=Table[
     Import["sheet.xlsx",{"Sheets","mcTransposed", #,i}] &/Range[2,1001],   
     {i,2,5}
];//AbsoluteTiming

but now it is giving:

{{1/2 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/3 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/4 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/5 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/6 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/7 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/8 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/9 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/10 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),
  1/11 (Import[sheet.xlsx,{Sheets,mcTransposed,#1,i}]&),

instead of my actual Excel data?

Improve this question
$\endgroup$
4
  • 6
    $\begingroup$ why are you importing column by column, row by row (i.e. cell by cell) instead of importing the entire worksheet? $\endgroup$
    – Mike Honeychurch
    Commented Jan 13, 2013 at 2:07
  • 7
    $\begingroup$ If your questions were a showoff of exceptional quality, perhaps the users around could pardon you the fact that you already posted 42 questions in two months, answered only one question, voted only 13 times, and seldom comment when someone asks you something. Please try to improve your participation on this site, $\endgroup$
    – Dr. belisarius
    Commented Jan 13, 2013 at 5:07
  • $\begingroup$ Hi @belisarius to answer your reply I only begun using Mathematica for my course in late-October. Learning to use this forum and how to format properly also takes time do you agree? Or you simply become expert from immediate usage? From talking to someone else who has been using Mathematica for over 20 years he is still learning many new ways of doing things, not sure how to improve participation if one has limited knowledge of both tool and here. $\endgroup$
    – sebastian c.
    Commented Jan 13, 2013 at 13:48
  • 3
    $\begingroup$ @sebastianc. What belisarius is trying to convey is what is considered common etiquette here. We are all volunteers, and more then willing to help. So, we are not expecting perfection, but we are expecting improvement and conformation to the established idioms. Have you read the FAQ? Did you look at my revisions to see what I did, so you can replicate it? $\endgroup$
    – rcollyer
    Commented Jan 13, 2013 at 15:49

2 Answers 2

Reset to default
7
$\begingroup$

Obviously you read the xlsx docs, or you would not have known how to import a sheet named "MC". But, it appears that you missed that you can omit the cell specification after the sheet name. Run this instead:

fittedMatrix = Import["sheet.xlsx", {"Sheets", "MC"}]

it should be a lot faster as it does not load it cell-by-cell, or for your edited version

fittedMatrix = Import["sheet.xlsx", {"Sheets", "mcTransposed"}]

As to your edit: you are missing the @ in /@, so the Import in

Import[...]& / Range[2, 2001]

is interpreted as being divided by the range.

Improve this answer
$\endgroup$
6
  • $\begingroup$ Hi @rcollyer, why importing a 1000 x 4 set of data from Excel, it becomes a matrix of 4 x 100 as shown by Dimensions[]-> {4,1000}, i.e it got transposed? $\endgroup$
    – sebastian c.
    Commented Jan 13, 2013 at 14:06
  • $\begingroup$ @sebastianc. Dimensions gives {rows, columns}, so it has not been transposed. But, if you need it transposed, use Transpose. $\endgroup$
    – rcollyer
    Commented Jan 13, 2013 at 14:13
  • $\begingroup$ Hi @collyer, when I do CTRL-SHIFT-ENTER to hi-light all the cells within Excel, a pop-up shows 1000R x 4C. But importing using the last command shows Dimensions ->{4,1000} and doing a //MatrixForm also confirms that it got transposed without me actually performing the tranpose? $\endgroup$
    – sebastian c.
    Commented Jan 13, 2013 at 14:21
  • $\begingroup$ @sebastianc. based upon that I would say yes it is being transposed. But, you can confirm that by entering into a sheet the numbers 1 - 8 across two rows and 4 columns. When you import it, you will then be able to tell whether it transposes it or not. $\endgroup$
    – rcollyer
    Commented Jan 13, 2013 at 15:43
  • $\begingroup$ It is fine importing the whole sheet, it didn't get transposed, just that it did using the method above. Possible to import .xlsm file too since I converted from .xlsx to xlsm? Not sure why ok to import data but not from Macro-Enabled sheet data? $\endgroup$
    – sebastian c.
    Commented Jan 13, 2013 at 17:14
0
$\begingroup$

Well, I'm not sure if it's actually faster, but maybe you should give a try to the OpenRead command.
I have a (359,835 x 17) matrix in .CSV format which I import following these steps:

1. MyMatrix = OpenRead["C:\MyData\MyData.csv"];
2. n = 359835; (*This is the number of lines to be read*)
3. MatrixLines = ReadList[MyMatrix, "String", n];
4. CompleteMatrix = ParallelTable[StringSplit[MatrixLines[[x]], ";"], {x,1,n}]

I hope this helps!
Rod

Improve this answer
$\endgroup$
4
  • $\begingroup$ You can use Table instead of ParallelTable as well... $\endgroup$
    – Rod
    Commented Jan 14, 2013 at 0:35
  • $\begingroup$ Thanks, Rod do you or anyone know why I cannot import from Excel Macro-Enabled file? $\endgroup$
    – sebastian c.
    Commented Jan 14, 2013 at 1:43
  • $\begingroup$ Take a look at this: [1]:(reference.wolfram.com/mathematica/guide/…). $\endgroup$
    – Rod
    Commented Jan 14, 2013 at 1:55
  • 1
    $\begingroup$ XLSM is simply not accepted as an importing format by Mathematica, so I would advise you to save your data in another format and THEN import it to Mathematica. $\endgroup$
    – Rod
    Commented Jan 14, 2013 at 1:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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