How to read date in custom format from csv file?

Question

I am parsing a csv file having data as

03-10-2016,18:00:00,2,6

When I am reading file creating schema as below

StructType schema = DataTypes.createStructType(Arrays.asList(
                DataTypes.createStructField("Date", DataTypes.DateType, false),
                DataTypes.createStructField("Time", DataTypes.TimestampType, false),
                DataTypes.createStructField("CO(GT)", DataTypes.IntegerType, false),
                DataTypes.createStructField("PT08.S1(CO)", DataTypes.IntegerType, false)))
Dataset<Row> df = spark.read().format("csv").option("Date", "dd-MM-yyyy").schema(schema).load("src/main/resources/AirQualityUCI/sample.csv");

Its producing below error as

Exception in task 0.0 in stage 0.0 (TID 0)
java.lang.IllegalArgumentException
    at java.sql.Date.valueOf(Unknown Source)
    at org.apache.spark.sql.catalyst.util.DateTimeUtils$.stringToTime(DateTimeUtils.scala:137)

I feel that it is due to date format error. What are the ways of converting them into specific formats?

Jacek Laskowski · Accepted Answer · 2018-01-12T11:46:32.567

7

Use dateFormat option when reading the CSV file(s) as follows:

val csvs = spark.
  read.
  format("csv").
  option("dateFormat", "dd-MM-yyyy"). // <-- should match 03-10-2016
  load(...)

The default for dateFormat is yyyy-MM-dd so it's no surprise you've got the parsing error.

Quoting from the javadoc of valueOf:

Throws IllegalArgumentException - if the date given is not in the JDBC date escape format (yyyy-[m]m-[d]d)

That means that the value is incorrect for the parser of valueOf.

I'd have two recommendations here:

Read the dataset and show it to see what you have inside.
Use dateFormat option to define the proper format (it's yyyy-MM-dd by default)

Find more about the format patterns in Date and Time Patterns (of java.text.SimpleDateFormat).

edited Jan 12 '18 at 11:46

answered Sep 19 '17 at 11:40

Jacek Laskowski

72,696
27
242
420

Can I use map function here which while parsing the csv data it will use `dataFormat` on string and convert it into required format and then create data set out of it. Just curious. – Utkarsh Saraf Sep 19 '17 at 12:00
1

You can, but don't do things Spark could do for you (unless you're sure you can do them better). – Jacek Laskowski Sep 19 '17 at 12:03
1

Sure.I am big fan of yours and have read your wonderful notes named "Mastering Spark".They are very much helpful in learning it :) – Utkarsh Saraf Sep 19 '17 at 12:05
Is there any elaborated details or link available on using `option`? – Utkarsh Saraf Sep 19 '17 at 12:21
Working on one. Haven't found anything worth mentioning yet. – Jacek Laskowski Sep 19 '17 at 12:28

How to read date in custom format from csv file?

1 Answers1

Linked