Function only reading filtered rows. How do I read all rows from a sheet?

[abhi250372]

Here is my code -
filepath = os.path.join(FolderPath, FileName)
a = load_workbook(filepath, read_only=True)

def read_excel_file(path: str, sheet_index: int) -> pd.DataFrame:
buffer = StringIO()
Xlsx2csv(path, outputencoding="utf8").convert(buffer,sheetid=sheet_index)
buffer.seek(0)
df = pd.read_csv(buffer)
return df

dfinal=read_excel_file(filepath,a.index(a.get_sheet_by_name('Sheetname'))+1)

[abhi250372]

I am using XLSX2CSV because it is faster in reading excel files as compared to read_excel() from openpyxl. But using XLSX2CSV only reads filtered rows of a sheet whereas read_excel reads all rows regardless if the sheet is filtered or not.

[tongngo]

Hello, I have just experimented it also. Is there a way/option that XLSX2CSV would read all rows of a sheet ?

[serene-dev]

what is a filtered row? can you share a sample xlsx file?

[tongngo]

In the attached file, ColumnA is filtering rows only showing 'A' value in ColumnA (not showing the rows beginning with 'B' value)
The xlsx2csv would only convert the visible rows, not all rows as Excel would do.
Samplefile.xlsx

[abhi250372]

Here is another example. The openpyxl function returns all rows regardless if the sheet is pre filtered or not but the XLSX2CSV only returns the pre filtered rows. Abhishek

…

On Wed, 21 Dec 2022 at 12:08, TongsasTong ***@***.***> wrote: In the attached file, ColumnA is filtering rows only showing 'A' value in ColumnA (not showing the rows beginning with 'B' value) The xlsx2csv would only convert the visible rows, not all rows as Excel would do. Samplefile.xlsx <https://github.com/dilshod/xlsx2csv/files/10280128/Samplefile.xlsx> — Reply to this email directly, view it on GitHub <#246 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A4YGWD75OHFZGECCBFZEN7TWONBR7ANCNFSM6AAAAAAS7F2HWU> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[abhi250372]

Here is sample file Car.xlsx

[abhi250372]

Looks like dilshod has fixed the issue. I downloaded the xlsx2csvc.py file in the repository and replaced the old file in my local machine with this new one. I added the skip_hidden_rows as False in my function mentioned above and now it works. However, I need to know when this version will be available via pip install command so that all the users can get the updated version instead of manually downloading this file. Can someone please help with that? def read_excel_file(path: str, sheet_index: int) -> pd.DataFrame:
buffer = StringIO()
Xlsx2csv(path, outputencoding="utf-8",skip_hidden_rows = False).convert(buffer,sheetid = sheet_index)
buffer.seek(0)
df = pd.read_csv(buffer)
return df

[tongngo]

Looks like dilshod has fixed the issue. I downloaded the xlsx2csvc.py file in the repository and replaced the old file in my local machine with this new one. I added the skip_hidden_rows as False in my function mentioned above and now it works. However, I need to know when this version will be available via pip install command so that all the users can get the updated version instead of manually downloading this file. Can someone please help with that? def read_excel_file(path: str, sheet_index: int) -> pd.DataFrame: buffer = StringIO() Xlsx2csv(path, outputencoding="utf-8",skip_hidden_rows = False).convert(buffer,sheetid = sheet_index) buffer.seek(0) df = pd.read_csv(buffer) return df

Thank you again @dilshod !!

[abhi250372]

@dilshod When will the latest version be released so that I can do pip install?

[abhi250372]

@dilshod is there an update on this?

[hendrikschafer]

It is available via pip now, this issue can be closed!

[mcrumiller]

I think skip_hidden_rows=False should be the default. Nobody expects filtered rows to be removed when reading an Excel.

[tongngo]

I think skip_hidden_rows=False should be the default. Nobody expects filtered rows to be removed when reading an Excel.

Yes, also, this is the default beahvior of excel when saviong to csv file