cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Incorrect reading csv format with inferSchema

Tracy_
New Contributor II

Hi All,

There is a CSV with a column ID (format: 8-digits & "D" at the end).

When trying to read a csv with .option("inferSchema", "true"), it returns the ID as double and trim the "D". Is there any idea (apart from inferSchema=False) to get correct result? Thanks for help!

Below options was tried and also failed.

options(delimiter=",", sep = ",", header=True, inferSchema=True,multiline=True, quote="\"", escape="\"")

image.png

5 REPLIES 5

Ajay-Pandey
Esteemed Contributor III

Hi @tracy ng​ 

By default, spark treating as a double value for all the numbers ending with D or F

I think you should connect with databricks regarding this.

Ajay Kumar Pandey

Tracy_
New Contributor II

Thanks @Ajay Pandey​.

Hubert-Dudek
Esteemed Contributor III

It can be solved, for example, by specifying the schema with ID as string.

If you don't want to specify schema, better would be autoloader as you can specify hint:

spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "csv")
  .option("cloudFiles.schemaLocation", checkpoint_path)
  .option("cloudFiles.schemaHints", "ID String")
  .option("inferSchema", True)
  .option("mergeSchema", True)
  .load(folder)

Tracy_
New Contributor II

Thanks @Hubert Dudek​ ,

Becasue the reading file is used to loop different source from different directory with different schema and dynamic column name (sometimes named ID, sometime named SID etc.), it seems the autoloader is not applicable to the case. I wonder is there any option to disable this feature (treating column as double when there is trailing D).

Anonymous
Not applicable

Hi @tracy ng​ 

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance! 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group