[Python] Reading in an Excel file with pandas read Excel `invalid literal for int() with base...

I am reading in an Excel file with Python using pandas' function read_excel. However, I keep encountering the error invalid literal for int() with base 10: ' ' (full error, see below).

As I read the error, my understanding is that there is a single whitespace somewhere in a column where the other values of the column are integers. In order to fix this, I am trying to read in the Excel file without interpreting the cell values:

pd.read_excel(filename, sheet_name = sheet_name, header = 0, converters = {colname : str for colname in colnames})


I have also tried this using:

pd.read_excel(filename, sheet_name = sheet_name, header = 0, converters = {colname : object for colname in colnames})


and also replacing converters by dtype, but in all cases I keep getting the same error.. I just want to read in the Excel file such that I can look for data issues in Python.

I also find it strange that when I type

pd.read_excel(filename, sheet_name = sheet_name, usecols = "A")


where the column A doesn't contain integers, I still have the same error.

Full error:

---------------------------------------------------------------------------
ValueError
Traceback (most recent call last) ~\AppData\Local\Temp/ipykernel_11216/3112089226.py in <module>
----> 1 pd.read_excel(filename, sheet_name = "Forecasted Flights (6 months)", header = 0, converters = {colname : str for colname in colnames})

~\Anaconda3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
309 stacklevel=stacklevel,
310 )
--> 311 return func(*args, **kwargs)
312
313 return wrapper

~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in read_excel(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)
370
371 try:
--> 372 data = io.parse(
373 sheet_name=sheet_name,
374 header=header,

~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, converters, true_values, false_values, skiprows, nrows, na_values, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds) 1270 DataFrame from the passed in Excel file. 1271 """
-> 1272 return self._reader.parse( 1273 sheet_name=sheet_name, 1274 header=header,

~\Anaconda3\lib\site-packages\pandas\io\excel\_base.py in parse(self, sheet_name, header, names, index_col, usecols, squeeze, dtype, true_values, false_values, skiprows, nrows, na_values, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, **kwds)
537 sheet = self.get_sheet_by_index(asheetname)
538
--> 539 data = self.get_sheet_data(sheet, convert_float)
540 if hasattr(sheet, "close"):
541 # pyxlsb opens two TemporaryFiles

~\Anaconda3\lib\site-packages\pandas\io\excel\_openpyxl.py in get_sheet_data(self, sheet, convert_float)
570 data: list[list[Scalar]] = []
571 last_row_with_data = -1
--> 572 for row_number, row in enumerate(sheet.rows):
573 converted_row = [self._convert_cell(cell, convert_float) for cell in row]
574 while converted_row and converted_row[-1] == "":

~\Anaconda3\lib\site-packages\openpyxl\worksheet\_read_only.py in
_cells_by_row(self, min_col, min_row, max_col, max_row, values_only)
77 data_only=self.parent.data_only, epoch=self.parent.epoch,
78 date_formats=self.parent._date_formats)
---> 79 for idx, row in parser.parse():
80 if max_row is not None and idx > max_row:
81 break

~\Anaconda3\lib\site-packages\openpyxl\worksheet\_reader.py in parse(self)
153 element.clear()
154 elif tag_name == ROW_TAG:
--> 155 row = self.parse_row(element)
156 element.clear()
157 yield row

~\Anaconda3\lib\site-packages\openpyxl\worksheet\_reader.py in parse_row(self, row)
284 self.row_dimensions[str(self.row_counter)] = attrs
285
--> 286 cells = [self.parse_cell(el) for el in row]
287 return self.row_counter, cells
288

~\Anaconda3\lib\site-packages\openpyxl\worksheet\_reader.py in <listcomp>(.0)
284 self.row_dimensions[str(self.row_counter)] = attrs
285
--> 286 cells = [self.parse_cell(el) for el in row]
287 return self.row_counter, cells
288

~\Anaconda3\lib\site-packages\openpyxl\worksheet\_reader.py in parse_cell(self, element)
200 elif value is not None:
201 if data_type == 'n':
--> 202 value = _cast_number(value)
203 if style_id in self.date_formats:
204 data_type = 'd'

~\Anaconda3\lib\site-packages\openpyxl\worksheet\_reader.py in
_cast_number(value)
80 if "." in value or "E" in value or "e" in value:
81 return float(value)
---> 82 return int(value)
83
84

ValueError: invalid literal for int() with base 10: ' '

Continue reading...