Open Forum

Expand all | Collapse all

best tool for read and update and insert from excel?

  • 1.  best tool for read and update and insert from excel?

    GOLD CONTRIBUTOR
    Posted 26 days ago
    what's are the best tools for pulling BC data into excel and pushing it back for insert/update/delete?

    We are on BC17 on premise wave 2

    This also needs to ideally be easy across multiple companies

    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------


  • 2.  RE: best tool for read and update and insert from excel?

    TOP CONTRIBUTOR
    Posted 23 days ago

    Hey Benji,
    Have you tried using the "Edit In Excel" function?
    https://docs.microsoft.com/en-us/dynamics365/business-central/across-work-with-excel

    It does UPDATE for sure, For DELETE you'll probably be flipping a field from active to in-active or similar status change, not actually deleting rows/data.
    I am not sure it supports INSERT.  I have never tried it, but it seems like you should be able to just add rows to the bottom of the sheet and "Publish" it to insert, but you'll have to populate all the required fields.  For INSERT you may need to use a rapid start package depending on the table you are importing into.

    If you don't see it, or you don't have the webclient enabled on a Windows desktop (the only place this is supported.
    You may have some setup to do to enable it.
    This should get heading the right direction to get it set up if you do not have it.
    https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/configuring-excel-addin

    It is really quick to use if you filter your data sets down to a "reasonable" number of rows that you are opening into EXCEL.



    ------------------------------
    -Bob*
    Bob Stahr
    BTC - Business Technology Consulting  | http://bit.ly/wwwbtc
    bob@stahr.us
    ------------------------------



  • 3.  RE: best tool for read and update and insert from excel?

    GOLD CONTRIBUTOR
    Posted 23 days ago
    can this be made to work for an on premise install?

    ------------------------------
    Benji Jasik
    Generate Capital
    ------------------------------



  • 4.  RE: best tool for read and update and insert from excel?

    SILVER CONTRIBUTOR
    Posted 23 days ago
    Edited by Steve Florko 23 days ago
    Hey Benji,   I dont think this is cloud specific however here is the setup URL.   You do have to have the excel add-in and approve it.

    The URL was posted, but check the section (Deploy the Excel add-in for Business Central on-premises)

    https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/configuring-excel-addin

    *** Not every page offers this functionality.


    ------------------------------
    Steve Florko
    IT Director
    DC International
    Carrollton TX
    ------------------------------



  • 5.  RE: best tool for read and update and insert from excel?

    Posted 20 days ago

    You may be able to use RapidStart services, depending on the table(s), fields, and type of changes (not sure if you can DELETE using it or not).  We've used it to update fields on Items, Vendors, Customers, and Purchase Orders.  As well as some light item creation, to create Assembly BOM items.

    The link below is specific to creating 'Configuration Packages', which are used to export and re-import data from excel. But there are plenty of other documents out there, or videos that can guide you through it. 

    https://docs.microsoft.com/en-us/dynamics365/business-central/admin-how-to-prepare-a-configuration-package




    ------------------------------
    Casey Cabler
    ERP Project Manager
    The Music People Inc.
    Berlin CT
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role