title: I am getting error in using the Python script which is designed to split Excel based on column categories
body: <p>Please help, I am trying to create separate files based on company column, I ran the filescript using CMD,</p>
  <p>This screenshot is a sample of my data, where in every day I have to filter and create new Excel file according to company and send email to, I want to automate this with Python:</p>
  <p><img src="https://i.stack.imgur.com/QQE3M.png" alt="Screenshot" /></p>
  <p>I am getting error when separating as file</p>
  <p>C:\Users\sudarshan\Desktop&gt;py filesplit.py</p>
  <p>File Path: C:\Users\sudarshan\Desktop\Sample.xlsx</p>
  <p>Select Column: Company</p>
  <p>You data will split based on these values Medlife B2B Cargo, TWIN BIRDS, Allied Moulded Enclosure Products, JMZ CARGO PVT LTD and create 4 files or sheets based on next selection. If you are ready to proceed please type &quot;Y&quot; and hit enter. Hit &quot;N&quot; to exit.
  Ready to Proceed (Y/N): y</p>
  <p>Split into different Sheets or File (S/F): F</p>
  <p>Error starts from here and this error message show after i input &quot;F&quot; for the above line.</p>
  <pre><code>Traceback (most recent call last):
    File &quot;C:\Users\sudarshan\Desktop\filesplit.py&quot;, line 49, in &lt;module&gt;
      sendtofile(cols)
    File &quot;C:\Users\sudarshan\Desktop\filesplit.py&quot;, line 24, in sendtofile
      df[df[colpick] == i].to_excel(&quot;{}/{}.xlsx&quot;.format(pth, i), sheet_name=i, index=False)
    File &quot;C:\python\lib\site-packages\pandas\core\generic.py&quot;, line 2188, in to_excel
      formatter.write(
    File &quot;C:\python\lib\site-packages\pandas\io\formats\excel.py&quot;, line 821, in write
      writer.write_cells(
    File &quot;C:\python\lib\site-packages\pandas\io\excel\_xlsxwriter.py&quot;, line 209, in write_cells
      wks = self.book.add_worksheet(sheet_name)
    File &quot;C:\python\lib\site-packages\xlsxwriter\workbook.py&quot;, line 184, in add_worksheet
      return self._add_sheet(name, worksheet_class=worksheet_class)
    File &quot;C:\python\lib\site-packages\xlsxwriter\workbook.py&quot;, line 735, in _add_sheet
      name = self._check_sheetname(name, isinstance(worksheet, Chartsheet))
    File &quot;C:\python\lib\site-packages\xlsxwriter\workbook.py&quot;, line 784, in _check_sheetname
      raise InvalidWorksheetName(
  xlsxwriter.exceptions.InvalidWorksheetName: Excel worksheet name 'Allied Moulded Enclosure Products' must be &lt;= 31 chars.
  </code></pre>
  <p>below is the python script for filesplit</p>
  <pre><code>#!/usr/bin/env python
  # coding: utf-8
  
  # In[ ]:
  
  
  import pandas as pd
  import os
  from openpyxl import load_workbook
  import xlsxwriter
  from shutil import copyfile
  
  file=input('File Path: ')
  extension = os.path.splitext(file)[1]
  filename = os.path.splitext(file)[0]
  pth=os.path.dirname(file)
  newfile=os.path.join(pth,filename+'_2'+extension)
  df=pd.read_excel(file)
  colpick=input('Select Column: ')
  cols=list(set(df[colpick].values))
  
  def sendtofile(cols):
      for i in cols:
          df[df[colpick] == i].to_excel(&quot;{}/{}.xlsx&quot;.format(pth, i), sheet_name=i, index=False)
      print('\nCompleted')
      print('Thanks for using this program.')
      return
  
  def sendtosheet(cols):
      copyfile(file, newfile)
      for j in cols:
          writer = pd.ExcelWriter(newfile, engine='openpyxl')
          for myname in cols:
              mydf = df.loc[df[colpick] == myname]
              mydf.to_excel(writer, sheet_name=myname, index=False)
          writer.save()
  
      print('\nCompleted')
      print('Thanks for using this program.')
      return
  
  print('You data will split based on these values {} and create {} files or sheets based on next selection. If you are ready to proceed please type &quot;Y&quot; and hit enter. Hit &quot;N&quot; to exit.'.format(', '.join(cols),len(cols)))
  while True:
      x=input('Ready to Proceed (Y/N): ').lower()
      if x == 'y':
          while True:
              s = input('Split into different Sheets or File (S/F): ').lower()
              if s == 'f':
                  sendtofile(cols)
                  break
              elif s == 's':
                  sendtosheet(cols)
                  break
              else: continue
          break
      elif x=='n':
          print('\nThanks for using this program.')
          break
  
      else: continue
  
  
  # In[ ]:
  </code></pre>
  <p>Files link
  <a href="https://github.com/sudarshan0091/filesplit" rel="nofollow noreferrer">https://github.com/sudarshan0091/filesplit</a></p>
  <p>Please note this script is not mine got it from github, it is of youtube trainer.</p>