Python Using Pandas To Convert Xlsx To Csv File. How To Delete Index Column?

I am using the following code to convert .xlsx files into .csv files. import pandas as pd data_xls = pd.read_excel('excelfile.xlsx', 'Sheet2', index_col=None) data_xls.to_csv('cs

Solution 1:

As noted in the docs for pandas.DataFrame.to_csv(), simply pass index=False as a keyword argument, to exclude row names.

data_xls.to_csv('csvfile.csv', encoding='utf-8', index=False)

Solution 2:

Inspired by miradulo and fix a number conversion problem:

import pandas as pd
data_xls = pd.read_excel('excelfile.xlsx', 'Sheet2', dtype=str, index_col=None)
data_xls.to_csv('csvfile.csv', encoding='utf-8', index=False)

Can drop 'Sheet2' if there is one sheet. dtype=str to avoid number conversion.

Solution 3:

I just answered a function that can do the work in another SO question in here.

I had to use the openpyxl library to convert the xlsx format into csv due to deprecated from xlrd library. In order to work the function below, don't forget to install the openpyxl library from here.

Function:

import os
import pathlib
import pandas as pd

# Function to convert excel spreadsheet into csv formatdefExcel_to_csv():
    # Excel file full path
    excel_file = os.path.join(os.path.sep, pathlib.Path(__file__).parent.resolve(), "Excel_Spreadsheet.xlsx")    
    # Excel sheets
    excel_sheets = ['Sheet1', 'Sheet2', 'Sheet3']

    for sheet in excel_sheets:
        # Create dataframe for each sheet
        df = pd.DataFrame(pd.read_excel(excel_file, sheet, index_col=None, engine='openpyxl'))
        # Export to csv. i.e: sheet_name.csv
        df.to_csv(os.path.join(os.path.sep, pathlib.Path(__file__).parent.resolve(), sheet + '.csv'), sep=",", encoding='utf-8', index=False, header=True)

# Runs the excel_to_csv function:
Excel_to_csv()