shows only _corrupt_record column while reading from xml file in databricks

user_16529551 0
Viewed 29

I am reading a .xml file from s3 using databricks notebook, i have installed necessary packages. But after reading i only get one column which is "_corrupt_record". Following is my XML file

<?xml version="1.0" encoding="UTF-8"?>
<Records>
  <Record>
    <App>Y! Mobile menu</App>
    <Category>BUSINESS</Category>
    <Rating>NaN</Rating>
    <Reviews>9</Reviews>
    <Size>1.2M</Size>
    <Installs>1,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>April 9, 2018</Last Updated>
    <Current Ver>1.0.5</Current Ver>
    <Android Ver>6.0 and up</Android Ver>
  </Record>
  <Record>
    <App>Slack</App>
    <Category>BUSINESS</Category>
    <Rating>4.4</Rating>
    <Reviews>51507</Reviews>
    <Size>Varies with device</Size>
    <Installs>50,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>August 2, 2018</Last Updated>
    <Current Ver>Varies with device</Current Ver>
    <Android Ver>Varies with device</Android Ver>
  </Record>
  <Record>
    <App>FreshBooks Classic</App>
    <Category>BUSINESS</Category>
    <Rating>4.1</Rating>
    <Reviews>1802</Reviews>
    <Size>26M</Size>
    <Installs>1,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>April 18, 2018</Last Updated>
    <Current Ver>1.7.14</Current Ver>
    <Android Ver>4.2 and up</Android Ver>
  </Record>
  <Record>
    <App>Insightly CRM</App>
    <Category>BUSINESS</Category>
    <Rating>3.8</Rating>
    <Reviews>1383</Reviews>
    <Size>51M</Size>
    <Installs>1,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>July 12, 2018</Last Updated>
    <Current Ver>3.24.1</Current Ver>
    <Android Ver>5.0 and up</Android Ver>
  </Record>
  <Record>
    <App>QuickBooks Accounting: Invoicing &amp; Expenses</App>
    <Category>BUSINESS</Category>
    <Rating>4.3</Rating>
    <Reviews>23175</Reviews>
    <Size>41M</Size>
    <Installs>10,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>July 13, 2018</Last Updated>
    <Current Ver>18.7</Current Ver>
    <Android Ver>4.1 and up</Android Ver>
  </Record>
  <Record>
    <App>HipChat - Chat Built for Teams</App>
    <Category>BUSINESS</Category>
    <Rating>3.8</Rating>
    <Reviews>5868</Reviews>
    <Size>20M</Size>
    <Installs>5,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>July 3, 2018</Last Updated>
    <Current Ver>3.19.005</Current Ver>
    <Android Ver>4.1 and up</Android Ver>
  </Record>
  <Record>
    <App>Xero Accounting Software</App>
    <Category>BUSINESS</Category>
    <Rating>3.5</Rating>
    <Reviews>2111</Reviews>
    <Size>Varies with device</Size>
    <Installs>1,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>July 30, 2018</Last Updated>
    <Current Ver>Varies with device</Current Ver>
    <Android Ver>Varies with device</Android Ver>
  </Record>
  <Record>
    <App>MailChimp - Email, Marketing Automation</App>
    <Category>BUSINESS</Category>
    <Rating>4.1</Rating>
    <Reviews>5448</Reviews>
    <Size>12M</Size>
    <Installs>5,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Business</Genres>
    <Last Updated>July 25, 2018</Last Updated>
    <Current Ver>4.9.1</Current Ver>
    <Android Ver>5.0 and up</Android Ver>
  </Record>
  <Record>
    <App>Children&apos;s cartoons (Mithu-Mina-Raju)</App>
    <Category>COMICS</Category>
    <Rating>4.6</Rating>
    <Reviews>279</Reviews>
    <Size>3.3M</Size>
    <Installs>1,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>December 18, 2017</Last Updated>
    <Current Ver>1.6</Current Ver>
    <Android Ver>4.0 and up</Android Ver>
  </Record>
  <Record>
    <App>Narrator&apos;s Voice</App>
    <Category>COMICS</Category>
    <Rating>4.5</Rating>
    <Reviews>564387</Reviews>
    <Size>Varies with device</Size>
    <Installs>50,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>July 5, 2018</Last Updated>
    <Current Ver>Varies with device</Current Ver>
    <Android Ver>Varies with device</Android Ver>
  </Record>
  <Record>
    <App>【Ranobbe complete free】 Novelba - Free app that you can read and write novels</App>
    <Category>COMICS</Category>
    <Rating>NaN</Rating>
    <Reviews>1330</Reviews>
    <Size>22M</Size>
    <Installs>50,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>July 3, 2018</Last Updated>
    <Current Ver>6.1.1</Current Ver>
    <Android Ver>4.2 and up</Android Ver>
  </Record>
  <Record>
    <App>Faustop Sounds</App>
    <Category>COMICS</Category>
    <Rating>4.7</Rating>
    <Reviews>1677</Reviews>
    <Size>40M</Size>
    <Installs>1,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>May 26, 2018</Last Updated>
    <Current Ver>1.7.7</Current Ver>
    <Android Ver>4.1 and up</Android Ver>
  </Record>
  <Record>
    <App>Manga Mania - Best online manga reader</App>
    <Category>COMICS</Category>
    <Rating>4.4</Rating>
    <Reviews>757</Reviews>
    <Size>10M</Size>
    <Installs>10,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Teen</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>May 24, 2018</Last Updated>
    <Current Ver>1.0</Current Ver>
    <Android Ver>4.1 and up</Android Ver>
  </Record>
  <Record>
    <App>- Free Comics - Comic Apps</App>
    <Category>COMICS</Category>
    <Rating>3.5</Rating>
    <Reviews>115</Reviews>
    <Size>9.1M</Size>
    <Installs>10,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Mature 17+</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>July 13, 2018</Last Updated>
    <Current Ver>5.0.12</Current Ver>
    <Android Ver>5.0 and up</Android Ver>
  </Record>
  <Record>
    <App>Manga AZ - Manga Comic Reader</App>
    <Category>COMICS</Category>
    <Rating>3.3</Rating>
    <Reviews>125</Reviews>
    <Size>4.7M</Size>
    <Installs>5,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Teen</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>July 24, 2018</Last Updated>
    <Current Ver>1.0.4</Current Ver>
    <Android Ver>4.0.3 and up</Android Ver>
  </Record>
  <Record>
    <App>Buff Thun - Daily Free Webtoon / Comics / Web Fiction / Mini Game</App>
    <Category>COMICS</Category>
    <Rating>4.5</Rating>
    <Reviews>9952</Reviews>
    <Size>38M</Size>
    <Installs>5,00,000</Installs>
    <Type>Free</Type>
    <Price>0</Price>
    <Content Rating>Everyone 10+</Content Rating>
    <Genres>Comics</Genres>
    <Last Updated>July 31, 2018</Last Updated>
    <Current Ver>4.3.0</Current Ver>
    <Android Ver>4.1 and up</Android Ver>
  </Record>
</Records>

I am reading the file using

df = spark.read.format("com.databricks.spark.xml")
.option("rootTag","Records")
.option("rowTag","Record")
.load(f"s3://{fileName}")

the result of df.show() is

+--------------------+
|     _corrupt_record|
+--------------------+
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
|<Record>\r\n    <...|
+--------------------+
only showing top 20 rows

You can also check this image result of display(df)

Cannot understand what i am doing wrong. Help is appreciated.

pyspark databricks xml
1 Answers

It looks like this library does not support column/tag names with space as you can see below first I tried a record with just last updated as tag having space in it 1st record unchanged and second only has last updated as space containing column

and when i change last updated -> last_updated the record is being read fine

all columns without space

so just check if can change the tag names to not have space somehow before reading

user_190757710
Related