title: R - Import & Merge Multiple Excel Files And Add Filesource Variable
body: <p>I have used R for various things over the past year but due to the number of packages and functions available, I am still sadly a beginner. I believe R would allow me to do what I want to do with minimal code, but I am struggling.</p>
  
  <h3>What I want to do:</h3>
  
  <p>I have roughly a hundred different excel files containing data on students. Each excel file represents a different school but contains the same variables. I need to:<br /></p>
  
  <blockquote>
    <ol>
    <li>Import the data into R from Excel<br /></li>
    <li>Add a variable to each file containing the filename<br /></li>
    <li>Merge all of the data <em>(add observations/rows - do not need to match on variables)</em></li>
    </ol>
  </blockquote>
  
  <p>I will need to do this for multiple sets of data, so I am trying to make this as simple and easy to replicate as possible.</p>
  
  <h3>What the Data Look Like:</h3>
  
  <blockquote>
    <p><strong>Row 1</strong>&nbsp; &nbsp; Title<br>
    <strong>Row 2</strong>&nbsp; &nbsp; <em>StudentID&nbsp; &nbsp; Var1&nbsp; &nbsp; Var2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;  &nbsp; Var3&nbsp; &nbsp; &nbsp; Var4&nbsp; &nbsp; &nbsp; &nbsp; Var5</em><br />
    <strong>Row 3</strong>&nbsp; &nbsp; 11234 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp;  &nbsp; &nbsp; &nbsp;9/8/2011&nbsp; &nbsp; &nbsp; 343&nbsp; &nbsp; &nbsp; 159-167&nbsp; &nbsp; &nbsp; 32<br />
    <strong>Row 4</strong>&nbsp; &nbsp; 11235 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/16/2011&nbsp; &nbsp; 112&nbsp; &nbsp; &nbsp; 152-160&nbsp; &nbsp; &nbsp; 12<br />
    <strong>Row 5</strong>&nbsp; &nbsp; 11236 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/8/2011&nbsp; &nbsp; &nbsp; 325&nbsp; &nbsp; &nbsp; 164-171&nbsp; &nbsp; &nbsp; 44</p>
  </blockquote>
  
  <p>Row 1 is meaningless and Row 2 contains the variable names. The files have different numbers of rows.</p>
  
  <h3>What I have so far:</h3>
  
  <p>At first I simply tried to import data from excel. Using the XLSX package, this works nicely:</p>
  
  <pre><code>dat &lt;- read.xlsx2("FILENAME.xlsx", sheetIndex=1, 
                    sheetName=NULL, startRow=2, 
                    endRow=NULL, as.data.frame=TRUE, 
                    header=TRUE)
  </code></pre>
  
  <p>Next, I focused on figuring out how to merge the files (also thought this is where I should add the filename variable to the datafiles). This is where I got stuck. </p>
  
  <pre><code>setwd("FILE_PATH_TO_EXCEL_DIRECTORY")
  filenames &lt;- list.files(pattern=".xls")
  do.call("rbind", lapply(filenames, read.xlsx2, sheetIndex=1, colIndex=6, header=TRUE, startrow=2, FILENAMEVAR=filenames));
  </code></pre>
  
  <p>I set my directory, make a list of all the excel file names in the folder, and then try to merge them in one statement using the a variable for the filenames.</p>
  
  <p>When I do this I get the following error:</p>
  
  <blockquote>
    <p>Error in data.frame(res, ...) : 
     arguments imply differing number of rows: 616, 1, 5</p>
  </blockquote>
  
  <p>I know there is a problem with my application of lapply - the startrow is not being recognized as an option and the FILENAMEVAR is trying to merge the list of 5 sample filenames as opposed to adding a column containing the filename.</p>
  
  <h3>What next?</h3>
  
  <p>If anyone can refer me to a useful resource or function, critique what I have so far, or point me in a new direction, it would be GREATLY appreciated!</p>