Process Excel files in Azure with Data Factory and Databricks | Tutorial

As a force I habit, I keep saying Crealytics library, but in fact, this library is called Spark-Excel and was developed by Crealytics company. 😊

how did you create the demo container to load the files? was this in a previous video.

Hey Adam, I have a template for an excel spreadsheet I would like to use to generate multiple reports from. But I would like to populate the spreadsheet template using data from my database, using several different queries. Could you provide some insight on ho to do this?

Can we use this same functionality in Azure Synapse notebook?

com.crealytics:spark-excel_2.12:3.3.1_0.18.5 this library worked for me with scala 2.12.15

Awesome vídeo, thanks for sharing

Hi Adam, could you please make a video on ADF batch service? Your videos have been very helpful in understanding ADF. Thanks

to master databricks is my goal, but damn seems to be a long way there...

Nice and ble to learn the concepts!!Thanks Adam

I am not able to read .xls file getting below error. Please let me know if any possible solution to read .xls file without changing it to .xlsx
ErrorCode=ExcelUnsupportedFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at NPOI.HSSF.Record.RecordInputStream.get_HasNextRecord() at NPOI.HSSF.Record.RecordFactoryInputStream.NextRecord() at NPOI.HSSF.Record.RecordFactory.CreateRecords(Stream in1) at NPOI.HSSF.UserModel.HSSFWorkbook..ctor(DirectoryNode directory, Boolean preserveNodes) at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=NPOI.HSSF.Record.LeftoverDataException,Message=Initialisation of record 0x5B left 1 bytes remaining still to be read.,Source=NPOI,'

Adaś, powiedz mi proszę co trzeba zrobić, żeby mieć takie poszewki na poduszki :)
P.S. Świetna robota - w szczególności cały cykl filmików o ADF

Hi Adam,
Thanks for the content.
is there a way to automate and create a job / task to add excel data in Azure database.
Thank you in Advance

The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)
how do you handel it?
failed while creating connection to DataLake with Excel 5.0

Awesome content Adam. Specially the demos are pretty helpful. Please make more videos covering other use cases using ADF.

can we use Python in Databricks????

Great video Adam! So, isn't it possible to use files with xlsb extension?

Hi man this video helped me a lot! Hey is there any workaround or alternative in keeping an csv or excel file updated in the azure container? Imagine a pipeline -> Source=excel -> Sink=SQL Database, and that excel file has to be updated each day with new info

Hi Your video is awesome I have a question, how to schedule each sheet at some time..can you please post a video regarding the same..

Your ADF playlist is AWESOME 🙂 and make videos on real time scenarios. Thank you...

Thank you

And how to read a xlsb file?

Great Tutorial Adam. Spark-Excel installed on Interactive cluster and used in Development environment is working fine. When moving up to higher enviroments linked services created with Job clusters. How the Spark-Excel library gets Intalled in job clusters?

You nailed it bro

Hi Adam,
Could you please help me to make some column as read-only while writing to excel format using Crealytics spark-excel library

This video is amazingly informative and helpful!
I really appreciate the production value you put into this!

Is there a way to copy multiple sheets in data factory? In databricks I can see how to do. A for or while in .option(“dataAdress”, “myVarHere!<range>”) can do it. But how achieve this in data factory? WIth parameters?

Very clear explanation and well organized tutorial. Thank you so much for sharing. Keep up the great work!

Very Excellent Video, nice step by step tutorial.

Can I compile Tables in one excel and automate it to do this on a daily basis

i have problem at data factory as it say no github. How i can encounter

Very nice video 👍

Hi Adam, I hope you're still monitoring this. First of all, superb video and has helped me in meeting some of my business requirements. One thing which I would like to understand is how can we load the worksheet name(eg: Cars, Planes etc) in a separate Excel or CSV file as record of data. Waiting for your response. Thanks

Just a small question. If I make changes to underlying excel data, will this pipeline work? I want to connect my Excel file to the Azure SQL database and I am using this video for reference. I want to have an updated Azure SQL database whenever there is a change in connected Excel data.

ufff! Awesome video, thanks a lot

Great video but you have not shown with xlsb file format..!

How to load multiple xlsx files with same folder path at a time into databricks using pyspark?

Good job Adam.

awesome tutorial Adam... Thanks for sharing..

how would you pass the file name as a parameter? for eg we get filenames with the format .. data_20200511.xls where the date component changes in the file name

Adam, is it possible to load data to on prem db using ADF ?

Very nice tutorial, would you consider these activities as IT only or do you consider databricks as something the business could setup? The business is using currently R only locally, but would like to take advantage of the azure (spark) environment. Any considerations or advice on our journey? Thx

install spark-excel seems to be ok, but the error message: NoClassDefFoundError: Could not initialize class com.crealytics.spark.excel.WorkbookReader$at com.crealytics.spark.excel.DefaultSource.createRelation(DefaultSource.scala:28)? anyone can help?

thanks, can it be implemented on aws databricks? seems not ?

error : NoClassDefFoundError: Could not initialize class com.crealytics.spark.excel.WorkbookReader$

using com.crealytics:spark-excel_2.12:0.13.1 for scala 2.12

Hi Adam, is there a way where we can write all the data from dataframe. I have millions of records and while writing in .xlsx format it is only writing max rows which one excel sheet can handle. It should split and write all the rows right like how it does for . CSV?

Hello Adam, how we can archive an excel file as Excel are supported as Sink..Any tips ..Thank you ( Reference to Azure Data Factory )

Hello! Does this library have other dependencies? I'm doing the simplest case possible, your first example, but getting an error when I try to do anything with the dataframe: "Could not initialize class org.apache.spark.rdd.RDDOperationScope". Any clue what's going on here? It seems like a bug with the library.

Amazing!!!