Hey everyone 🙂
Please, I am in a critical situation and I would like to ask for your recommendations.
Please excuse my poor English as it is not my native.
I've found that my boss is asking me to build a giant database from zillions of excel workbooks.
When I google for such a thing I've found many recommended using SQL from Microsoft.
He knows that I am not that geek with neither Excel nor SQL.
I am an intermediate power user in excel but not that geek in Excel plus that I've never used SQL for a live production before.
Now I am lost on the ocean.
The task is to build a database and when I searched I found that should or maybe by using SQL server and SQL database.
Most of the database will be from excel sheets.
So I will clean the data in the excel sheets, then import it to SQL database (to be honest I never did that before and I do not know what is the best practice for such a task).
Then I will use PowerBI to represent the information from the database in different graphs and dashboards. (This is something I never did before but when I googled it I've found that the only or the best way to summarize that database could be done by using PowerBI. (Also I never used it before).
So please advise what path I should take.
I've 3 weeks for such a task or a month maximum.
I've the time to learn but do not know what steps or paths to go through to learn efficiently.
All I am asking for is just tell steps for what topics to do or to go through.
Then I will google those steps or topics on my own to learn it in depth.
Thanks a lot for your valuable time reading my message.
Note: What are the limits of using Power Query instead of SQL?
I am fully dedicated for such a task.
If this is not the right or suitable place for posting such a thread, please recommend the better place 🙂
Thanks a lot 🙂
Hi Nelson,
Welcome to our forum! It would be helpful if you were specific about the number of workbooks, but I'd say you have the following options:
1. Use Power Query to get the data from the workbooks, then load it into Power Pivot where you can model it like you would in a SQL database, then use Excel to visualise the data in Excel Dashboards.
2. Same as above but instead of Excel Dashboards, you can use Power BI, because Power BI also uses Power Query and Power Pivot.
You might be better off using Power BI when getting data from a lot of Excel files because you can use incremental refresh for any new files that are added, whereas Excel will get the data from ALL files each time you refresh the query.
You can learn Power BI, Power Query and Power Pivot here, although 3 weeks is a big ask, unless you have nothing else to do.
Mynda
It maybe about 1000 or 1500 excel workbooks.
Each workbook has different layout regarding formats and contents.
It is like collecting oranges and apples in the same basket.
I did not received the exact real files so far to send you any samples but that is what I've been told.
So you mean no need to use SQL server from the start? and I can use Excel in everything in conjunction with PowerQuery, PowerPivot, and PowerBI? or files will be too slow?
Please if you know any books that go through more details to learn the topics better that would be awesome.
I am fully dedicated to do this task YES 🙂
Hi Nelson,
Correct, no need for SQL. However, I can't predict whether you'll have performance issues with that many workbooks that are all different. It will take you 3+ weeks just to get the data from them all into a common layout so you can append them.
If you have only two layouts, then that's a much easier task, but still may have performance issues.
There are some eBooks on Power Query, Power Pivot and Power BI here. Just keep in mind that these books were written quite a while ago, and the apps have continued to be developed since, so there may be parts of the books that are out of date. You also don't get any support with these books if you have questions along the way.
All the best with this project!
Mynda
