CODEWITHSUNDEEP
pythonpandascsvdataframexlsx
Debbie
Debbie

Reputation: 969

Column names of xlsx file are not retained in the converted csvs

I am fetching data from a multisheet xlsx file and storing data in separate csv files. The first rows of all the sheets in xslx are stored in the first csv, the 2nd rows of all the sheets are stored in the 2nd csv, and, so on. For that I wrote the following code which works:

xls = xlrd.open_workbook(r'Smallys ORDER.xlsx', on_demand=True)
df_list = []

names = xls.sheet_names()
#print(names)
#print('-'*80)
names.remove('EVENT')
#print(names)

for i in range(191):
    rows = []
    for name in names:
        count = 0
        prod = pd.read_excel('Smallys ORDER.xlsx', name, index_col=None)
        #print(prod)
        try:
            item = prod.iloc[i]
            print(item)
            rows.append(item)
            #items = item.concat(item)
            #print(items)
            #prod.to_csv(item + '.csv', encoding='utf-8', index=False) 
            #print('-'*80)

        except:
            print('Row finished !!!')


    writer = csv.writer(open('/home/hp/products/' + 'prod['+str(i)+'].csv', 'w'))  
    writer.writerows(rows)

This code does not retain the column names of the xlsx file (same for all the sheets) in the csvs.

Upvotes: 0

Views: 54

Answers (1)

Serge Ballesta
Serge Ballesta

Reputation: 148910

You have to explicitely write the column names when you use a csv.writer. It is enough to use the column names from the last sheet:

writer = csv.writer(open('/home/hp/products/' + 'prod['+str(i)+'].csv', 'w'))
writer.writerow(prod.columns.tolist())
writer.writerows(rows)

Upvotes: 2

Related Questions