Message Boards Message Boards

0
|
1000 Views
|
5 Replies
|
2 Total Likes
View groups...
Share
Share this post:

Import only specfic columns and rows from XLSX file

Ehud Behar
Posted 5 months ago

I have the file myfile.xlsx file that looks like the following:

enter image description here

I want to use Import to create a dataset that drops the first row ("File generated automatically by software"), contains the second row as headers, imports only the 4th to 7th rows, and only the 2nd and 3rd columns.

I need help with specifying the columns to be imported.

What I have now is

Import[
 "myfile.xlsx",
 {"XLSX", "Dataset", "Sheet1" ,
  2 ;; (*drops the first row*)},
 HeaderLines -> 1
 ]

which gives all columns and all rows except of the first. I then manipulate it to create a new variable that has only the rows and columns I need.

But I am looking for a way to achieve this only using Import.

Any help?

Besides, when I do 

Import[
 "myfile.xlsx",
 {"XLSX", "Dataset", "Sheet1" ,
  {1, 1}}
 ]

I get

enter image description here

Why the double rows? Shouldn't it import only the first row of the first column?
POSTED BY: Ehud Behar
5 Replies
Sort By:
Ehud Behar
Ehud Behar
Posted 5 months ago

Thanks.

My question is about specyfying rows to be imported.

Can I import only row 2 (which will be used as a header) and rows 4 to 7?

Something like

Import["data.xlsx", {
  "Dataset",
  1,(*in sheet no. 1*)
  {2, 5 ;; 7},(*import row 2 and rows 4 to 7*)
  2 ;; 3}, (*import only 2nd and 3rd columns *)
 "SkipLines" -> 
  1, (*skip the message automatically generated by software*)
 HeaderLines -> 1 (*use current and voltage as headers*)
 ]

?
POSTED BY: Ehud Behar
Rohit Namjoshi
Rohit Namjoshi
Posted 5 months ago

A Span cannot be combined with an Integer. You can explicitly enumerate the rows

Import["data.xlsx",
 {"Dataset", 1, {1, 2, 4, 5, 6}, 2 ;; 3},
 "SkipLines" -> 1,
 HeaderLines -> 1 ]
POSTED BY: Rohit Namjoshi
Ehud Behar
Ehud Behar
Posted 5 months ago

That solves the confusion.
POSTED BY: Ehud Behar
Rohit Namjoshi
Rohit Namjoshi
Posted 5 months ago

You can convert a simple Span to a Range using Apply

Range @@ (4 ;; 7)
(* {4, 5, 6, 7}  *)

and use it to build the list. This might be useful if you have several disjoint Span

{1, 2, Sequence @@ Range @@ (4 ;; 7), Sequence @@ Range @@ (10 ;; 13)}
(* {1, 2, 4, 5, 6, 7, 10, 11, 12, 13} *)
POSTED BY: Rohit Namjoshi
Rohit Namjoshi
Rohit Namjoshi
Posted 5 months ago

I don't think skipping row 3 can be done during import. You did not provide the file so we make one

SeedRandom[123];
data = RandomReal[1, {10, 3}]

fileData = {{"File generated automatically by software", "", ""}, {"t", "current", "voltage"}}~Join~data
Export["data.xlsx", fileData]

dataset = 
  Import["data.xlsx", {"Dataset", 1, 1 ;; 7, 2 ;; 3}, 
    "SkipLines" -> 1,
     HeaderLines -> 1] // Rest
POSTED BY: Rohit Namjoshi
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract