What are recommended ways\tools for processing large data from Excel Files?

Question

A Very Happy New Year! I'm currently working on an analytics project with large volumes of data stored in excel files (about 50GB in 1000 files). The files use a custom formatting to store date-time data to the millisecond. The processing also has to be efficient in view of the large data volume. What is the recommended methodology and tool to handle this?

I've seen others convert excel to CSV, and then confining their analysis to the CSV itself. Is this method advantageous in the sense that CSV is faster to read in, and can be processed by a larger toolset? Is there any powerful tool\library that can do batch conversion, and even extract custom formatting data?

Thanks and Regards

Answer 1

pandas loads a csv upto 10x faster than excel. So, if you can, please convert these files to csv, which ever ones that are being loaded several times.

I have been using cython, which seems to speedup read functions and processing. Please follow this link for more details. It says, that using cython speeds up the processing 10x.

Suggestion: If you can, I would recommend to store data in database and use an orm like django/peewee for the data processing.

Please lemme know if you have any other queries.

Answer 2

If you want to stay in Excel format without converting to CSV first, have a look at the readxl package in the R programming environment. Since you have multiple files to read in and assuming they are in the same folder, I would use a for loop and iterate over all the file names in the folder. Then if all the files have the same variables you can bind them together into one data frame. Available memory may limit your ability to bind everything together in one frame though. You may also need to modify the read_excel statement depending on how your spreadsheets are structured.

setwd("directory_path") # Set the working directory

# List all the excel (xlsx) files in the directory

files <- list.files("directory_path", full.names = TRUE, pattern="*.xlsx")

# Create empty "collector" data frame

df <- data.frame()

# loop through files in subset list and read the xlsx file and bind to "collector" data frame

for (i in 1:length(files)) {
  tempdf <- readxl::read_excel(files[i], sheet = 1, col_names = TRUE, col_types = NULL, na = "", skip = 0)
  df <- rbind(df,tempdf)
}