Importing Images and Files into Business Central with DataMigrate Pro

The -t putdata parameter also allows files to be uploaded automatically into a Media or Mediaset field of a specific table in Business Central. The uploaded files are stored in Tenant Media and referenced with a GUID. This GUID is then entered in the corresponding Media or Mediaset field of the target table.

Command syntax

DataMigratePro -t putdata -s <TableNo> -i "<SQL-Befehl>" --automapping <Tabellennummer>

or with a manual mapping file:

DataMigratePro -t putdata -s <TableNo> -i "<SQL-Befehl>" -m <Pfad zur Mapping-Datei>

Parameter description

• -s <TableNo>Specifies the target table in Business Central into which the files are imported.
• -i "<SQL-Befehl>"Defines the SQL command that supplies the information required for the file transfer.
• --automapping <table number>Automatically creates a mapping for the table and field assignment.
• -m <path to mapping file>Specifies a manual mapping file if --automapping is not used.

How it works

1. Run SQL queryThe SQL command passed via -i supplies the information required for the file transfer.For the Media or Mediaset field concerned in the Business Central target table, the following three or four columns must be returned:
2. Upload files
3. Link to the Business Central table

Case 1: upload files from the file system into the [Document Attachment] table

Preparatory work

CREATE TABLE [dbo].[ContractDocuments](
	[Contract Code] [nvarchar](20) NOT NULL,
	[FileName] [nvarchar](255) NOT NULL,
	[Extension] [nvarchar](10) NOT NULL,
	[Path to File] [nvarchar](512) NOT NULL,
 CONSTRAINT [PK_ContractDocuments] PRIMARY KEY CLUSTERED 
(
	[Contract Code] ASC,
	[FileName] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[ContractDocuments] ([Contract Code], [FileName], [Extension], [Path to File]) 
VALUES 
 (N'CONTRACT001', N'Arbeitsvertrag_Befristet.pdf', N'pdf', N'C:\Mustervertraege\Arbeitsvertrag_Befristet.pdf')
,(N'CONTRACT002', N'Arbeitsvertrag_Unbefristet.pdf', N'pdf', N'C:\Mustervertraege\Arbeitsvertrag_Unbefristet.pdf')
,(N'CONTRACT003', N'Ausbildungsvertrag.pdf', N'pdf', N'C:\Mustervertraege\Ausbildungsvertrag.pdf')
,(N'CONTRACT004', N'Kulturvertrag.pdf', N'pdf', N'C:\Mustervertraege\Kulturvertrag.pdf')
,(N'CONTRACT005', N'Bauvertrag.pdf', N'pdf', N'C:\Mustervertraege\Bauvertrag.pdf')
GO

Preparation is also required in the mapping configuration, or you create a manual mapping. Here is the first variant, in which the source configuration follows the SQL query and cannot be derived from the structure of NAV, and therefore has to be entered manually.

The SQL query returns the primary key ([ID],[Table ID],[No_]) and the file information for the media field [Document Reference ID]:

SELECT ROW_NUMBER() OVER(ORDER BY [Contract Code]) [ID]
     , 8052 [Table ID] -- Customer contracts
	 , [Contract Code] [No_]
	 , 21 [Document Type] -- Service Contract
	 , 0 [Line No_]
	 , GETDATE() [Attached Date]
	 , [FileName] [File Name]
     , [Extension] [File Extension]
     , [FileName] [Document Reference ID.Description]
	 , CASE WHEN [Extension] = 'jpg' THEN 'image/jpeg' WHEN [Extension] = 'png' THEN 'image/png' ELSE 'application/octet-stream' END AS [Document Reference ID.MimeType]
     , [Path to File] [Document Reference ID.FilePath]
  FROM [dbo].[ContractDocuments]

Command to run:

DataMigratePro -t putdata -s 1173 -i "SELECT ROW_NUMBER() OVER(ORDER BY [Contract Code]) [ID], 8052 [Table ID], [Contract Code] [No_], 21 [Document Type] -- Service Contract, 0 [Line No_], GETDATE() [Attached Date], [FileName] [File Name], [Extension] [File Extension], [FileName] [Document Reference ID.Description], CASE WHEN [Extension] = 'jpg' THEN 'image/jpeg' WHEN [Extension] = 'png' THEN 'image/png' ELSE 'application/octet-stream' END AS [Document Reference ID.MimeType], [Path to File] [Document Reference ID.FilePath] FROM [dbo].[ContractDocuments]" --automapping 1173

Case 2: uploading files from a BLOB field in the database

If the files are stored in an SQL database as a BLOB, .Content has to be used instead:

SELECT 
    [Contract Code] AS [Code], 
    [FileName] AS [Documents.Description], 
    [MimeType] AS [Documents.MimeType], 
    [FileContent] AS [Documents.Content] 
FROM [Some Table with Document Information]

Command to run:

DataMigratePro -t putdata -s 50000 -i "SELECT ..." --automapping 50000

Prerequisites

• The target table in Business Central must contain a Media or Mediaset field.
• If FilePath is used, the files have to be physically present on the server.
• If content is used, the file must already exist as a BLOB in the SQL database.
• The Business Central instance must be configured for the use of media/mediaset fields.

Supported file formats

• Images: jpg, png, gif, bmp, tiff
• Documents: pdf, txt, docx