CODEWITHSUNDEEP
csvdataframeapache-sparkpysparketl
br1
br1

Reputation: 377

pyspark read text file with multiline column

I have the following bad formatted txt file:

id;text;contact_id
1;Reason contact\
\
The client was not satisfied about the quality of the product\
\
;c_102932131

I'm trying to load the file using pyspark by using:

df = sc.read\
.option("delimiter", ";")\
.option("header", "true")\
.option("inferSchema", "true")\
.option("multiLine", "true")\
.option("wholeFile", "true")\
.csv(os.path.join(appconfig.configs[appconfig.ENV]["ROOT_DIR"], "data", "input", file_name))

But the column text is truncated, since the dataframe is:

id|text|contact_id
1|Reason contact|null
null|null|c_102932131

So I lose all the other lines. The goal is to read correctly the file in this way:

id|text|contact_id
1|Reason contact The client was satisfied not about the quality of the product|c_102932131

How can I do this? Thank you

Upvotes: 1

Views: 2269

Answers (1)

notNull
notNull

Reputation: 31490

Use .wholeTextFiles and then replace new line (\n) and \ finally create df.

Example:

Spark-Scala:

sc.wholeTextFiles("<file_path>").
toDF().
selectExpr("""split(replace(regexp_replace(_2,"[\\\\|\n]",""),"id;text;contact_id",""),";") as new""").
withColumn("id",col("new")(0)).
withColumn("text",col("new")(1)).
withColumn("contact_id",col("new")(2)).
drop("new").
show(false)
//+---+---------------------------------------------------------------------------+-----------+
//|id |text                                                                       |contact_id |
//+---+---------------------------------------------------------------------------+-----------+
//|1  |Reason contactThe client was not satisfied about the quality of the product|c_102932131|
//+---+---------------------------------------------------------------------------+-----------+

Pyspark:

from pyspark.sql.functions import *

sc.wholeTextFiles("<file_path>").\
toDF().\
selectExpr("""split(replace(regexp_replace(_2,'[\\\\\\\\|\n]',''),"id;text;contact_id",""),";") as new""").\
withColumn("id",col("new")[0]).\
withColumn("text",col("new")[1]).\
withColumn("contact_id",col("new")[2]).\
drop("new").\
show(10,False)
#+---+---------------------------------------------------------------------------+-----------+
#|id |text                                                                       |contact_id |
#+---+---------------------------------------------------------------------------+-----------+
#|1  |Reason contactThe client was not satisfied about the quality of the product|c_102932131|
#+---+---------------------------------------------------------------------------+-----------+

Upvotes: 2

Related Questions