How to convert xls to csv ? I can see first header column got shifted to next column- Solved

The requirement is to convert and xls to csv using python. Initially we used pandas ,  pandas.read_excel to read an xls file. But we can see error as could not read that xls file. When we open the file directly we are getting a message as “The file format and extension of” document “don’t match” , the file could be corrupted or unsafe. Do you want to open“. So have to try an alternative method, we tried to read as csv with tab separated . Got that worked.

import pandas as pd
file_to_process = '/mnt/my_file/Freshers_in/my_xls_2021-06-11.xls'
excel_file = pd.read_csv(file_to_process, sep='\t',index_col=False)
df_total.to_csv('my_csv.csv',index=False)

index_col=False can be used to force pandas to not use the first column as the index, e.g. when you have a malformed file with delimiters at the end of each line.The default value is None, and pandas will add a new column start from 0 to specify the index column.

Reference doc

Get more Python article, Source Code, Tricks, Interview questions etc here

Author: user

Leave a Reply

Your email address will not be published. Required fields are marked *