Can Mitto get the data from multiple sheets in an excel spreadsheet automatically (ie don’t have to specify the sheet name - it just goes through and adds a table for each sheet)?
The use case:
You have an Excel workbook with multiple tabs.
Is the end goal:
- Separate output database table for each tab? You would need a separate Mitto Excel job for each tab.
- One output database table that unions data from each tab? The Mitto RegEx job doesn’t currently support this use case, but I can put in a feature request.
The end goal for the Excel workbook with multiple tabs is one database table that unions the data from each tab. Please add the feature request.
Note that each sheet in the spreadsheet has the exact same structure. One could use the Mitto UNION job to union together each of the output tables from the separate Mitto Excel Jobs mentioned above.
If I can get the client to split up their multiple-sheet excel file into separate sheets, could I use an Rclone Job to download the individual excel files from a cloud file service (ie onedrive), and then use the Regex Job to union all of these files together into a single DB table?
The original Excel file with tabs can be manually split into multiple Excel files or delimited files (csv, etc) or this could be done programatically.
Here’s a Stack Overflow thread on splitting an Excel workbook into multiple files with Python using pandas: python - splitting an Excel workbook into multiple excel files - Stack Overflow
After creating the Python script you can automate it with a Mitto Python job.