Importing CSV Files

CSV is one of the most common formats for importing data into Business Central with BCCL. This page walks you through the complete process of importing CSV files, from preparing your data and generating a mapping file to running the import and handling special cases like custom delimiters and automatic number series.

Basic CSV Import Command

The simplest CSV import command looks like this:

bccl -t putdata -i customers.csv -m table18.mapping.json -s table=18

This tells BCCL to:

1. Read data from customers.csv
2. Use table18.mapping.json to map CSV columns to table fields
3. Import the data into table 18 (Customer)

CSV File Requirements

Your CSV file must meet these requirements:

Requirement	Details
Header row	Required by default (first row must contain column names)
Encoding	UTF-8 recommended
Delimiter	Comma by default (configurable with -l)
Quote character	Double-quote by default (configurable with -c)

Example CSV File

Number,Name,ZIP
10000,Adatum Corporation,98052
20000,Trey Research,30329
30000,Fabrikam Inc,60601

The header row values (Number, Name, ZIP) are the identifiers you will reference in the mapping file's MappedName property.

Step-by-Step CSV Import

Follow these steps to import a CSV file into Business Central:

Step 1: Create Your CSV File

Prepare a CSV file with your data. Make sure the first row contains descriptive column headers:

Number,Name,ZIP
10000,Adatum Corporation,98052
20000,Trey Research,30329
30000,Fabrikam Inc,60601

Step 2: Generate a Mapping File

Use the MAPPING task to generate a default mapping file for your target table:

bccl -t mapping -s table=18 -o mapping.json

This creates a JSON file with every field in the Customer table. The output looks similar to this (abbreviated):

{
  "Customer": [
    {"FieldNo":1,"FieldName":"No.","MappedName":"No_","Validate":true},
    {"FieldNo":2,"FieldName":"Name","MappedName":"Name","Validate":true},
    {"FieldNo":5,"FieldName":"Address","MappedName":"Address","Validate":true},
    {"FieldNo":91,"FieldName":"Post Code","MappedName":"Post_Code","Validate":true}
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true,
  "ValidateOnDelete": true
}

Step 3: Edit the Mapping File

Remove any fields you are not importing and update the MappedName values to match your CSV column headers exactly:

{
  "Customer": [
    {"FieldNo":1,"FieldName":"No.","MappedName":"Number","Validate":true},
    {"FieldNo":2,"FieldName":"Name","MappedName":"Name","Validate":true},
    {"FieldNo":91,"FieldName":"Post Code","MappedName":"ZIP","Validate":true}
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true
}

Notice the key changes:

• Only three fields remain -- the ones that match your CSV columns.
• MappedName matches the CSV headers: Number, Name, and ZIP correspond exactly to the CSV column headers.
• Validate is set to true on each field so Business Central runs field-level validation.

Important: The MappedName values are case-sensitive and must match your CSV column headers exactly.

Step 4: Run the Import

Execute the import command:

bccl -t putdata -i customers.csv -m mapping.json -s table=18

Step 5: Review the Result

BCCL returns a JSON result:

{"Table":18,"Inserted":3,"Modified":0}

This confirms that three new customer records were created. If you run the same command again, the result changes to:

{"Table":18,"Inserted":0,"Modified":3}

This is because the records now exist, so BCCL modifies them instead of inserting.

CSV Delimiter Configuration

By default, BCCL uses a comma as the CSV delimiter. If your file uses a different delimiter, specify it with the -l parameter.

Common Delimiters

Delimiter	Parameter	Example
Comma	-l ,	(default)
Semicolon	-l ;	Common in European locales
Tab	-l \t	Tab-separated values
Pipe	-l &#|;	Pipe-delimited files

Semicolon-Delimited Example

For a file like this:

Number;Name;ZIP
10000;Adatum Corporation;98052
20000;Trey Research;30329

Use:

bccl -t putdata -i customers.csv -m mapping.json -s table=18 -l ;

Tab-Delimited Example

bccl -t putdata -i customers.tsv -m mapping.json -s table=18 -l \t

BCCL interprets escape sequences like \t (tab), \n (newline), and \0 (null) in the delimiter parameter.

Quote Character Handling

CSV files commonly use double quotes to wrap values that contain the delimiter or line breaks. BCCL handles this by default using the double-quote character (").

Changing the Quote Character

If your file uses a different quote character (for example, single quotes), use the -c parameter:

bccl -t putdata -i data.csv -m mapping.json -s table=18 -c '

Ignoring Quotes Entirely

If your data does not use quoting and contains literal quote characters, use -q to tell BCCL to ignore quotes:

bccl -t putdata -i data.csv -m mapping.json -s table=18 -q true

When -q is set, BCCL treats quote characters as regular text instead of field delimiters.

CSV Files Without Headers

By default, BCCL expects the first row of a CSV file to contain column headers. If your CSV file has no header row, use -h false:

bccl -t putdata -i data.csv -m mapping.json -s table=18 -h false

When headers are disabled, a mapping file is required. BCCL uses the field order defined in the mapping file to assign values to columns sequentially (first column to first mapped field, second column to second mapped field, and so on).

Validation Settings

You control how much validation Business Central performs during import through two mechanisms:

Field-Level Validation

Set "Validate": true on individual fields in the mapping file. This triggers the field's OnValidate code in AL. For example, validating the Post Code field might auto-fill the City field.

Record-Level Validation

The root-level ValidateOnInsert and ValidateOnModify properties determine whether the table's OnInsert and OnModify triggers fire:

{
  "Customer": [
    {"FieldNo":1,"FieldName":"No.","MappedName":"Number","Validate":true},
    {"FieldNo":2,"FieldName":"Name","MappedName":"Name","Validate":false}
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": false
}

In this example:

• The No. field runs its field validation trigger.
• The Name field is assigned directly without triggering validation.
• New record inserts fire the table's OnInsert trigger.
• Modifications to existing records do not fire the OnModify trigger.

Automatic Number Series

If you want Business Central to assign record numbers automatically from a number series, remove the primary key field from the mapping file. For customers (table 18), remove FieldNo 1:

{
  "Customer": [
    {"FieldNo":2,"FieldName":"Name","MappedName":"Name","Validate":true},
    {"FieldNo":91,"FieldName":"Post Code","MappedName":"ZIP","Validate":true}
  ],
  "ValidateOnInsert": true,
  "ValidateOnModify": true
}

Your CSV file would then contain only the non-key fields:

Name,ZIP
Adatum Corporation,98052
Trey Research,30329

Each imported record receives the next available number from the configured number series.

Binary Data in CSV

If your import includes binary fields (images, documents), you can reference files from within the CSV by prefixing the file path with !:

Number,Name,Picture
10000,Adatum Corporation,!C:\images\adatum.png
20000,Trey Research,!C:\images\trey.png

BCCL reads the referenced file and converts it to Base64 before sending it to Business Central. You must also enable binary field handling with -b true:

bccl -t putdata -i customers.csv -m mapping.json -s table=18 -b true

Language and Regional Settings

When importing data that contains locale-specific formatting (dates, decimals), you can specify a language ID with the -a parameter:

bccl -t putdata -i data.csv -m mapping.json -s table=18 -a 1031

This tells BCCL to interpret values using German formatting (language ID 1031), where commas are decimal separators and periods are thousands separators.

You can also specify format strings for individual fields in the mapping file using the Format property:

{"FieldNo":54,"FieldName":"Last Date Modified","MappedName":"Modified","Validate":false,"Format":"dd/MM/yyyy"}

Paging Large CSV Files

For large CSV files, BCCL sends data in batches. The default page size is 1000 records. Each batch is sent to Business Central as an independent transaction.

Adjust the page size with -z:

bccl -t putdata -i large_import.csv -m mapping.json -s table=18 -z 500

Smaller page sizes use less memory but require more round-trips to Business Central. Larger page sizes are faster but increase the risk of timeouts for complex validations.

Tips and Best Practices

• Always test your import with a small CSV file first to verify mapping correctness.
• Use the -v flag for verbose output when debugging import issues.
• If your CSV comes from a European system, remember to set the delimiter with -l ; and consider the language parameter -a.
• When you need to both create and update records, there is nothing special to do. BCCL handles this automatically based on primary key matching.
• For very large imports (millions of records), consider splitting the CSV into smaller files and running multiple imports.
• Keep your mapping file under version control alongside your import scripts for reproducibility.

Related Topics

• Importing Data Overview
• Importing Excel Files
• Importing XML and JSON Files
• Advanced Import Features
• CSV Formatting for Export