CODEWITHSUNDEEP
rexcelcsvxlsxxlsxwriter
M.Qasim
M.Qasim

Reputation: 1878

Write multiple excel/csv files from a large data.frame

I have a large data frame of over 500,000 rows. I wish to export it to multiple excel/csv files with a certain row limit (e.g. 100,000 rows).

My solution is:

library(openxlsx)
write.xlsx(df[1:100000,], "path")
write.xlsx(df[100001:200000,], "path")
write.xlsx(df[200001:300000,], "path")
write.xlsx(df[300001:400000,], "path")
write.xlsx(df[400001:500000,], "path")

Is there any elegant way to do it? Thanks

Upvotes: 0

Views: 1290

Answers (2)

Brandon Bertelsen
Brandon Bertelsen

Reputation: 44698

You could try something like...

breaks <- split(1:nrow(df), ceiling(seq_along(1:nrow(df))/1e5))
lapply(breaks, 
   function(x)                  
     write.xlsx(
          df[x,],
          paste0("path",substr(x[1],1,1),".xlsx")
)

Change 1e5 to change the number of rows to include in each break. Should output something like path1.xlsx,path2.xlsx, ...

Upvotes: 6

arturro
arturro

Reputation: 1606

batchSize=100; # number of rows in single output file
filename="file_name";  # prefix of output file

chunks=split(df, floor(0:(nrow(df)-1)/batchSize))
for (i in 1:length(chunks))
{
  write.xlsx(chunks[[i]],paste0(filename,i,'.xlsx'))
}

Above will create file_name1.xlsx, file_name2.xlsx, .... etc. in your working directory.

Upvotes: 3

Related Questions