How to save CSV with all fields quoted?

Question

The below code does not add the double quotes which is the default. I also tried adding # and single quote using option quote with no success. I also used quoteMode with ALL and NON_NUMERIC options, still no change in the output.

s2d.coalesce(64).write
  .format("com.databricks.spark.csv")
  .option("header", "false")
  .save(fname)

Are there any other options I can try? I am using spark-csv 2.11 over spark 2.1.

Output it produces:

d4c354ef,2017-03-14 16:31:33,2017-03-14 16:31:46,104617772177,340618697

Output I am looking for:

“d4c354ef”,”2017-03-14 16:31:33”,”2017-03-14 16:31:46”,104617772177,340618697  

Papke

Answer 1

tl;dr Enable quoteAll option.

scala> Seq(("hello", 5)).toDF.write.option("quoteAll", true).csv("hello5.csv")

The above gives the following output:

$ cat hello5.csv/part-00000-a0ecb4c2-76a9-4e08-9c54-6a7922376fe6-c000.csv
"hello","5"

That assumes the quote is " (see CSVOptions)

That however won't give you "Double quotes around all non-numeric characters." Sorry.

You can see all the options in CSVOptions that serves as the source of the options for the CSV reader and writer.

p.s. com.databricks.spark.csv is currently a mere alias for csv format. You can use both interchangeably, but the shorter csv is preferred.

p.s. Use option("header", false) (false as boolean not String) that will make your code slightly more type-safe.

Prepossession

Answer 2

In Spark 2.1 where the old CSV library has been inlined, I do not see any option for what you want in the csv method of DataFrameWriter as seen here.

So I guess you have to map over your data "manually" to determine which of the Row components are non-numbers and quote them accordingly. You could utilize a straightforward isNumeric helper function like this:

def isNumeric(s: String) = s.nonEmpty && s.forall(Character.isDigit)

As you map over your DataSet, quote the values where isNumeric is false.

Potaufeu

Thanks. But I am not using the native spark library. I am using the Databricks spark-csv library. github.com/databricks/spark-csv – Papke

I know you are, but you should switch because it is deprecated as they note. Besides, they aren't quite two distinct things. The functionality has been absorbed into Spark, and your question proves why you should switch. Getting help on deprecated libraries will only get harder and harder. – Potaufeu

its sad only quoteAll is available in spark 2.1. I am facing a similar issue where i need quoteMode in spark 2 – Odell