How to read a pipe delimited text file in pyspark that contains escape character but no quotes?

user_9051697 0
Viewed 448

Below is an example of my data in raw format and in a table:

THis is a test|This is a \| test|"this is a \| test"

col1 col2 col3
THis is a test This is a | test "this is a | test"

I am using the following script:

df = spark.read\
  .option("header", "false")\
  .option("escape", "\"")\
  .option("delimiter", "|")\
  .csv(file_dir)

However, the output looks like this:

df.show()
+--------------+-----------+-----+-----------------+
|           _c0|        _c1|  _c2|              _c3|
+--------------+-----------+-----+-----------------+
|THis is a test|This is a \| test|this is a \| test|
+--------------+-----------+-----+-----------------+

Essentially, I am trying to escape the delimiter if it is proceeded by a backslash regardless of quotes

pyspark apache-spark hadoop python
1 Answers

If you really want to do this you can write a new data reader that can handle this format natively. Here's a good youtube video explaining the components you'd need. Basically you'd create a new data source that new how to read files in this format. A little overkill but hey you asked.

The alternative would be to treat the file as text and use some regex judo to wrestle the data into a format you liked. (Similar to this)

user_135351200
Related