Is there an R function for pulling data(it is actually not database, the data is unstructured that has sentences) from 2 excel files

0
votes

There are 2 excel files. Each excel file has different sheets. Lets say, there are 2 excel files in my working directory. File1 and File2. File1 has 4 sheets and File2 has 5 sheets. All the sheet names have different names.

Example: File1(Price, Planning, contract, delivery) File2(CND, Discount, Installation, Price, Affidavit)

Now i need to extract the above data in R. I Need to have finally 2 files in R with object name coming as File names and also Sheet name getting reflected in the data.

So for example. My final R files should be like

1) "File1" that has all the data(from all the 5 sheets) from Excel File1 2) "File2" that has all the data(from all the 4 sheets) from Excel File2

Can we have something like this?

1
r

1 Answers

1
votes

You could use package openxlsx to open the Excel files. To load all sheets into a structure you could do:

library(openxlsx)

sheetNames = names(loadWorkbook("Book1.xlsx"))
File_1 = lapply(sheetNames, function(x){read.xlsx("Book1.xlsx",sheet = x)})
names(File_1) = sheetNames

This will read the sheet names from the 'Book1.xlsx' file and then proceed to read all data into File_1. The last line will add the sheets' name to each element in the list.

The structure you get is a list. For example:

> File_1
$Sheet1
  1 2
1 3 4

$Sheet2
  1 2
1 1 2
2 1 2

Edit:

If you want to read all Excel files and store them in the same structure do:

library(openxlsx)
filenames = list.files(pattern = "\\.xlsx$")

fullData = lapply(filenames, function(fil){
  sheetNames = names(loadWorkbook(fil))
  File_data = lapply(sheetNames, function(x){read.xlsx(fil,sheet = x)})
  names(File_data) = sheetNames
  File_data
})

names(fullData) = filenames

Explanation:

lapply will loop over all filenames that end in .xlsx and for each one:

  • read each workbook's sheet names
  • load the sheet data
  • add the sheet name

And after reading all files, the name of each file is added to the result.

Result (for two files created for this):

> fullData
$Book1.xlsx
$Book1.xlsx$Sheet1
  1 2
1 3 4

$Book1.xlsx$Sheet2
  1 2
1 1 2
2 1 2


$Book2.xlsx
$Book2.xlsx$Income
  11 22 55 77
1 33 44 66 88

$Book2.xlsx$Expenses
  3 55
1 3 55
2 3 55