• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

build a giant database from zillions of excel workbooks|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / build a giant database from zillions of excel workbooks|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…build a giant database from zillion…
sp_PrintTopic sp_TopicIcon
build a giant database from zillions of excel workbooks
Avatar
Nelson Leobrent
Member
Members
Level 0
Forum Posts: 8
Member Since:
November 1, 2020
sp_UserOfflineSmall Offline
1
November 1, 2020 - 6:37 pm
sp_Permalink sp_Print sp_EditHistory

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 🙂

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
November 1, 2020 - 7:14 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Nelson Leobrent
Member
Members
Level 0
Forum Posts: 8
Member Since:
November 1, 2020
sp_UserOfflineSmall Offline
3
November 1, 2020 - 8:26 pm
sp_Permalink sp_Print sp_EditHistory

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 🙂

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
November 2, 2020 - 10:00 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Velouria
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.