[R] Multiple Data Import Excel

[Jeff Newmiller]

Reading Excel files isn't a feature of base R (the topic here... read the Posting Guide linked below) but the readxl contributed package can help with that. The read_xlsx function has arguments to skip rows, limit the number of rows read in, or specify regions to import such as B2:B2. Read the help documentation with ?read_xlsx to learn more.

You should always focus first on creating a function that can read one of these files as a data frame. With that done, lapply can be used with list.files to read in 2 or 20000 files. The do.call function can be used with rbind to assemble those data frames into one (or dplyr::bind_rows can do that in one call).

You may find [1] or [2] helpful with the process if not the details. I don't feel energetic enough to actually invent xlsx files for a reproducible example.

[1] https://jdnewmil.github.io/RLoopsAndGroups/DoItAgainR.html
[2] https://jdnewmil.github.io/FunctionsTalk/FunctionsTalk.html


On January 3, 2022 7:19:31 PM PST, Bradley Heins via R-help <r-help using r-project.org> wrote:
>Hello,
>I have 2100 Excel files (.xlsx) that I need to read and combine into 1
>file.
>I am perplexed because the first 6 lines are header information and the 8th
>line are the columns that are needed with the data in columns.
>
>I need to save the first last (IceTag ID) because that number becomes the
>ID for all of the data in each specific Excel file.  The ID can be
>different for spreadsheets.
>
>Line 2 to 7 are not needed.
>Line 8 are the column headers.
>
>The columns are Date, Time, Motion Standing (in time format), Lying (In
>time format), Steps and bouts.  See example below.
>
>Any help in reading in multiple files and discarding some lines would
>be appreciated.
>Regards,
>Brad
>
>
>
>IceTag ID: 61409782
>Site ID: n/a
>Animal ID: n/a
>First Record: 05/18/2021 14:04:27
>Last Record: 05/25/2021 14:00:51
>File Time Zone: Central Standard Time
>
>Date             Time    Motion StandingT LyingT Steps Bouts
>05/18/2021 14:04:27  65  0:10:29 0:00:04 20 1
>05/18/2021 14:15:00 69 0:08:52 0:06:08 15 1
>
>

-- 
Sent from my phone. Please excuse my brevity.