Excel File Import

Step 4 of 6

In this section, you will learn how to implement the Attach system action to allow users to upload Excel files. This functionality is crucial for providing the data necessary for field mapping and subsequent data import into PromptDialog.

Attach System Action

The Attach system action is designed to facilitate file uploads in PromptDialog page. It enables users to browse their local system, select a file, and upload it to the application. For the Import Any Excel Copilot, this action will handle the uploading of Excel files that contain session data.

Implementation

1. Upload Action

Add an systemaction of Attach type to the PromptDialog page. On the OnAction trigger, call the ReadExcelFile procedure to handle the file upload and processing.

2. Read Excel File

This procedure is responsible for opening the file upload dialog, reading the uploaded file, and loading its contents into the system for processing. We will use the Excel Buffer to import the Excel file data and use it for mapping to Business Central table fields.

local procedure ReadExcelFile()
var
    InStr: InStream;
    SheetName: Text;
    ImportSessionsFromExcelLbl: Label 'Import Sessions from any Excel';
    ExcelFileTypeFilterLbl: Label 'Excel files (*.xlsx, *.xls)|*.xlsx;*.xls', Locked = true;
begin
    UploadIntoStream(ImportSessionsFromExcelLbl, '', ExcelFileTypeFilterLbl, FileName, InStr);
    if FileName = '' then
        exit;

    SheetName := TempExcelBuffer.SelectSheetsNameStream(InStr);

    TempExcelBuffer.Reset();
    TempExcelBuffer.DeleteAll();

    TempExcelBuffer.OpenBookStream(InStr, SheetName);
    TempExcelBuffer.ReadSheet();
end;

Key Functions Explained

• UploadIntoStream: Opens a file dialog for the user to select an Excel file, then reads the file into a stream.
• SelectSheetsNameStream: Identifies the sheet names from the uploaded Excel file, allowing users or the system to select which sheet to import.
• OpenBookStream and ReadSheet: These functions load the selected Excel sheet into the Excel Buffer, preparing it for data extraction and processing.

Next Steps

Now that you have implemented the Attach action to upload Excel files, you can proceed to the next step of dynamically generating prompts based on the uploaded file contents. This will allow users to map Excel columns to Business Central fields for data import.