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 -- Bradley J. Heins Extension Specialist, Dairy Management | Extension | extension.umn.edu Associate Professor, Dairy Management | West Central ROC | wcroc.cfans.umn.edu University of Minnesota | umn.edu 46352 State Hwy 329, Morris, MN 56267 hein0106 at umn.edu | 320-589-1711, Ext. 2118 [[alternative HTML version deleted]]
Hi Try readxl package which has possibility to limit range of cells for reading. https://cran.r-project.org/web/packages/readxl/readxl.pdf Cheers Petr> -----Original Message----- > From: R-help <r-help-bounces at r-project.org> On Behalf Of Bradley Heins via > R-help > Sent: Tuesday, January 4, 2022 4:20 AM > To: r-help at r-project.org > Subject: [R] Multiple Data Import Excel > > Hello, > I have 2100 Excel files (.xlsx) that I need to read and combine into 1file.> I am perplexed because the first 6 lines are header information and the8th> 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 theID> for all of the data in each specific Excel file. The ID can be differentfor> 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 (Intime> 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 > > > -- > Bradley J. Heins > Extension Specialist, Dairy Management | Extension | extension.umn.edu > Associate Professor, Dairy Management | West Central ROC | > wcroc.cfans.umn.edu University of Minnesota | umn.edu > 46352 State Hwy 329, Morris, MN 56267 > hein0106 at umn.edu | 320-589-1711, Ext. 2118 > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting- > guide.html > and provide commented, minimal, self-contained, reproducible code.
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 at 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.