Import data from files into Environmental Journals

If you have sources of emissions data from outside of Business Central, you can use Business Central’s Data Exchange Definitions to set up a template for regularly importing data from files. This is very likely to be the case with Employee Commuting data, but could also help if you receive spreadsheets with data about machinery or facility usage.

The initial setup involves:

• Deciding your CSV file structure to import
• Creating a new data exchange definition with field mapping
• Adding the data exchange definition code to the Environmental Journal Template

This unit looks at setting up a data import template for Employee Commuting data from CSV files.

Set up the CSV file to import

The CSV file to import must include at least the following columns:

• Posting date
• Environmental Account Number
• Quantity (e.g., in the case of employee commuting this could represent the total distance travelled)

However, your spreadsheet can include other columns that help you and your colleagues collect the data. The example spreadsheet below has a suitable structure.

Create a Data Exchange Definition

In Business Central, Navigate to Data Exchange Definitions, and use the New action.

Then, in the General FastTab,

• Use the Show more action.
• Enter a Code and Name.
• At Type, select Environmental Journal Import.
• At Reading/Writing XMLport, select 1220 (Data Exch. Import – CSV).

Depending on how you set up your CSV file, you might also need to make other configurations, such as to the File Type, Column Separator or Header Lines.

Then, in the Column Definitions FastTab, enter the relevant information about your CSV file. The image below shows the Column Definitions for the example spreadsheet. You must include definitions for all columns from your CSV file, even though only some columns will be imported in the process.

Then, in the Line Definitions FastTab, add a new line and at Line Type, select Detail. Also enter a relevant Code and Name for the line.

In the Line Definitions FastTab, use the Field Mapping action to start creating the mapping between the relevant columns to import from your CSV file and the corresponding columns in the Environmental Journal.

Then, in the General FastTab of the Field Mapping page,

• At Table ID, select 6237252 (Environmental Journal Line).
• At Name, enter a relevant name for the mapping.
• At Mapping Codeunit, select 1247 (Process Gen. Journal Lines).

Then, in the Field Mapping FastTab,

• Enter the Column No. and Column Caption from your CSV file for posting date, account number and distance travelled.
• In the Field ID column, match the Column No.s with:
  • Field ID 10 (Posting Date)
  • Field ID 22 (Account No.)
  • Field ID 45 (Distance Travelled)

Configure the Environmental Journal Template

After you finish setting up the Data Exchange Definition and Field Mapping, you are ready to link this to the template for the relevant Environmental Journal.

Navigate to Environmental Journal Templates and then,

• Select the relevant line, in this case for the Employee Commuting journal.
• In the Environmental Journal Import Format column, select the code for the Data Exchange Definition you just created.

Import data from a file into the Environmental Journal

After the initial setup, the process to import data into an Environmental Journal is quick and smooth.

Navigate to the Environmental Journals, and choose the relevant journal, in this case the Employee Commuting Journal.

• From the Home action menu, use the Import a File action.
• Then, simply drag in or browse your files for the CSV file to import.

Other resources

Importing data from external files into Environmental Journals (Continia Docs article)