Databricks pyspark - Find columns in xls file.

[weldermartins]

Hello everyone, every day I extract data into xls files but the column position changes every day. Is there any way to find these columns within the file?

Here's a snippet of my code.

df = spark.read.format("com.crealytics.spark.excel")\
  .option("header", "true")\
  .schema(schema)\
  .option("dataAddress", "'releases'!A27:D78") \
  .load("dbfs:/FileStore/tables/invoice_september.xls")
df.display()

[Debayan]

Hi, Thanks for reaching out to community.databricks.com.

Please refer and let us know if this helps, you can find column names: https://sparkbyexamples.com/pyspark/pyspark-find-datatype-column-names-of-dataframe/

[jose_gonzalez · Accepted solution]

You can also do df.printSchema() to check. Or even dbutils.fs.head(<file_path>) to check the header's position. Docs https://docs.databricks.com/dev-tools/databricks-utils.html

[Vidula]

Hi @welder martins​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!

[weldermartins]

Hello, come shape. Thanks!