Bringing in Data Efficiently from Excel (multiple documents and sheets)

Hi, 

I have a set of about 25 excel documents, each with 7 sheets with data on them. I have a few questions about this, as this sort of problem is common in what I've been having issues with, but in a few different ways. 

1. Is there a way assuming all the data is formatted the same on each sheet and document, to input all of it at once? (I really don't want to create 175 connections, one for every sheet and every document)

2. If the data is the same in each excel document (uniform across all 25 documents), but different on each sheet, is there a way to still bring it in and combine it together? What is the most efficient way to do this? (Meaning I would end up with a different dataset in Domo for each sheet, essentially 7 total datasets, combine the 25 documents by sheet)

3. In a similar problem, in which every excel document is the same (uniform documents). But each sheet (5 or so sheets) is a set of about 5 to 25 answers, that has been sent out to over 100 people to fill out. I was hoping that I could set up a way, to build a dataset, that combines each excel into a single row of data, with the responses from all of the sheets for that person's response. Essentially just trying to aggregate all of the data from the survey. What is the most efficient way to accomplish this?

Thank you so much for any direction you can give me on these, I've spent hours and hours trying to figure it out. 

Comments

  • AS
    AS 🔵

    Hi-

     

    In a previous position my team tasked with doing something along these same lines: hundreds of similar as well as disimilar sheets that needed to be uploaded into a single dataset in Domo.  You can connect data multiple ways into Domo. With its API streams and connector library specifically, the Domo out-of-the-box functionality allows for only one dataset per Excel sheet. So you're out of luck there.

     

    However, the framework that Domo Workbench was built with allows companies to build custom connection solutions, which includes the ability to have Workbench use a plugin that can traverse multiple files and aggregate them together.  That same plugin could implement data validation and formatting techniques.

     

    This is the option my team took.  Luckily we had a skilled developer who could figure out the technical details for us.  He built us a plugin and he was able to correct formatting problems and datatype mismatches, as well as aggregate Excel files across multiple folders in a file system.  It worked out really well for us.

     

    This is probably your best, most efficient option, especially if this is a long-term connection strategy.  If you do not have a developer on staff, you can contract the work out to Domo and its partner network.  Reach out to your account executive or customer success manager for more information.

     

    Also see: http://knowledge.domo.com?cid=wb4plugins