Open Forum

​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
------------------------------

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
------------------------------

Original Message:
Sent: 12-12-2018 05:04 PM
From: Samantha Suire
Subject: Exporting Document Attached Files

​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
------------------------------

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
------------------------------

Original Message:
Sent: 12-12-2018 05:04 PM
From: Samantha Suire
Subject: Exporting Document Attached Files

​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
------------------------------

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
------------------------------

Original Message:
Sent: 12-12-2018 05:04 PM
From: Samantha Suire
Subject: Exporting Document Attached Files

​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
------------------------------

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
------------------------------

Original Message:
Sent: 12-13-2018 10:45 AM
From: Steve Endow
Subject: Exporting Document Attached Files

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

Original Message:
Sent: 12-12-2018 05:04 PM
From: Samantha Suire
Subject: Exporting Document Attached Files

​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
------------------------------

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
------------------------------

Original Message:
Sent: 12-13-2018 11:19 AM
From: Rob Klaproth
Subject: Exporting Document Attached Files

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

Original Message:
Sent: 12-12-2018 05:04 PM
From: Samantha Suire
Subject: Exporting Document Attached Files

​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
------------------------------