# Can I improve the importing speed of Excel data?

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?

-
why are you importing column by column, row by row (i.e. cell by cell) instead of importing the entire worksheet? –  Mike Honeychurch Jan 13 '13 at 2:07
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, –  belisarius Jan 13 '13 at 5:07
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. –  sebastian c. Jan 13 '13 at 13:48
@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? –  rcollyer Jan 13 '13 at 15:49

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.

-
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? –  sebastian c. Jan 13 '13 at 14:06
@sebastianc. Dimensions gives {rows, columns}, so it has not been transposed. But, if you need it transposed, use Transpose. –  rcollyer Jan 13 '13 at 14:13
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? –  sebastian c. Jan 13 '13 at 14:21
@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. –  rcollyer Jan 13 '13 at 15:43
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? –  sebastian c. Jan 13 '13 at 17:14

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

-
You can use Table instead of ParallelTable as well... –  Rod Jan 14 '13 at 0:35
Thanks, Rod do you or anyone know why I cannot import from Excel Macro-Enabled file? –  sebastian c. Jan 14 '13 at 1:43
Take a look at this: [1]:(reference.wolfram.com/mathematica/guide/…). –  Rod Jan 14 '13 at 1:55
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. –  Rod Jan 14 '13 at 1:57