I have read many posts about this question, but, although there are many threads on this topic, I can't find the solution, even using the one in the documentation.
The question is pretty straightforward. I have an Excel I need to read, and some columns have a leading zero, as you can see in the image:
From the pandas documentation and many posts, pandas should be able to read the leading zeros if I use dtype='string' or dtype='object'.
I have tried everything, but the result is always the same:
df = pd.read_excel(file_location, sheet_name=0, dtype='object', index_col=None)
0 3664824000805
1 4260402311159
2 841333118549
3 5010993721788
4 5010993540570
Name: EAN, dtype: object
df = pd.read_excel(file_location, sheet_name=0, dtype='string')
0 3664824000805
1 4260402311159
2 841333118549
3 5010993721788
4 5010993540570
Name: EAN, dtype: string
df = pd.read_excel(file_location, sheet_name=0, dtype=str)
0 3664824000805
1 4260402311159
2 841333118549
3 5010993721788
4 5010993540570
Name: EAN, dtype: object
df = pd.read_excel(file_location, sheet_name=0, converters={"EAN": str})
0 3664824000805
1 4260402311159
2 841333118549
3 5010993721788
4 5010993540570
Name: EAN, dtype: object
df = pd.read_excel(file_location, sheet_name=0, dtype={"EAN": 'string'})
0 3664824000805
1 4260402311159
2 841333118549
3 5010993721788
4 5010993540570
Name: EAN, dtype: string
And so on…
If I convert the XLSX to CSV using cloudconvert, then the zeros are kept. As well, when I use the app “numbers” from the Mac, or Google Sheets.
But I need to keep the leading zeros in any column using python.
Maybe someone had the same problem. I don't have to use pandas, btw, but I tried directly with openpyxl and the result is the same.