cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

data.write.format('com.databricks.spark.csv') added additional quotation marks

WenLin
New Contributor II

0favorite

I am using the following code (pyspark) to export my data frame to csv:

data.write.format('com.databricks.spark.csv').options(delimiter="\t", codec="org.apache.hadoop.io.compress.GzipCodec").save('s3a://myBucket/myPath')

Note that I use

delimiter="\t"
, as I don't want to add additional quotation marks around each field. However, when I checked the output csv file, there are still some fields which are enclosed by quotation marks. e.g.

abcdABCDAAbbcd ....
1234_3456ABCD  ...
"-12345678AbCd"...

It seems that the quotation mark appears when the leading character of a field is "-". Why is this happening and is there a way to avoid this? Thanks!

3 REPLIES 3

WenLin
New Contributor II

I also tried to use quoteMode = "NONE" , but doesn't work either

SiddSingal
New Contributor II

Could you possible supply some Python code that creates a small DataFrame that demonstrates this behavior?

chaotic3quilibr
New Contributor III

The way to turn off the default escaping of the double quote character (") with the backslash character (\) - i.e. to avoid escaping for all characters entirely, you must add an .option() method call with just the right parameters after the .write() method call. The goal of the option() method call is to change how the csv() method "finds" instances of the "quote" character as it is emitting the content. To do this, you must change the default of what a "quote" actually means; i.e. change the character sought from being a double quote character (") to a Unicode "\u0000" character (essentially providing the Unicode NUL character assuming it won't ever occur within the document).

Here's Scala code achieving the effect. The second to last line (ending with magic is happening here) is the critical line and looks exactly the same in Python (as it does here in Scala).

val dataFrame =
  spark.sql("SELECT * FROM some_table_with_odd_characters_column")
val unitEmitCsv =
  dataframe
    .write
    .option("header", true)
    .option("quote", "\u0000") //magic is happening here
    .csv("/FileStore/temp.csv")

This was only one of several lessons I learned attempting to work with Apache Spark and emitting .csv files. For more information and context on this, please see the blog post I wrote titled "Example Apache Spark ETL Pipeline Integrating a SaaS".

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.