excel file merge with sheets in python
<p>I am trying to append many excel files with sheet1 and sheet2.
I have written the following code</p>

<pre><code>import os
import pandas as pd

files = os.listdir("C:/Python27/files")
files
df = pd.DataFrame()
for f in files:
    data = pd.read_excel(f, 'Sheet1', 'Sheet2')
    df = df.append(data)
</code></pre>

<p>example names of the files :Total Apr 2014,Total Aug 2014</p>

<p>The following is the error:</p>

<pre><code>Traceback (most recent call last):
  File "C:/Python27/filemerge2.py", line 10, in &lt;module&gt;
    data = pd.read_excel(f, 'Sheet1', 'Sheet2')
  File "C:\Python27\lib\site-packages\pandas\io\excel.py", line 170, in read_excel
    io = ExcelFile(io, engine=engine)
  File "C:\Python27\lib\site-packages\pandas\io\excel.py", line 227, in __init__
    self.book = xlrd.open_workbook(io)
  File "C:\Python27\lib\site-packages\xlrd\__init__.py", line 395, in open_workbook
    with open(filename, "rb") as f:
IOError: [Errno 2] No such file or directory: 'Total Apr 2014.xls'
</code></pre>

<p>It would be great if someone can help me out with this error</p>

<p>Python <code>os.listdir</code> will return a list containing the <strong>relative names</strong> of files inside the given directory. If you are running your script from a folder other than <code>"C:/Python27/files"</code> (or whatever folder your xls files are located), then you need to give it the full path to the required file to the <code>read_excel()</code> function (or whatever file management function you call).</p>

<p>To make your script work, just append the base folder to the file name and it should work:</p>

<pre><code>import os
import pandas as pd

folder = "C:/Python27/files"
files = os.listdir(folder)
files
df = pd.DataFrame()
for f in files:
    data = pd.read_excel(folder + '/' + f, 'Sheet1', 'Sheet2')
    df = df.append(data)
</code></pre>

<p>Notice that <code>os.listdir</code> will return all files and directories inside <code>folder</code>, regardless of their types. Make sure to perform some file type checking before trying to open it with <code>read_excel()</code> (or just use a <code>try-except</code> block around the contents of your for loop that depend on this call).</p>

python excel