Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
8 years ago

Storage | MS Excel or MS Access

Hi,   Given a scenario where I only have two options to store data     Multiple Excel Workbooks  or One(1) Access Database   Also, let's say potentially it could reach just around 10 million rec...
Need Help
zlokesh's avatar
zlokesh
Resolver I
8 years ago

Hi ovetteabejuela, You are right its always good to access data from Single source instead of multiple workbooks. Here i will suggest write the package in SSIS to convert MS-Excel to MS-Access and use the same Access file.

 

In SSIS select MS Excel as SOURCE and MS-Access as Destination.

 

If this help you Please "Accept as Solution" else let me know. Thanks.

  • ovetteabejuela's avatar
    ovetteabejuela
    Impactful Individual
    8 years ago

    Wait, SSIS... you need SQL server for that right? Is it present in MS Access?

      • ovetteabejuela's avatar
        ovetteabejuela
        Impactful Individual
        8 years ago

        Yes, I'm simply refering to this method as I don't have access to SSIS, for a moment there I thought there is an SSIS package within MS Access... hahaha.

         

        What I really trying to get an answer from is would it help to convert Excel data to access prior to loading into PowerBI

         

        so it's like 

        Multiple Excel Workbook > Power Query (PowerBI)

        versus

        Multiple Excel Workbook > MS Access > Power Query (PowerBI)

         

        But i think you initially favored the latter on your first answer.