Python Forum
Add a new column when I extract each sheet in an Excel workbook as a new csv file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Add a new column when I extract each sheet in an Excel workbook as a new csv file
#1
Below is a python script is to extract each sheet in an Excel workbook as a new csv file.Want to Add a new column when I extract each sheet in an Excel workbook as a new csv file. The new column is added at the end of last column of every file. Can anyone help me how to do this??
'''This python script is to extract each sheet in an Excel workbook as a new csv file'''
import csv
import xlrd
import sys
import pandas as pd

def ExceltoCSV(excel_file, csv_file_base_path):
    workbook = xlrd.open_workbook(excel_file)
    for sheet_name in workbook.sheet_names():
        print('processing - ' + sheet_name)
        worksheet = workbook.sheet_by_name(sheet_name)
        csv_file_full_path = csv_file_base_path + sheet_name.lower().replace(" - ", "_").replace(" ","_") + '.csv'
        csvfile = open(csv_file_full_path, 'w')
        writetocsv = csv.writer(csvfile, quoting = csv.QUOTE_MINIMAL)
        for rownum in range(worksheet.nrows):
            writetocsv.writerow(
                list(x.encode('utf-8') if type(x) == type(u'') else x for x in worksheet.row_values(rownum)
                )
            )
            #writetocsv.writerow(rownum.append(sheet_name)) #Adding a new column
        csvfile.close()


if __name__ == '__main__':
    ExceltoCSV(excel_file = sys.argv[1], csv_file_base_path = sys.argv[2])
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 243 Aug-29-2021, 12:39 PM
Last Post: snippsat
  Python Pandas: How do I extract all the >1000 data from a certain column? JaneTan 0 294 Jul-17-2021, 09:09 AM
Last Post: JaneTan
  Verification of an excel file saleenahohn 2 257 Jul-09-2021, 06:56 AM
Last Post: Larz60+
  Want to remove the text from a particular column in excel shantanu97 2 369 Jul-05-2021, 05:42 PM
Last Post: eddywinch82
  Read and write active Excel file euras 4 522 Jun-29-2021, 11:16 PM
Last Post: Pedroski55
  [Solved] Reading every nth line into a column from txt file Laplace12 7 773 Jun-29-2021, 09:17 AM
Last Post: Laplace12
  Open an excel file Newbie1114 1 532 Jun-16-2021, 09:11 PM
Last Post: Gribouillis
  Python script for excel sheet Nabil 5 692 Jun-01-2021, 02:29 PM
Last Post: anuradha
  Extract specific sentences from text file Bubly 3 527 May-31-2021, 06:55 PM
Last Post: Larz60+
  Filter Excel and Convert an Excel File giddyhead 0 550 May-13-2021, 06:31 PM
Last Post: giddyhead

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020