8
$\begingroup$

I have a spreadsheet (.xlsx) with multiple worksheets. I want one worksheet as a Tabular with the proper column headers. Currently I use Import to produce a list of lists and use Tabular to convert it to a Tabular. This feels a bit roundabout and duplicative. Have I overlooked a more direct approach, so that I could import directly as a Tabular?

Edit note: added text in bold.

Edit: example of current code strategy

data = Import["fname.xlsx", {"Sheets", 3}]  (* get 3rd sheet *)
newtab = Tabular[Rest@data, First@data]
Improve this question
$\endgroup$

3 Answers 3

Reset to default
12
$\begingroup$

As written in the documentation for XLSX format, you can give the sheet specification directly to Import:

Import["fname.xlsx", {"Tabular", 3}]
Improve this answer
$\endgroup$
1
  • $\begingroup$ Yes! I felt this had to be possible but clearly did not understand the concept of a data representation element. $\endgroup$ Commented Mar 5 at 13:47
3
$\begingroup$

As you give no data, I make something up: A "test.xlsx" file with a first column of 4 "1", second column of 4 "2" and a third column of 4 "3".

To read this xlsx file and put it in a Tabular we can write:

Tabular[ Flatten[Import["d:/tmp/test.xlsx"], 1] ]

enter image description here

Improve this answer
$\endgroup$
1
$\begingroup$

You can use ResourceFunction["ImportSheetData"][path] to interactively select the sheet from your xlsx file and import it into Tabular.

An example:

path = FindFile["ExampleData/elements.xls"];
data = ResourceFunction["ImportSheetData"][path]

You can even use the function without providing the path to your file, as it will open a search window.

I hope it helps!

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.