How to export Excel file to csv in python

We use the pandas library and the following three lines:

 import pandas as pd
 df = pd.read_excel("Libro2.xlsx",sheet="questionlist")
 df.to_csv('l2.csv',index=False,header=False)

Sometimes we need to do some formatting before we export the excel to csv file.

Convert a column to string type

df.Answers = df.Answers.astype(str)

Check the original in stackoverflow.

Remove new lines

df.Answers = df.Answers.str.replace(r"/\n\r|\n|\r/g",'')

Check the syntax of replace here.

In case you want to modify a column, you can do the following to replace some characters:

df.Answers = df.Answers.str.replace("\"","")

Remove all spaces:

df.Question.apply(lambda x: " ".join(x.split()))

Remove trailing spaces

df.Answers = df.Answers.str.strip()

Example program in python

import pandas as pd
import fire

def to_csv(fileName,sheet,outfile):
 df = pd.read_excel(fileName,sheet=sheet)
 for c in df.columns:
  df[c]= df[c].astype(str)
  df[c] = df[c].str.replace(r"/\n\r|\n|\r/g",'')
  df[c] = df[c].str.strip()
 df.to_csv(outfile,index=False,header=False)
 

if __name__ == '__main__':
  fire.Fire(to_csv)

Documentation from pandas