xls to csv converter

Question

I am using win32 client in python for converting my  xlsx and  xls file into a  csv  When I execute this code it s giving an error  My code is   def convertXLS2CSV aFile          converts a MS Excel file to csv w  the same name in the same directory         print  ------ beginning to convert XLS to CSV ------       try          import win32com client  os         from win32com client import constants as c         excel   win32com client Dispatch  Excel Application            fileDir  fileName   os path split aFile          nameOnly   os path splitext fileName          newName   nameOnly 0      csv          outCSV   os path join fileDir  newName          workbook   excel Workbooks Open aFile          workbook SaveAs outCSV  c xlCSVMSDOS    24 represents xlCSVMSDOS         workbook Close False          excel Quit           del excel          print     Converted     nameOnly     to CSV      except          print   gt  gt  gt  gt  gt  gt  gt  FAILED to convert     aFile     to CSV    convertXLS2CSV  G   hello xlsx     I am not able to find the error in this code  Please help

User · Answer

First read your excel spreadsheet into pandas, below code will import your excel spreadsheet into pandas as a OrderedDict type which contain all of your worksheet as dataframes. Then simply use worksheet_name as a key to access specific worksheet as a dataframe and save only required worksheet as csv file by using df.to_csv(). Hope this will workout in your case.

import pandas as pd
df = pd.read_excel('YourExcel.xlsx', sheet_name=None)
df['worksheet_name'].to_csv('YourCsv.csv')

If your Excel file contain only one worksheet then simply use below code:

import pandas as pd
df = pd.read_excel('YourExcel.xlsx')
df.to_csv('YourCsv.csv')

If someone want to convert all the excel worksheets from single excel workbook to the different csv files, try below code:

import pandas as pd
def excelTOcsv(filename):
    df = pd.read_excel(filename, sheet_name=None)  
    for key, value in df.items(): 
        return df[key].to_csv('%s.csv' %key)

This function is working as a multiple Excel sheet of same excel workbook to multiple csv file converter. Where key is the sheet name and value is the content inside sheet.

User · Answer

I would use pandas  The computationally heavy parts are written in cython or c-extensions to speed up the process and the syntax is very clean  For example  if you want to turn  Sheet1  from the file  your workbook xls  into the file  your csv csv   you just use the top-level function read excel and the method to csv from the DataFrame class as follows   import pandas as pd data xls   pd read excel  your workbook xls    Sheet1   index col None  data xls to csv  your csv csv   encoding  utf-8     Setting encoding  utf-8  alleviates the UnicodeEncodeError mentioned in other answers

User · Answer

As much as I hate to rely on Windows Excel proprietary software  which is not cross-platform  my testing of csvkit for  xls  which uses xlrd under the hood  failed to correctly parse dates  even when using the commandline parameters to specify strptime format    For example  this xls file  when parsed with csvkit  will convert cell G1 of 12 31 2002 to 37621  whereas when converted to csv via excel -  save as  using below  cell G1 will be  December 31  2002    import re import os from win32com client import Dispatch xlCSVMSDOS   24  class CsvConverter object       def   init   self     input dir  output dir           self  excel   None         self input dir   input dir         self output dir   output dir          if not os path isdir self output dir               os makedirs self output dir       def isSheetEmpty self  sheet             https   archive is RuxR7           WorksheetFunction CountA ActiveSheet UsedRange    0 And ActiveSheet Shapes Count   0          return                not self  excel WorksheetFunction CountA sheet UsedRange                 and                not sheet Shapes Count       def getNonEmptySheets self  wb  as name False           return                  sheet Name if as name else sheet                for sheet in wb Sheets               if not self isSheetEmpty sheet                   def saveWorkbookAsCsv self  wb  csv path           non empty sheet names   self getNonEmptySheets wb  as name True           assert  len non empty sheet names     1                  Expected exactly 1 sheet but found  i non-empty sheets    s                                    len non empty sheet names                          join name replace      r      for name in non empty sheet names                         wb Worksheets non empty sheet names 0   SaveAs csv path  xlCSVMSDOS          wb Saved   1      def isXlsFilename self  filename           return bool re search r   i   xls    filename        def batchConvertXlsToCsv self           xls names   tuple  filename for filename in next os walk self input dir   2  if self isXlsFilename filename             self  excel   Dispatch  Excel Application           try              for xls name in xls names                  csv path   os path join self output dir    s csv   os path splitext xls name  0                   if not os path isfile csv path                       workbook   self  excel Workbooks Open os path join self input dir  xls name                       try                          self saveWorkbookAsCsv workbook  csv path                      finally                          workbook Close           finally              if not len self  excel Workbooks                   self  excel Quit                self  excel   None  if   name         main         self   CsvConverter          input dir  C   data  xls             output dir  C   data  csv               self batchConvertXlsToCsv     The above will take an input dir containing  xls and output them to output dir as  csv -- it will assert that there is exactly 1 non-empty sheet in the  xls  if you need to handle multiple sheets into multiple csv then you ll need to edit saveWorkbookAsCsv

User · Answer

I d use csvkit  which uses xlrd  for xls  and openpyxl  for xlsx  to convert just about any tabular data to csv   Once installed  with its dependencies  it s a matter of   python in2csv myfile  gt  myoutput csv   It takes care of all the format detection issues  so you can pass it just about any tabular data source  It s cross-platform too  no win32 dependency

User · Answer

I would use xlrd - it s faster  cross platform and works directly with the file  One thing to note - it doesn t work on xlsx files - so you d have to save your Excel file as xls  Edit  As of version 0 8 0  xlrd reads both XLS and XLSX files   import xlrd import csv  def csv from excel        wb   xlrd open workbook  your workbook xls       sh   wb sheet by name  Sheet1       your csv file   open  your csv file csv    wb       wr   csv writer your csv file  quoting csv QUOTE ALL       for rownum in xrange sh nrows           wr writerow sh row values rownum        your csv file close

User · Answer

Maybe someone find this ready-to-use piece of code useful  It allows to create CSVs from all spreadsheets in Excel s workbook       - - coding  utf-8 - - import xlrd import csv from os import sys  def csv from excel excel file       workbook   xlrd open workbook excel file      all worksheets   workbook sheet names       for worksheet name in all worksheets          worksheet   workbook sheet by name worksheet name          with open u    csv  format worksheet name    wb   as your csv file              wr   csv writer your csv file  quoting csv QUOTE ALL              for rownum in xrange worksheet nrows                   wr writerow  unicode entry  encode  utf-8   for entry in worksheet row values rownum     if   name         main         csv from excel sys argv 1

User · Answer

andi I tested your code  it works great  BUT  In my sheets there s a column like this  2013-03-06T04 00 00  date and time in the same cell  It gets garbled during exportation  it s like this in the exported file  41275 0416667  other columns are ok   csvkit  on the other side  does ok with that column but only exports ONE sheet  and my files have many

User · Answer

Quoting an answer from Scott Ming  which works with workbook containing multiple sheets   Here is a python script getsheets py  mirror   you should install pandas and xlrd before you use it   Run this   pip3 install pandas xlrd    or  pip install pandas xlrd    How does it works     python3 getsheets py -h Usage  getsheets py  OPTIONS  INPUTFILE  Convert a Excel file with multiple sheets to several file with one sheet   Examples       getsheets filename      getsheets filename -f csv  Options  -f  --format  xlsx csv   Default xlsx  -h  --help               Show this message and exit    Convert to several xlsx     python3 getsheets py goods temp xlsx Sheet xlsx Done  Sheet1 xlsx Done   All Done    Convert to several csv     python3 getsheets py goods temp xlsx -f csv Sheet csv Done  Sheet1 csv Done   All Done    getsheets py     - - coding  utf-8 - -  import click import os import pandas as pd   def file split file       s   file split          name       join s  -1      get directory name     return name   def getsheets inputfile  fileformat       name   file split inputfile      try          os makedirs name      except          pass      df1   pd ExcelFile inputfile      for x in df1 sheet names          print x         fileformat   Done            df2   pd read excel inputfile  sheetname x          filename   os path join name  x         fileformat          if fileformat     csv               df2 to csv filename  index False          else              df2 to excel filename  index False      print   nAll Done      CONTEXT SETTINGS   dict help option names   -h    --help       click command context settings CONTEXT SETTINGS   click argument  inputfile    click option  -f    --format   type click Choice        xlsx    csv     default  xlsx   help  Default xlsx    def cli inputfile  format          Convert a Excel file with multiple sheets to several file with one sheet       Examples        b         getsheets filename       b         getsheets filename -f csv             if format     csv           getsheets inputfile   csv       else          getsheets inputfile   xlsx     cli

User · Answer

Using xlrd is a flawed way to do this  because you lose the Date Formats in Excel   My use case is the following   Take an Excel File with more than one sheet and convert each one into a file of its own   I have done this using the xlsx2csv library and calling this using a subprocess   import csv import sys  os  json  re  time import subprocess  def csv from excel fname       subprocess Popen   xlsx2csv     fname     --all -d     -i -p                            lt New Sheet gt    gt       test csv    shell True       return  lstSheets   csv from excel sys argv 1    time sleep 3    system needs to wait a second to recognize the file was  written  with open   YOUR PATH  test csv   as f      lines   f readlines       firstSheet   True      for line in lines          if line startswith   lt New Sheet gt                 if firstSheet                  sh 2 fname   line replace   lt New Sheet gt        strip   replace   -         replace                          print sh 2 fname                  sh2f   open sh 2 fname   csv    w                   firstSheet   False             else                  sh2f close                   sh 2 fname   line replace   lt New Sheet gt        strip   replace   -         replace                          print sh 2 fname                  sh2f   open sh 2 fname   csv    w           else              sh2f write line  sh2f close

User · Answer

We can use Pandas lib of Python to conevert xls file to csv file Below code will convert xls file to csv file   import pandas as pd  Read Excel File from Local Path    df   pd read excel  C  Users IBM ADMIN BU GPA Scorecard xlsx  sheetname 1    Trim Spaces present on columns    df columns   df columns str strip     Send Data frame to CSV file which will be pipe symbol delimted and without Index    df to csv  C  Users IBM ADMIN BU GPA Scorecard csv csv  sep     index False

User · Answer

I ve tested all anwers  but they were all too slow for me  If you have Excel installed you can use the COM    I thought initially it would be slower since it will load everything for the actual Excel application  but it isn t for huge files  Maybe because the algorithm for opening and saving files runs a heavily optimized compiled code  Microsoft guys make a lot of money for it after all   import sys import os import glob from win32com client import Dispatch  def main path       excel   Dispatch  Excel Application       if is full path path           process file excel  path      else          files   glob glob path          for file path in files              process file excel  file path      excel Quit    def process file excel  path       fullpath   os path abspath path      full csv path   os path splitext fullpath  0      csv      workbook   excel Workbooks Open fullpath      workbook Worksheets 1  SaveAs full csv path  6      workbook Saved   1     workbook Close     def is full path path       return path find       gt  -1  if   name         main         main sys argv 1     This is very raw code and won t check for errors  print help or anything  it will just create a csv file for each file that matches the pattern you entered in the function so you can batch process a lot of files only launching excel application once

User · Answer

Python is not the best tool for this task  I tried several approaches in Python but none of them work 100   e g  10  converts to 0 1  or column types are messed up  etc   The right tool here is PowerShell  because it is an MS product  as is Excel  and has the best integration  Simply download this PowerShell script  edit line 47 to enter the path for the folder containing the Excel files and run the script using PowerShell

User · Answer

xlsx2csv is faster than pandas and xlrd   xlsx2csv -s 0 crunchbase monthly  xlsx cruchbase   excel file usually comes with n sheetname   -s is sheetname index    then  cruchbase folder will be created  each sheet belongs to xlsx will be converted to a single csv   p s  csvkit is awesome too

[python] xls to csv converter

Examples related to python

Examples related to excel

Examples related to csv

Examples related to xls

Examples related to export-to-csv