dilshod
Function only reading filtered rows. How do I read all rows from a sheet?
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)
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.
Hello, I have just experimented it also. Is there a way/option that XLSX2CSV would read all rows of a sheet ?
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
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 https://github.com/dilshod/xlsx2csv/issues/246#issuecomment-1361789157, or unsubscribe https://github.com/notifications/unsubscribe-auth/A4YGWD75OHFZGECCBFZEN7TWONBR7ANCNFSM6AAAAAAS7F2HWU . You are receiving this because you authored the thread.Message ID: @.***>
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
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 !!
@dilshod When will the latest version be released so that I can do pip install?
I think skip_hidden_rows=False should be the default. Nobody expects filtered rows to be removed when reading an Excel.
I think
skip_hidden_rows=Falseshould 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