Open Forum

Expand all | Collapse all

Exporting Document Attached Files

  • 1.  Exporting Document Attached Files

    SILVER CONTRIBUTOR
    Posted Dec 12, 2018 05:05 PM
    ​I'm trying to figure out how to export the documents in GP that are attached to Customer Cards without saving them one by one?  We are moving to a new sales tax software and in need to upload all our tax certificates.  Currently, we attach them in GP to the Customer Card Note.  I need to export all of these and mass import them into our new Tax Certification Manager system.  Anyone ever done this before?

    ------------------------------
    Samantha Suire
    Financial Systems Manager/ Consultant
    ------------------------------


  • 2.  RE: Exporting Document Attached Files

    TOP CONTRIBUTOR
    Posted Dec 13, 2018 10:45 AM
    Edited by Steve Endow Dec 13, 2018 10:45 AM
    Hi @Samantha Suire,

    I'm assuming your tax certificates are attached to the customer records using the Doc Attach feature in GP?

    I haven't performed a bulk export​ of Doc Attach files, but I have two blog posts showing how to export individual attachments using SQL (BCP) and .NET.


    https://dynamicsgpland.blogspot.com/2017/05/extract-dynamics-gp-document-attach.html

    https://dynamicsgpland.blogspot.com/2017/05/extract-and-save-dynamics-gp-document.html


    Using some scripting, additional .NET, or an integration tool, it should be possible to export all of the customer attachments.

    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    steveendow (at) gmail (dot) com
    ------------------------------



  • 3.  RE: Exporting Document Attached Files

    SILVER CONTRIBUTOR
    Posted Dec 13, 2018 11:18 AM
    Thanks Steve yes I was able to export one at a time via your BCP Export instructions.  I just need to figure out how to export 45,000 records as I can't do them one at a time.  Yes they are attached to the customer card note as attachments.  I'm thinking if I can figure out how to do just one export via Sql Query then I can have my developer write something that can loop and export each one by customerID.  I'll keep playing with it and see if I can figure something out.  Thank you for the great start.

    ------------------------------
    Samantha Suire
    Financial Systems Manager/ Consultant
    ------------------------------



  • 4.  RE: Exporting Document Attached Files

    TOP CONTRIBUTOR
    Posted Dec 13, 2018 11:20 AM
    There is no tool to extract them from GP per se, but there is a stored procedure that I found on this blog post that will extract a single document if you know the ID # of the document in the database.  You will have to modify the stored proc for GP as the stored proc references a generic table:

    Essentially the documents are stored in a table called coAttachmentItems in each GP company database.

    You'll need to change the columns in the stored proc, the docID column in the stored proc would need to be changed to Attachment_ID
    The docname column should be changed to filename
    I'm not sure what this proc is using doctype for, GP doesn't have it
    Change the name "doc" to binaryblob because that's the column name in the GP table for it


    Exporting Binary Files To The File System
    Jahaines remove preview
    Exporting Binary Files To The File System
    In my last post I demonstrated how to use SSIS to load binary files into a SQL Server 2005 VARBINARY(MAX) column, http://jahaines.blogspot.com/2009/10/ssis-importing-binary-files-into.html. This post will focus on recreating the binary documents on the file system. I will be using a combination of TSQL and the BCP utility to perform the export, http://msdn.microsoft.com/en-us/library/ms162802.aspx.
    View this on Jahaines >




    ------------------------------
    Rob Klaproth
    Dynamics Certified Professional
    (GP Install & Configure)
    Sr. GP Consultant
    Armanino, LLP
    San Diego, CA
    ------------------------------



  • 5.  RE: Exporting Document Attached Files

    Posted Dec 20, 2018 10:44 PM
    I was able to use the link that Rob was referencing in his post to extract the data from SQL. Took a bit of messing with but it will work. It requires a few more steps and is not as clean as the .NET solution Steve provided. Once I get the code cleaned up I can share it if anyone wants it.

    ------------------------------
    David Cain
    Herbert L Flake Co. LLC
    Houston TX
    ------------------------------



  • 6.  RE: Exporting Document Attached Files

    TOP CONTRIBUTOR
    Posted Dec 20, 2018 07:23 PM
    In case anyone needs to do a bulk export of Document Attachments in the future, I created a very basic .NET application that can export attachments for Customers, Vendors, and Items.

    Here is my blog post with a link to download the application and source code:

    https://blog.steveendow.com/2018/12/bulk-export-dynamics-gp-document.html

    Note that I built the application in a few hours today, so it is very basic and not a polished commercial app.  You may need to make some modifications for your environment or your specific needs.




    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    steveendow (at) gmail (dot) com
    ------------------------------