I have access in AWS Glue Databrew, AWS S3 and AWS Athena. Is there a best way to work with multiple sheet excel file using these services?
Scenario:
- I have an excel file that contains 100+ sheets.
- The sheets are named per date.
Example: Sheet 1 is named as 1.1.2022, Sheet 2 is named 1.2.2022, etc.
- This is an example of what is contained per sheet. The date is not to be seen in the table except the name of the sheet. The ‘Periods’ basically just tell the duration each person spent on a certain period.
Name | ID | Period1 | Period2 | Period 3 |
---|---|---|---|---|
Adam | 510 | 05:11:20 | 03:10:33 | 07:19:58 |
Ben | 205 | 04:00:00 | 02:02:02 | 00:25:68 |
I am lost and have no idea how I could possibly combine all of these sheets together to form a single table so I could query them per date in Athena and visualize it on Quicksight.
I have checked a few tutorial videos about partitioning the data in S3 but I am unsure how to proceed. Please help. Thank you.