Excel exported from Configuration Package

Background: Existing Business Central only support to covert value in Base64 format for BLOB field, so we need to convert our longer text into Base64 format.

MS Document

Refer to the MS document: the BLOB type fields which is use for entering multiple lines of text can be imported using the Configuration Package.  However, it has issue when using configuration package for import the data. Before the issue is resolved by MS, a workaround solution mentioned in this KB that allows data import to BC using the Configuration Package is required.

https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/smb/dynamics365-business-central/export-import-multiline-text-using-configuration-packages#feature-details


Assumption: We assume Developer Tab can be used in Excel (If not sure, the page end has a step to enable this)

Action: We need to add module inside Excel for Blob field conversion.



Step-by-step guide

1. Open Excel

  • Open Microsoft Excel.

2. Open the Visual Basic for Applications (VBA) Editor

  • Press Alt + F11 on your keyboard to open the VBA editor.

3. Insert a New Module

  • In the VBA editor, go to the menu bar.
  • Click on Insert > Module. This will create a new module in your VBA project.


Expected Result


4. Copy and Paste the VBA Code

  • Copy the VBA code you want to import:


Function Encoding(text$)
    Dim i
    With CreateObject("ADODB.Stream")
        .Open: .Type = 2: .Charset = "utf-8"
        .WriteText text: .Position = 0: .Type = 1: i = .Read
        With CreateObject("Microsoft.XMLDOM").createElement("b64")
            .DataType = "bin.base64": .nodeTypedValue = i
            Encoding = Replace(Mid(.text, 5), vbLf, "")
        End With
        .Close
    End With
End Function

Function Decoding(b64$)
    Dim i
    With CreateObject("Microsoft.XMLDOM").createElement("b64")
        .DataType = "bin.base64": .text = b64
        i = .nodeTypedValue
        With CreateObject("ADODB.Stream")
            .Open: .Type = 1: .Write i: .Position = 0: .Type = 2: .Charset = "utf-8"
            Decoding = .ReadText
            .Close
        End With
    End With
End Function



5. Save



6. In Excel Sheet, =Encoding(Blob value)

C3 FormulaD3 Formula
=Encoding(B4)=Decoding(C4)


Verification


Step-by-Step Guide to Add the Developer Ribbon in Excel

1. Open Excel

  • Open Microsoft Excel.

2. Go to Excel Options

  • Click on the File tab in the top-left corner of Excel.
  • From the menu that appears, click on Options at the bottom.

3. Open the Customize Ribbon Options

  • In the Excel Options window, look for the left sidebar.
  • Click on Customize Ribbon.

4. Enable the Developer Tab

  • On the right side, you will see a list of Main Tabs.
  • Look for the Developer option in the list of tabs.
  • Check the box next to Developer.

5. Click OK

  • Click the OK button to close the Excel Options window.

6. Verify the Developer Tab

  • The Developer tab should now appear in the Excel ribbon at the top of the screen.