Argthtjtr

Facing an issue while reading the test2.csv file in pyspark.

Test file test1.csv

a1^b1^c1^d1^e1

a2^"this is having 

multiline data1

multiline data2"^c2^d2^e2

a3^b3^c3^d3^e3

a4^b4^c4^d4^e4

Test file test2.csv

a1^b1^c1^d1^e1

a2^this is having 

multiline data1

multiline data2^c2^d2^e2

a3^b3^c3^d3^e3

a4^b4^c4^d4^e4

Below is the Code

schema = StructType([

    StructField("A", StringType()),

    StructField("B", StringType()),

    StructField("C", StringType()),

    StructField("D", StringType()),    

    StructField("E", StringType())

])

Creating the dataframe for the above 2 csv files.

df1=spark.read.csv("s3_path/test1.csv",schema=schema,inferSchema=True,multiLine=True,sep='^')
df1.show(10,False)
print ('df1.count() is: ', df1.count())


Below is the output when I read the test1.csv file

+---+-----------------------------------------------+---+---+---+

|A  |B                                              |C  |D  |E  |

+---+-----------------------------------------------+---+---+---+

|a1 |b1                                             |c1 |d1 |e1 |

|a2 |this is having 

multiline data1

multiline data2|c2 |d2 |e2 |

|a3 |b3                                             |c3 |d3 |e3 |

|a4 |b4                                             |c4 |d4 |e4 |

+---+-----------------------------------------------+---+---+---+



df1.count() is: 4







df2 = spark.read.csv("s3_path/test2.csv",schema=schema,inferSchema=True,multiLine=True,sep='^')

df2.show(10,False)

print ('df2.count() is: ', df2.count())





Below is the output when I read the test2.csv file

+---------------+---------------+----+----+----+

|A              |B              |C   |D   |E   |

+---------------+---------------+----+----+----+

|a1             |b1             |c1  |d1  |e1  |

|a2             |this is having |null|null|null|

|multiline data1|null           |null|null|null|

|multiline data2|c2             |d2  |e2  |null|

|a3             |b3             |c3  |d3  |e3  |

|a4             |b4             |c4  |d4  |e4  |

+---------------+---------------+----+----+----+



df2.count() is: 6

Source Files:
If we see the difference in source files. test1.csv has " at the beginning and end of the multiline data. But test2.csv doesnt have that.

Issue Description: Column B, 2nd row has multiline data. If we see the output of the df2, it has 6 records, here spark is reading it as new record which is not correct.
The output of the df1 has 4 records, the multiline data in column B 2nd row is treated as one string which is correct.

Question : Can someone help to fix the code to read the test2.csv file as well correctly.