Comments (8)
jaypanchal
commented on June 13, 2024
The reason could be this
ExcelConverterSimpleSpreadSheetCellDAO
BigDecimal bd = (BigDecimal) this.decimalFormat.parse(currentCellValue, new ParsePosition(0));
as this sentence will parse any string that starts with number / integer.
from spark-hadoopoffice-ds.
jornfranke
commented on June 13, 2024
Hi,
No, it is not that. The converter takes as input a schema which can be autoinferred or you define it yourself. Can you please provide some source code?
How did you define the schema? Is it autoinferred ? Then some valued might not be date. Or did you define it yourself?
What columns does the file contain?
Thank you.
… On 4. Jun 2018, at 14:10, Jay Panchal ***@***.***> wrote:
The reason could be this
ExcelConverterSimpleSpreadSheetCellDAO
BigDecimal bd = this.decimalFormat.parse(currentCellValue, new ParsePosition(0));
as this sentence will parse any string tha starts with number / integer.
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub, or mute the thread.
from spark-hadoopoffice-ds.
jornfranke
commented on June 13, 2024
It could be as well that you define the wrong date locale - this does not look like a US date.
… On 4. Jun 2018, at 14:01, Jay Panchal ***@***.***> wrote:
When excel cell has cell value as a date for example 17-05-2018 19:10 it is converting that column into double type. So resultant value will be 17.0000 and spark consider it as a double datatype column
—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub, or mute the thread.
from spark-hadoopoffice-ds.
jaypanchal
commented on June 13, 2024
-
Yes, it is auto inferred.
-
Particular date column (CreatedDate named column) has all the same date formatted value i.e. 15-02-
2018 13:15 (dd-MM-yyyy HH:mm) (Available in sample file) -
And yeah, that is also true that, it is not US date, but, it in form of
dataFrameReader = dataFrameReader.load().sparkSession().read()
.format("org.zuinnote.spark.office.excel")
.option("read.spark.simpleMode","true")
.option("read.spark.useHeader", "true")
//.option("read.spark.useHeader.skipHeaderInEachSheet", "true")
.option("read.locale.bcp47", "US")
.option("read.lowfootprint", "true")
.option("read.sheets", "");
dataset = dataFrameReader.load(pathToFile);
Sample file 188.xlsx
from spark-hadoopoffice-ds.
jornfranke
commented on June 13, 2024
Well you define that it is US locale . Try the locale of the original file. Is it “DE”?
Check also read.spark.simpleMode.dateLocale
https://github.com/ZuInnoTe/spark-hadoopoffice-ds/blob/master/README.md
… On 4. Jun 2018, at 15:00, Jay Panchal ***@***.***> wrote:
Yes, it is auto inferred.
Particular date column (CreatedDate named column) has all the same date formatted value i.e. 15-02-
2018 13:15 (dd-MM-yyyy HH:mm) (Available in sample file)
And yeah, that is also true that, it is not US date, but, it in form of
dataFrameReader = dataFrameReader.load().sparkSession().read()
.format("org.zuinnote.spark.office.excel")
.option("read.spark.simpleMode","true")
.option("read.spark.useHeader", "true")
//.option("read.spark.useHeader.skipHeaderInEachSheet", "true")
.option("read.locale.bcp47", "US")
.option("read.lowfootprint", "true")
.option("read.sheets", "");
dataset = dataFrameReader.load(pathToFile);
Sample file 188.xlsx
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.
from spark-hadoopoffice-ds.
jaypanchal
commented on June 13, 2024
Okay, let me give a try.
But, by this can we conclude, that any random date or timestamp (excluding available dateLocale) won't work ?
from spark-hadoopoffice-ds.
jornfranke
commented on June 13, 2024
Not sure what you mean by random date, but the reasoning behind it was that Excel very often stores dates not as date but as string, eg for the case that it has not defined by the user as date in Excel (it happens very often). One could think about another option such as “use only Excel dates” , but this has the disadvantage that the string dates cannot be detected.
Another alternative would be that you define in Spark a custom schema that is StringType for the date column and do any mapping to a custom date format. Probably this is preferred for “random” dates.
Another problem with random dates is that some date formats have conflicting information, eg one has first day and then month and then other first month and then day. This is very difficult to detect and handle automatically. Hence we can use the Power of Spark to do any custom processing on them.
… On 5. Jun 2018, at 08:16, Jay Panchal ***@***.***> wrote:
Okay, let me give a try.
But, by this can we conclude, that any random date or timestamp (excluding available dateLocale) won't work ?
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or mute the thread.
from spark-hadoopoffice-ds.
jornfranke
commented on June 13, 2024
I have not heard from you again, but feel free to check out also 1.2.0
from spark-hadoopoffice-ds.
Related Issues (20)
- java.lang.NoSuchMethodError while using spark-hadoopoffice-ds_2.11 (v1.2.4) HOT 7
- Doesn't generate excel file as expected but no obvious error HOT 3
- Cell with Integer value has an = sign HOT 24
- Spark Datasource does not pick up options for writing correctly HOT 2
- Job abortion on databricks HOT 2
- IndexOutOfBoundsException: When reading xlsx file. HOT 8
- How do i get a schema that's all String with "read.spark.simpleMode" HOT 2
- Add support for partitioning by folders in Hive/Spark Style HOT 19
- Writing to XLSX only outputs String fields but XLS works fine HOT 11
- workbookDocument .getWorkbook().getWorkbookPr() can return null HOT 8
- Support for Scala 2.13 and drop support for Scala 2.11 HOT 1
- Support Spark 3.1 HOT 8
- lowFootprint: numeric column values result in nulls HOT 7
- Nullpointer Exception when using Spark with Kyroserializer HOT 2
- "hadoopoffice.write.header.write" is only working for default sheet HOT 6
- Spill over to next sheet if number of rows exceeding Excel limitations
- Skipped imported decimal values HOT 11
- Not all HadoopOffice configuration is applied correctly
- CVE-2021-44228: Mitigate Log4shell HOT 2
- inferSchema for Excelfiles with one row is not working correctly
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from spark-hadoopoffice-ds.