1
$\begingroup$

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

| improve this question | |
$\endgroup$
  • 1
    $\begingroup$ Have you tried pandas? The read_excel function should work; if not, convert everything to csv and use read_csv. $\endgroup$ – vbox Dec 31 '16 at 5:47
  • $\begingroup$ What are you planning to do with the data? Can you distribute it onto a cluster? $\endgroup$ – Spacedman Dec 31 '16 at 13:58
  • $\begingroup$ Programming languages Python and R are perfectly capable to handle these amounts of data. Using a programming language has the advantage that they can easily be applied on all files. $\endgroup$ – Pieter Jan 1 '17 at 13:21
  • $\begingroup$ The way you store your data should not matter. All sensible tools can handle excel. $\endgroup$ – Pieter Jan 1 '17 at 13:23
2
$\begingroup$

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.

| improve this answer | |
$\endgroup$
  • $\begingroup$ Thanks Rahul for your answer! csv is indeed a far faster data format to read compared to excel, even for Matlab. Just like to add that using vectorized operations also saved much more time compared to looping each observation. $\endgroup$ – Justin Jan 29 '17 at 6:33
1
$\begingroup$

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)
}
| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.