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

[@AdamMarczakYT]

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. 😊

[@satyajee9575]

Great videos Adam 👍🏻

[@AdamMarczakYT]

@@satyajee9575 Thank you :)

[@rengaray1]

@@AdamMarczakYT Awesome as always. Thanks

[@arpitbest]

Great man ..

[@santoshatyam1409]

Hii while uploading excel and view the data it is showing invalid but extension is correct.please help me

[@deepjyotimitra1340]

This really helped me alot. We had to deal with lots of excel sheets with diff format. Thank you so much Adam for such an wonderful video.You are a star.

[@AdamMarczakYT]

My pleasure!

[@manishdasgupta2997]

This fits my business case. Thank you so much for this to the point tutorial!

[@AdamMarczakYT]

You're so welcome!

[@big-bang-movies]

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

[@raviv5109]

As usual simple & clear. I really like your videos Adam. Way you explain is so natural.

[@AdamMarczakYT]

I appreciate that!

[@lonaosmani991]

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

[@jatinderarora2261]

One of the awesome tutorials on ADF and Azure Databricks. Thanks for sharing.

[@AdamMarczakYT]

You're very welcome!

[@balajibp7548]

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

[@ericjanssens3475]

Hi Adam, as always this is a great presentation ! Thanks for posting these videos !

[@AdamMarczakYT]

My pleasure!

[@shahid646]

Most demanding solution asked by business for long. Thanks for sharing :)

[@AdamMarczakYT]

My pleasure! thanks!

[@mersihaceranic9640]

Thank you Adam for all your videos and contribution. It helped me a lot.

[@AdamMarczakYT]

Glad to hear it! Thanks for tuning in :)

[@prakashloganathan5726]

Adam, Your contents are outstanding. If you get a chance. Could you please post a video on how to get lineage (likes of Informatica catalog, etc.,) from the Azure Data Factory pipeline?

[@AdamMarczakYT]

Thanks, noted, maybe in the future :)

[@carlosalonsocapilla4796]

Overall, your videos are very good, but man... this video is really amazing! I really liked the way you explained everything from the introduction putting the current problem into context to the possible solutions. I hope you make more videos of this "real problems" style and how to solve them with the different tools that Azure provides us (and if it is related to data engineering better :p ) I congratulate you for the video, very very good.

[@AdamMarczakYT]

Thanks Carlos! I appreciate this more than you know. This is because I want to do few more tutorials in 2021 for 'pure knowledge' where I just cover the service and it's features, but later I want to do more and more real scenario implementations. :)

[@choudhary25]

Thank you Adam for all your video.👍👍👍

[@AdamMarczakYT]

My pleasure! Thanks for watching :)

[@jahnavimurthy]

Thanks for all your videos. They have been very helpful!

[@AdamMarczakYT]

Glad you like them!

[@321zipzapzoom]

Nice and ble to learn the concepts!!Thanks Adam

[@HierImNorden]

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

[@ngophuthanh]

Excellent video. Thanks, Adam.

[@AdamMarczakYT]

My pleasure!

[@scsourav123]

awesome tutorial Adam... Thanks for sharing..

[@AdamMarczakYT]

No problem 👍 my pleasure!

[@pdsqsql1493]

Very Excellent Video, nice step by step tutorial.

[@balanm8570]

As always another awesome video. Thanks a lot for this video... Wondering how you were able to demo most of the azure services with pretty cool clarity and to the point !!!

[@AdamMarczakYT]

It's a gift! Thanks 😊

[@shahid646]

@@AdamMarczakYT I endorse Balan Comment. :)

[@AdamMarczakYT]

@@shahid646 Thanks a bunch :)

[@salmanriaz5184]

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

[@NILSUNIQ]

Great Video. Couple of queries though- 1. How to get all records for selective columns only using crealytics excel,say A:D. 2.How to skip some rows in crealytics excel (say skip first 4 rows but keep headers) as provided in pandas read excel parameter.

[@AdamMarczakYT]

Well, unfortunately spark-excel library is not as flexible and well rounded as pandas. For 1 just use example I've shown in the video by using ranges. For 2 check this github.com/crealytics/spark-excel/issues/65 not sure if they implemented it but it looks like it should be there.

[@abhishek8311]

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

[@ron-quicktips]

Good job Adam.

[@AdamMarczakYT]

Thanks!

[@JuanGarcia-qy9dt]

ufff! Awesome video, thanks a lot

[@AdamMarczakYT]

My pleasure!

[@SuperJamu]

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!”) can do it. But how achieve this in data factory? WIth parameters?

[@amjds1341]

That's awesome. Thanks for posting

[@AdamMarczakYT]

My pleasure, thanks!

[@sharathkarthik7347]

Quality content. Thanks

[@AdamMarczakYT]

Glad you think so!

[@gastondemundo9822]

Awesome vídeo, thanks for sharing

[@RajanieshKaushikk]

Very nice video 👍

[@sudarshant2340]

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..

[@bideveloper357]

Adam,. Can you make a series of databricks tutorials?

[@AdamMarczakYT]

Maybe in the future, yea, it's a cool idea :)

[@bideveloper357]

@@AdamMarczakYT Azure simple basic activities in adf we can understand using Microsoft docs. Please build some complex pipelines or real time projects pipelines. Also please include limitations of activities band work around for that. Like Lookup activity works for only 5k rows.

[@mohamedriyazdeen6563]

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?

[@frclasso]

Amazing!!!

[@AdamMarczakYT]

Thank you! Cheers!

[@carlosjdesouza000]

Hi Adam, Could you make a video explaining how to copy data from mysql table to delta lake storage with data factory? best regards my friend.

[@AdamMarczakYT]

You can use mapping data flows to export to delta lake. docs.microsoft.com/en-us/azure/data-factory/format-delta Unless you mean data lake, which is different from delta lake.

[@cosimocuriale8871]

Great video Adam, very simple and clear. However, is there a method (library as crealytics) that allows to save a csv file without being partitioned? Thanks a lot!

[@AdamMarczakYT]

You can use 'coalesce' or 'repartition' functions and specify 1 partition. This will end up with 1 partition file that's called something along the lines part0000.csv which you can use later on. You can also then use scala to rename that file.

[@chrisretsin7068]

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

[@AdamMarczakYT]

I'd say platform setup should always be done by the internal IT team or IT vendor. But then you can grant them access and teach them how to use it :)

[@solanavargas1284]

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

[@nikhilnikam5077]

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

[@piesogrodnika572]

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

[@RajivGuptaEverydayLearning]

Nice video

[@AdamMarczakYT]

Thanks

[@christofherdelgado177]

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

[@davidcardenas4266]

Great tutorial! Is tjere a way to use in pyspark? I tried but not succeeded.

Awesome! Thanks for sharing!

[@AdamMarczakYT]

Thanks for watching! :)

[@BijouBakson]

Thank you

[@uday20101]

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

[@zamarinen]

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

[@prashantpatil1260]

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

[@amitgulhane8519]

Can we use this same functionality in Azure Synapse notebook?

[@shyamthakur9799]

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

[@Ulfhedan]

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

[@vamsikrishnakilambi]

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?

[@AdamMarczakYT]

You need to write this logic yourself. You can also try Pandas with Python maybe it has more options too.

[@balaramtupili]

Hi Adam, very informative video. I'm facing an issue when printing data even if I defined Custom Schema. RuntimeException: Error while encoding: java.lang.RuntimeException: scala.Some is not a valid external type for schema of string Caused by: RuntimeException: scala.Some is not a valid external type for schema of string

[@balaramtupili]

It resolved by using a new version of the library.

[@AdamMarczakYT]

Cool! Always keep your libraries up to date :)

[@mokshithvsharma764]

@@balaramtupili which library did you use. Could you mention it here.

[@AxL28AxL]

Is it possible to use Azure Data factory to sink data to an Excel file?

[@AdamMarczakYT]

Not at this time :( Maybe in the future MS will add this support docs.microsoft.com/en-us/azure/data-factory/format-excel?WT.mc_id=AZ-MVP-5003556

[@BijouBakson]

At 10:30 you are selecting a table in the sheet. Is there an option for selecting more than just one table, i.e creating additional table datasets to reflect the number of tables in the sheet without recusing to say Databrick? Thank you

[@mominmushtaq6444]

Hey Adam, this was an awesome video !!!. Keep posting videos like this ... I have my project requirement, where I need to get MySQL Database Data in an on-going basis. We have two scenarios while getting the Data from MySQL. 1. First time Copy - Where we will get all the MySQL into Azure Synapse . For this we planned to use ADF to first store data in ADL gen2 and use polybase to store data into Azure synapse. 2. Incremental extract - Where we need to get updated data near real time for which data has updated in MYSQL . Do you have any suggestions for implementing the above 2nd scenario in near real time?. Thanks for your support.

[@AdamMarczakYT]

Near real time scenarios require typically some tool that can perform real time replication based on transnational logs. But I don't know MySQL nor tools like that s I can't help here. If near real time means ~10min maybe simple queries and jobs every 10 minutes are enough with some metadata driven approach. Thanks for stopping by.

[@mominmushtaq6444]

@@AdamMarczakYT ~10 to ~5 mint will be fine too can you suggest how to perform sync data from mysql to synapse with datafactory?

[@dev09able]

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

[@AdamMarczakYT]

Yes as long as your Self-Hosted Integration Runtime is installed in a local network (or extended network with Azure).

[@shubhammahajan9117]

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.

[@srinivasdevarampati6375]

Great video Thanks Adam. While reading list of sheets getting error : value sparkcontext is not a member of org. apache. spark. sql. sparksession spark. sparkcontext. hadoopconfiguration Thanks.

[@AdamMarczakYT]

After installing library remember to do import and detach & reattach notebook.

[@aniketsamant455]

As usual nice video

[@AdamMarczakYT]

Thanks again!

[@JackPickle]

It might be me Adam, but starting this demo now as a total Databrick newbie, some of the commands don't work with the current runtimes available (6.4, 6.6, 7.0, 7.1, 7.2, 7.3 and 7.4) for Crealytics. NB 6.5 does not exist for me. Trying both the supplied scala excel libraries depending on Scala version yields varying results. For example, using 7.4 and 2.12:0.13., no commands run in the workspace. Using 6.6 and 2.11:0.13., most do until I get to the worksheet looper. If it's something I've done wrong, then apologies, but if my assumption is correct - does the syntax for the libraries change so much between runtime versions?

[@AdamMarczakYT]

There's no difference in the language but on Spark 3.0 library probably had some issues. I probably would just install latest package like com.crealytics:spark-excel_2.12:0.13.5 (always check Maven for latest releases). I tested the code on 7.3 runtime with this package and it run with no problems. I ran entire script on 7.3 but also 5.5 with no issues at all.

[@JackPickle]

@@AdamMarczakYT many thanks Adam, I’ll give it whirl first thing. Next thing on my list is to parametrise things like keys and then export the file to an azure sql db. Great video though and really informative

[@AdamMarczakYT]

Make sure to check my tutorial on Databricks Secret Scopes ;) best of luck!

[@terryliu3635]

Thanks Adam. Do you know if there is a way to detect if the Excel has been updated on the SharePoint and trigger the ADF pipeline? Currently we’re using Logic App but not sure if we could avoid using it? Thanks.

[@AdamMarczakYT]

Check out my Azure Data Factory Triggers tutorial, it shows how to trigger ADF with logic app and logic app are amazing for triggering and moving files from sharepoint.

[@terryliu3635]

@@AdamMarczakYT thanks Adam, much appreciated!

[@pawanreddie2162]

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

[@canadatorontovideos7283]

Hi Adam, is there any suggestion to do testing/validation of datas processed in azure data lake ?

[@AdamMarczakYT]

There isn't any service that does this out of the box. So just like in good old days you need to write this by yourself. I tend to do this in databricks as notebooks.

[@canadatorontovideos7283]

@@AdamMarczakYT thanks Adam

[@shivanidubey1616]

Sir very helpful.but if I want to load multiple xl file having multiple sheet .how we will load multiple Excel file having multiple sheet

[@AdamMarczakYT]

Multiple sheet scenario is shown in the video. Multiple files is easy but not in the video. There are plenty of examples on the web/blogs/forums so you can try checking them out.

[@e-zuan2687]

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

[@sebastiencuber7088]

awsome - thanks

[@AdamMarczakYT]

You're welcome!

[@kanishkkashyap4662]

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

[@sureshpallapolua8]

Awesome Explanation. Thank you Adam. can you please explain how can we load dynamically multiple excel workbooks and each workbook having multiple sheets. if possible please provide sourcecode in github. THank you!.....

[@AdamMarczakYT]

Thanks. Dynamic multiple sheet demo was shown in the video so just watch it until the end. But I can't provide you with source code as I don't have any samples other than the one attached in the video description.

[@AA-kq8on]

can we use Python in Databricks????

[@ravitiwari6335]

Hi adam, I was using Mapping data flow in ADF, and some how facing challenges as I am looking for an aggregate function like collect but it should collect only distinct elements, which is not possible as collectdistinct expression function does not exist, can you please suggest how can I implement it.

[@AdamMarczakYT]

Sounds to me like you just need standard aggregate action. Why would you need collect in this case?

[@ravitiwari6335]

@@AdamMarczakYT Thanks for your reply.can you guide which aggregate function. ?Because collect brings all rows of a column2 associated with the unique value of column1 which is placed in group by. Collect is the expression function inside aggregate transformation, but I need a function that does collect distinct.

[@jaimis3639]

Is it possible to skip rows in Azure data factory, when reading Excel files, similar to what you showed in Databricks?. Typically business reports have informational headers that are not part of the data

[@AdamMarczakYT]

You can use range to specify starting row A100:X1000

[@Cristian-ek7xy]

Thanks for the video. How do you automatically test this?

[@AdamMarczakYT]

Excellent question without any good answer I'm afraid. I didn't found any good tool/pattern for testing Azure Databricks notebooks :( I typically just write small notebooks to test other notebooks (similarly to how you write unit test) but that's about it.

[@ChallusMercer]

Thank you for your effort on covering this topic Adam! I have a question - what if i have a customer database running on premise on his machine. Does microsoft offer a tool for exporting data from the database and uploading this data for example to a data lake or what ever location in the cloud for processing this data with data factory and so on? What are the common steps in this case?

[@jgowrri]

Install data gateway to extract on premise data and data factory to load into data lake .. hope this helps

[@AdamMarczakYT]

@@jgowrri is absolutely correct. Except for clarity, data gateway for data factory is called Self-hosted Integration Runtime, not to be mistaken with other Azure service called on-premises data gateway which is used with other services. That said, Integration Runtime with ADF should be used If we are targeting coterminous syncing scenario, i.e. co-existence of both databases for certain period of time. If you are migrating to the cloud as a one time process then maybe you should look at Azure Database Migration Service instead :) Hope this helps. If you want to check integration runtime I already have a video on that. Good luck :)

[@AdamMarczakYT]

@@jgowrri ps. One year ago when I started I wished to grow community to the point where members will help each other. You made my day mate :)

[@giancarlosql2005]

Great Video Adam! One question, do you know a way to read XLSB files in Pyspark? Unfortunately in Pandas it seems it requires a local path and my datalake path is not working :( Do you know a way to read XLSB files in databricks or data factory? Appreciate any feedback you can provide, Thanks!

[@AdamMarczakYT]

I'm pretty sure I've tested pandas on databricks with datalake path previously and it worked.

[@alexfridi8663]

great explanation! Thank you! Is it possible to use excel native formulas to change the content with Databricks?

[@AdamMarczakYT]

not really, only excel understands and executes excel forumals dynamically, for other tools like databricks, data factory, it's just a text with a value

[@alexfridi8663]

@@AdamMarczakYT ok, theoreticaly I can read the values and calculate and write back as you shown in the video. No idea how to use random function with databricks. The requirement is to generate random values and write it back in the same excel cells.

[@ncbshiva]

Hi Adam, Thanks for the Videos, I am following you for all Azure related I created Databricks as shown in your video, but i am facing below error. I have installed both Scala version 2.11 and 2.12. "java.lang.NoClassDefFoundError: Could not initialize class com.crealytics.spark.excel.WorkbookReader$" Could you help me ?

[@AdamMarczakYT]

Hard to say which step did you miss. Did you import the library as per video? Try detaching and attaching notebook too.

[@ncbshiva]

@@AdamMarczakYT Yes, i had imported both the libraries that you have mentioned.

[@AdamMarczakYT]

I'd try redoing the steps from the beginning. Maybe you missed some step. Try restarting cluster too.

[@ThoughtDiffusion]

Hi, I have one question or I would like you to prepare one video on the senario I am putting here. Lets say you have bunch of dcouments in folders or hirachy of the folders , You have one excel file which does contain the metafata of the all documents within the folders. Excel sheet have , document title , ducument type, document created date , document path of folder where its stored in folder, So basically excel sheet is storing all reference entigrity of documents and metadata. This entire source directory how would we upload the each documents in same in Azure blog storage as blobs , also each blob should have metadata to be added , and each blob is stored in pertificular folder in blob storage, folder path is given in the excel sheet for reference. |How would we do this using Azure factory pipe line flow

[@AdamMarczakYT]

I'd write Databricks notebook for this. This logic is too complex to do that in ADF.

[@ThoughtDiffusion]

@@AdamMarczakYT thanks for responding , I really appreciate you and your all vidoes which are very helpful. Could you please suggest any easy way how could we move/copy set of documents to the Azure blog storage with some metadata information ? lets we have set of documents in local machine or One drive, and have another excel file which has document reference and metadata information (few more columns), how would be migrate it to azure bob with document and its metadata ? would it be MS flow ? would it be ADF? would be APPS logic ? Would it be any other way you think ? and how it would be ?

[@SpeedyMechnic]

I've got the need to run a SQL report that produces a few tables, one of the tables has around 300 million rows, I then need to do a SUM() on one of the columns. Should I be using data bricks? What can do this, I think writing out to a csv would be inefficient.

[@AdamMarczakYT]

It depends where the data is. But I don't understand how this is related to a video about excel processing.

[@Bhurchh818]

graet video Adam thanks. is there a way to connect excel files at sharepoint online to data factory. thnaks

[@AdamMarczakYT]

You probably could move them with either data factory or logic apps to blob first. Process them and then transfer them back. This would be the safest approach. Other approach involves using Logic Apps Excel connector for sharepoint for editing, but I discussed my concerns about it int he video.

[@joyyoung3288]

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

[@AdamMarczakYT]

It should be possible. Databricks is a multi-cloud platform and most features are available when it comes to data movement and transformations.

[@MrAconfee]

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.

[@AdamMarczakYT]

All requirements are listed in the video. Just check if your cluster's spark version matches library version. You can also check the details on their website.

[@rohitkulkarni9038]

Is it possible from Powershell can i copy the source table from SQL server to one of the container in CSV format. Please let me know any video releated this Thanks RK

[@AdamMarczakYT]

You can but you need to write the script yourself. There is no out of the box ready script for you to use. Unfortunately I don't have a video covering this topic.

[@rohitkulkarni9038]

​@@AdamMarczakYT​: Please let me know the link if you have for Custom Activity please share it

[@Charango123quena]

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

[@AdamMarczakYT]

Check out my ADF parametrization tutorial kzbin.infopISBgwrdxPM use that to pass parameter to databricks, in databricks use widgets to get parameter value

[@rishabhchaurasia311]

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

[@AdamMarczakYT]

Hard to say, you must have done something differently :( try doing the demo again.

[@SuperJamu]

And how to read a xlsb file?

[@VijayGupta-ni2hm]

Hi Adam can we do incremental from Data flow ...

[@AdamMarczakYT]

Incremental load is really technology agnostic topic, because it's about figuring out technical + data level information as such there is quite few options do it. Check out this doc for some examples docs.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview once you figure out the way you want to go then data flows should be easier to set up.

[@joyyoung3288]

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?

[@AdamMarczakYT]

What's your cluster configuration?

[@vidyasalimath6177]

I was using Excel format in Dataflow as a source and faced issues while data previewing and selection of sheet name with space.kindly let me know if these are supported now

[@AdamMarczakYT]

The bug persists but it's very easy to work around it. As the error message suggests, click on edit to put name of the sheet manually and use single quotes around it. Example: 'My Sheet'. As a result preview data button on the dataset will stop working but data flows preview and flow itself will work just fine.

[@vidyasalimath6177]

@@AdamMarczakYT Hi Adam I have a query can we refer excel file as a wild card path in mapping dataflow,if we have a filename + date.xlsx and date will be dynamic so still can we refer this sheet with tabs.

[@sid0000009]

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

[@AdamMarczakYT]

I probably would use Databricks with Spark-Excel using Scala or better yet Pandas using Python.

[@sid0000009]

@@AdamMarczakYT we have existing pipelines in ADF and just want to plugin the archiving part

[@AdamMarczakYT]

Well, ADF can't do it. You need to employ extra tool. In my opinion use ADF to output CSV's and then call databricks to convert those CSV to Excel. Should be cheap since there is no logic just conversion.

[@sid0000009]

We found binary file format to be working for moving any file formats including excel..might be helpful to someone looking for similar use cases. Thanks..

[@ravipaul1657]

When will next episode coming 😫

[@AdamMarczakYT]

Episodes are coming out every week, sometimes two weeks, why?

[@jagerzhang4059]

@formatDateTime(trigger().startTime, 'yyyyMMdd') Adam how this work for output path, eg like output/2020/12/01 folder to save file

[@jagerzhang4059]

I would like to copy the data every data, to distinguish the folder by date ,how could I create folder daily, like output/2020/12/01 , output/2020/12/02 etc

[@AdamMarczakYT]

Use format in the second parameter like so @formatDateTime(trigger().startTime, 'yyyy/MM/dd') then use concat @concat('output/',formatDateTime(trigger().startTime, 'yyyy/MM/dd'))

[@jeevannr5980]

such a click bait! you did not mention any way to handle xlsm or xlsb, I just wanted that!

[@AdamMarczakYT]

Just because video doesn't have every possible detail explained it doesn't make it a clickbait. Also if you would watch it you would see that I did show how to process XLSM files and that XLSB is not supported and if you need XLSB then use pandas with python.

[@sayanm7750]

Jeevan NR - sad to see your disrespectful comment to Adam who is helping the community voluntarily...and hope you got a chance to notice the grace with which he replied to your complaint. Thanks!!

[@gobigpoker]

@22:05, I keep getting this error: RuntimeException: scala.Some is not a valid external type for schema of string. What do you think might be causing the issue?

[@AdamMarczakYT]

Unfortunately not from top of my head, sorry. :( My guess is you defined schema for table and mismatched it with the file contents.

[@99vi88]

I solved this problem using a cluster with 6.4 Runtime and com.crealytics:spark-excel_2.11:0.13.6 library.

[@SH-qt4ro]

@@99vi88 Cool , Thanks Vinicius Pivetta. I tried multiple times with different option - but was getting similar errors. 6.4 Runtime did the trick (6.4 Runtime and com.crealytics:spark-excel_2.11:0.13.6)