convert a .xlsb file to .csv file

hussain sagar 0
Viewed 28

I have an excel file from which I need to pick up a specific sheet and then convert that sheet to .csv format. The data looks like like this:

enter image description here

This sheet has other tables below Overall but I am not interested in those. I want to simply pick up the Overall table and store the values into a .csv file. I cannot change the input format or anything of that sort.

Until now, I have figured out how to pick a specific sheet from an excel file. But I am struggling with picking up only a single table from that sheet. This is what I have for now:

import pandas as pd
from pyxlsb import open_workbook as open_xlsb



#first handle xls case 
xls = pd.ExcelFile('sample.xlsx') #workbook has 2 sheets: sheet 1 and sheet 2
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
#print(df1.head())
#print(df2.head())

Example data https://drive.google.com/file/d/18uEai4NYHZVdxY4BtrB8oGqDbSIbZ6kX/view?usp=sharing

| overall | a   | b   | c  | d   |
|---------|-----|-----|----|-----|
| row 1   | 1   | 2   | 6  | 2   |
| row 2   | 2   | 5   | 2  | 4   |
| row 3   | 5   | 7   | 3  | 6   |
| row 4   | 7   | 8   | 3  | 7   |
|         |     |     |    |     |

| table 2 | l   | m   | n  | o   |
| row 1   | 1   | 35  | 5  | 887 |
| row 2   | 232 | 3   | 35 | 8   |
| row 3   | 2   | 5   | 5  | 6   |
| row 4   | 14  | 546 | 78 | 2   |
pandas excel python
1 Answers

You can use pandas.read_excel and pass engine = 'pyxlsb' as a parameter to read a fixed specific range (rows/columns) from a .xlsb file.

Note : There is no structured references (tables) in your .xlsb. There is only range of values.

import pandas as pd

#pip install pyxlsb
df = pd.read_excel('sample.xlsb', sheet_name='Summary_Detailed', skiprows=range(0,3), usecols='B:G', engine='pyxlsb')

df = df.iloc[0:41,:].dropna(subset='Overall') #to select the rows

df.to_csv('sample.csv', index=False) #to generate a .csv file

>>> display(df)

enter image description here

Let me know if you need to load more columns than B to G.

abokey0
Related