Add decimal option to read_excel

[bbirand]

The pd.read_csv() function has a decimal option, which allows it to parse European dates very easily:

pd.read_csv("afile.csv", decimal=',')

This option is not present in pd.read_excel(), but it could also be very useful there.

It would be even more useful if this option was a list of characters to interpret as decimal delimiters, instead of a single character.

[jorisvandenbossche]

As far as I know excel, this shouldn't matter? As excel stores values, and how it is represented in what you see in the excel file depends on your language settings of excel.
When reading the file in pandas, it should give you numerical values, regardless whether you see . or , for the decimal.

[bbirand]

@jorisvandenbossche I think this might be the case if the cells are declares as "Number" but in our case, our database queries are returned with the cell type "General", and this becomes an issue then.

[jorisvandenbossche]

How do they get read in? As strings?

[bbirand]

Yes, they have the dtype "object", and they appear as strings.

[WillAyd]

I don't think this is a feature we should be supporting, as pandas is being very literal in this case and interpreting the data as it is stored in Excel. The correct approach for what is desired here would be to store the values as a number, or if that is not possible handle from an end user perspective the desired conversions

Due to age closing this request though ping if it's still something you are looking for and can discuss further

[sven337]

This is something I am certainly interested in, as manually fixing up the data after parsing is annoying, and it is quite common to have that problem whenever you use a mix of US and European Excel files.

[WillAyd]

Thanks for the feedback @sven337 . Reopening for discussion since there is some interest, though I'd still vote against support something like this. Let's see what others think

[sven337]

I'm no pandas developer, but I don't really understand why the CSV reader would have the ability to interpret strings as decimal with a configurable decimal separator, while the Excel reader would have to only use a hardcoded "." decimal separator when reading strings.
The fact is that many Excel sheets have numbers of cell type "standard", and that works just fine on most people's computers (until you load them up with a different locale setting, which I do all of the time). Conceptually if "standard" is recorded as a string, but Pandas has the ability to interpret these as numbers, then this is the same thing as reading a CSV: you need to be able to specify the decimal separator, because all data doesn't use dot.

[WillAyd]

I'm no pandas developer, but I don't really understand why the CSV reader would have the ability to interpret strings as decimal with a configurable decimal separator, while the Excel reader would have to only use a hardcoded "." decimal separator when reading strings.

Because a CSV format doesn't offer cell metadata (specifically types) nor locale insights that are available to you with an Excel file, so you have to guess.

[MortenHannemose]

The fact is that many Excel sheets have numbers of cell type "standard", and that works just fine on most people's computers

Exactly this! Many documents are just stored as "standard" cell type, which causes no problems when the locale is similar. I have experienced several programs/websites that export the excel file in this way.