2

It seems that I can get the first column of excle data, say "test.xls", if I know the total length...

data = Table[{i}, {i, 6}];
Export["test.xls", data];    
Import["test.xls", {"Data",1,Range[len],1}]

But the problem is, If I don't know the total length, can I do the same thing? especially consider the data scale is quite large.

van abel
  • 1,235
  • 1
  • 11
  • 27
  • Can you provide a small example xls to ease answering? – Philipp Jan 13 '15 at 16:03
  • @Philipp updated with data filled in test.xls. – van abel Jan 13 '15 at 16:07
  • @Öskå the real range is set by Range[len],1 thus this will import first column with row from 1 to the len. Although the results is a row, quite strange. – van abel Jan 13 '15 at 16:26
  • Saving as csv is no option? – Philipp Jan 13 '15 at 16:28
  • @Öskå Aha, yes, your code work for me and solved my problem. But why my Import["test.xls", {"Data", 1, ;; , 1}] don't work??? – van abel Jan 13 '15 at 16:29
  • @Öskå unless this is a duplicate, you might consider answering, this could ve useful for future visitors. – Yves Klett Jan 13 '15 at 16:37
  • @Öskå please form your solution into an answer, as Yves Klett proposed. – van abel Jan 13 '15 at 16:40
  • @vanabel here are some examples that use span as part of the Import parameters http://mathematica.stackexchange.com/questions/5242/how-to-import-all-files-of-a-folder-at-once/5243#5243 – Gordon Coale Jan 13 '15 at 17:25
  • IIRC its actually faster to import the whole thing and extract what you need that is instead of putting the range inside import just do : Import["file.xls"][[1,;;,1]]. – george2079 Jan 13 '15 at 18:05

2 Answers2

7

The answer you are looking for is here: Import a Spreadsheet.

enter image description here

Thus you can use:

Import["~/test.xls", {"Data", 1, All, 2}]

The syntax in your case is {"Data", # of sheet(s), # of row(s), # of column(s)}.

Öskå
  • 8,587
  • 4
  • 30
  • 49
2

Its easy: just look at the structure of your imported data. Since you did not provide a file, we will use example data from Wolfram:

tab = Import["ExampleData/elements.xls"]
{{{"AtomicNumber", "Abbreviation", "Name", "AtomicWeight"}, {1., "H", 
"Hydrogen", 1.00793}, {2., "He", "Helium", 4.00259}, {3., "Li", 
"Lithium", 6.94141}, {4., "Be", "Beryllium", 9.01218}, {5., "B", 
"Boron", 10.8086}, {6., "C", "Carbon", 12.0107}, {7., "N", 
"Nitrogen", 14.0067}, {8., "O", "Oxygen", 15.9961}, {9., "F", 
"Fluorine", 18.9984}}}'

Now you see, there is a first bracket spreading the tables of the spreadsheet. Here only one. Then rows and then columns. Hence, what you want is the first column of the first table in the spreadsheet:

tab[[1, All, 1]]
{"AtomicNumber", 1., 2., 3., 4., 5., 6., 7., 8., 9.}

to look at the example spreadsheet, you can save it by using:

Export["test.xls", Import["ExampleData/elements.xls"]]
Philipp
  • 641
  • 3
  • 8