topic Error truncating #REF with spark.read in Data Engineering https://community.databricks.com/t5/data-engineering/error-truncating-ref-with-spark-read/m-p/74717#M34784 <P>Hello guys</P><P>I am trying to read an excel file and even using PERMISSIVE mode, its truncating the records that contains #REF in any column&nbsp;<span class="lia-unicode-emoji" title=":sad_but_relieved_face:">😥</span></P><P>Can anyone please help me on that?</P><P>&nbsp;</P><DIV><DIV><SPAN>schema </SPAN><SPAN>=</SPAN> <SPAN>StructType</SPAN><SPAN>([\</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>StructField</SPAN><SPAN>(</SPAN><SPAN>'Col1'</SPAN><SPAN>, </SPAN><SPAN>DateType</SPAN><SPAN>(), </SPAN><SPAN>True</SPAN><SPAN>), \ &lt;----------THIS COLUMN HAS #REF</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>StructField</SPAN><SPAN>(</SPAN><SPAN>'Col2'</SPAN><SPAN>, </SPAN><SPAN>DateType</SPAN><SPAN>(), </SPAN><SPAN>True</SPAN><SPAN>), \</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>StructField</SPAN><SPAN>(</SPAN><SPAN>'Col3'</SPAN><SPAN>, </SPAN><SPAN>StringType</SPAN><SPAN>(), </SPAN><SPAN>True</SPAN><SPAN>)])</SPAN></DIV><BR /><DIV><SPAN>test </SPAN><SPAN>=</SPAN><SPAN> (</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; spark.read.</SPAN><SPAN>format</SPAN><SPAN>(</SPAN><SPAN>"com.crealytics.spark.excel"</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"header"</SPAN><SPAN>, header_option)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"parseMode"</SPAN><SPAN>, </SPAN><SPAN>"PERMISSIVE"</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"keepUndefinedRows"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"useNullForErrorCells"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"treatEmptyValuesAsNulls"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"setErrorCellsToFallbackValues"</SPAN><SPAN>, </SPAN><SPAN>"true"</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"maxRowsInMemory"</SPAN><SPAN>, </SPAN><SPAN>1000</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"useNullForErrorCells"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"dataAddress"</SPAN><SPAN>, "Test!A1:C200")</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>schema(</SPAN><SPAN>schema</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>load</SPAN><SPAN>("File.xlsx")</SPAN></DIV></DIV><P>&nbsp;</P> Mon, 17 Jun 2024 19:58:47 GMT arthurandraderj 2024-06-17T19:58:47Z Error truncating #REF with spark.read https://community.databricks.com/t5/data-engineering/error-truncating-ref-with-spark-read/m-p/74717#M34784 <P>Hello guys</P><P>I am trying to read an excel file and even using PERMISSIVE mode, its truncating the records that contains #REF in any column&nbsp;<span class="lia-unicode-emoji" title=":sad_but_relieved_face:">😥</span></P><P>Can anyone please help me on that?</P><P>&nbsp;</P><DIV><DIV><SPAN>schema </SPAN><SPAN>=</SPAN> <SPAN>StructType</SPAN><SPAN>([\</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>StructField</SPAN><SPAN>(</SPAN><SPAN>'Col1'</SPAN><SPAN>, </SPAN><SPAN>DateType</SPAN><SPAN>(), </SPAN><SPAN>True</SPAN><SPAN>), \ &lt;----------THIS COLUMN HAS #REF</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>StructField</SPAN><SPAN>(</SPAN><SPAN>'Col2'</SPAN><SPAN>, </SPAN><SPAN>DateType</SPAN><SPAN>(), </SPAN><SPAN>True</SPAN><SPAN>), \</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; </SPAN><SPAN>StructField</SPAN><SPAN>(</SPAN><SPAN>'Col3'</SPAN><SPAN>, </SPAN><SPAN>StringType</SPAN><SPAN>(), </SPAN><SPAN>True</SPAN><SPAN>)])</SPAN></DIV><BR /><DIV><SPAN>test </SPAN><SPAN>=</SPAN><SPAN> (</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; spark.read.</SPAN><SPAN>format</SPAN><SPAN>(</SPAN><SPAN>"com.crealytics.spark.excel"</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"header"</SPAN><SPAN>, header_option)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"parseMode"</SPAN><SPAN>, </SPAN><SPAN>"PERMISSIVE"</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"keepUndefinedRows"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"useNullForErrorCells"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"treatEmptyValuesAsNulls"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>) </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"setErrorCellsToFallbackValues"</SPAN><SPAN>, </SPAN><SPAN>"true"</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"maxRowsInMemory"</SPAN><SPAN>, </SPAN><SPAN>1000</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"useNullForErrorCells"</SPAN><SPAN>, </SPAN><SPAN>True</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>option</SPAN><SPAN>(</SPAN><SPAN>"dataAddress"</SPAN><SPAN>, "Test!A1:C200")</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>schema(</SPAN><SPAN>schema</SPAN><SPAN>)</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .</SPAN><SPAN>load</SPAN><SPAN>("File.xlsx")</SPAN></DIV></DIV><P>&nbsp;</P> Mon, 17 Jun 2024 19:58:47 GMT https://community.databricks.com/t5/data-engineering/error-truncating-ref-with-spark-read/m-p/74717#M34784 arthurandraderj 2024-06-17T19:58:47Z