Read column which contains float like numbers using infer schema

[minnieshi]

Using infer schema, somehow the column is inferred as string, is this because it only take sample number of records?
Is there anything that we can get code work correctly by maybe formatting the cell/column? In other words would the input excel column format "general" vs "number" make a difference?

Also It is difficult to test on local machine vs spark cluster as I am not sure I can control the locale of the spark running machines (worker and driver) - it seems like sometime it is EU locale and sometime it is US locale. Can i somehow fix the spark clusters locale?

[nightscape]

Hi @minnieshi, it should indeed make a difference if you format all cells of a column as number.
Regarding the locale, I don't have any clue. If you find something out, it would be nice if you could post it here.

[minnieshi]

I will try the format thing and report back; right now it is all general I think.

[minnieshi]

I changed column to be number, it is still read as string column;
And casting to float, make the values to be null if the locale (tested in IDE local machine by setting region) is EU countries and there are decimal points

[minnieshi]

The column itself does have many empty/null values, maybe that is why it is inferred as string? @nightscape

[minnieshi]

I see csv spark data frame reader can set locale,
Do we have it for the excel? Or can the client control it?

https://spark.apache.org/docs/3.0.1/api/java/org/apache/spark/sql/DataFrameReader.html

Reason for this Q, the moment it is inferred as String Type, we have to ensure it does not contain comma, and forcing locale to be non-EU will help.

[nightscape]

There seems to be a method for setting the locale in Apache POI.
Not sure if this would help though.
You could try opening a spark-shell and calling this method before trying to parse the data.

Regarding your question about empty / null values: Yes, this could be related. spark-excel reads the first excerptSize rows to determine the schema. If these rows do not have any numbers in them, it will default to String.