Hello,
I am working with an excel file that is structured as shown below. I need to get the Title and Episode info into a flat table. Very new to Matillion so I'm not sure where to even begin to accomplish this.
Is it recommended to use a script to read in the file and write code to "build" each record? Or load the file using the excel component, then use another Matillion component that could unpivot the data? Any help is greatly appreciated.
Hi @james3,
You didn't mention which cloud data warehouse system you are using but either way, the typical pattern would be to load the data into your cloud data warehouse as close to raw as possible and then use the power of the data warehouse system to do the pivoting or other transformations after. If you absolutely have to transform it before loading the data then you could use a pattern where you write python to clean up the data and build an ingestible version of the file before doing the data ingestion. There are a couple design patterns that can help here but the common would be to read the file from it's original location, do the transformation work and drop the new file in a separate location that is slated for ingestion.
As you alluded to, using the Excel component to load the data first and then following that up with a series of SQL statements or a transformation job to organize the data how you would like is probably the best approach.
I hope this help! Thanks for posting!
