Merge xlsx data files

elienbellon

Hi!

I have several times 150 csv files for different experiments that were created with open sesame. Due to some errors during datacollection the "subject number" of several files are not correct.

To overcome this issue, I have made xlsx files based on the csv files, I have added a column "Subject" with the correct subject number for each file, and finally added all these corrected xlsx files to **one new folder. **

I want to merge all these files to one big "masterfile". I have tried different options, for example the data merger program. The program indicates it the merge was successful, but once I open the created csv file, it only contains the title cells, without any other data.

Can somebody help? Thanks in advance!

Pascal

Hi,
if you know / use R, you could use the read_bulk function from the readbulk package to merge all xlsx files.
The command would look something like this assuming that you have one folder named raw_data that contains all xlsx files to merge.

library(readbulk)
library(readxl)
dataset <- read_bulk(directory = "raw_data",  fun=read_xlsx)

Best,
Pascal

elienbellon

Hi,
Thank you very much for your answer. I have tried this and I get this: Warning in read_bulk(directory = "directory", fun = read_xlsx) :
Final data.frame has 0 rows.

Unfortunatly, I am not an R expert. Is there another way?

eduard

Hi Ellion,

Is your folder with all the corrected files called "directory"? If not, you have to put the correct folder name it in that field.

In case, this is not the problem, could you upload two or three of the corrected files?

Eduard

Pascal

Hi,

I thought it might be helpful to provide a bit more information on the process of reading the data into R (in general and using readbulk in particular). If you read any file into R, R by default looks in the current working directory. You can determine what is the current directory using the following command:

getwd()

To change the working directory, you can use the setwd() function or, if you use RStudio, you can change it via the menu (Session/Set Working Directory/Choose Directory...).

It is important that you change the current working directory to the folder that contains the folder in which your files are stored.

Then you can use the command from above:

library(readbulk)
library(readxl)
dataset <- read_bulk(directory = "raw_data",  fun=read_xlsx)

Where it says "raw_data" you need to enter the name of the folder that contains the raw data.

Best,

Pascal