Import Excel rows into a Drupal content type using batch process
We are creating a module that lets a site admin upload an Excel (.xlsx) file and import each row as a Drupal node (a content type) using Drupal Batch API. Batch processing keeps the UI responsive and safe for large uploads. The example uses PhpSpreadsheet to parse Excel files.
Prerequisites
- Drupal 9/10/11 site.
- Composer support in your project.
- Install PhpSpreadsheet:
composer require phpoffice/phpspreadsheet
4. Create a custom module folder, e.g. modules/custom/dn_import.
Assume we have content type sample_student with fields Card number, student name, class name and bus route. Using this excel we are going to insert data into this content type,
Files needed
dn_import/
dn_import.info.yml
dn_import.routing.yml
src/Form/ExcelImportForm.php
dn_import.info.yml
name: dn_import
type: module
description: dn_import module created by DigitalNadeem.com.
package: Custom
version: 1.0
core_version_requirement: ^9 || ^10 || ^11dn_import.routing.yml (admin page)
dn_import.import_form:
path: '/admin/content/student-import-sheet'
defaults:
_form: '\Drupal\dn_import\Form\StudentImportForm'
_title: 'Import Students'
requirements:
_permission: 'administer nodes'
Create a form with a file upload field in form.
On submit:
- Read the Excel file with PhpSpreadsheet.
- Extract header row (column names) & data rows.
- Optionally validate column names and row counts.
- Build batch operations (process rows in chunks).
- Start Batch API.
- Batch callback: create nodes from rows, track created/fail counts.
- Batch finished callback: show summary message.
Create a form – /src/Form/StudentImportForm.php
In this form we have a file upload field and link to a sample excel template. This template is used to fill the data and upload in the same form.
At the top of form calls file include php spreadsheet – use PhpOffice\PhpSpreadsheet\IOFactory;
Create form build function as below
public function buildForm(array $form, FormStateInterface $form_state) {
// Provide a download link for the Excel template.
$form['template_download'] = [
'#markup' => $this->t(
'<a href="/web/sites/default/files/students-sheet.xlsx" download>Download Excel Template</a>'
),
];
$form['excel_file'] = [
'#type' => 'file',
'#title' => $this->t('Upload Excel File (.xlsx)'),
'#required' => TRUE,
];
$form['submit'] = [
'#type' => 'submit',
'#value' => $this->t('Import Students'),
];
return $form;
}
This is the sample excel we are going to import.
First create submit form function.
public function submitForm(array &$form, FormStateInterface $form_state) {
}
Inside submit form, read the excel rows and save it in $rows variable.
if ($file) {
$filepath = $file->getFileUri();
$realpath = \Drupal::service('file_system')->realpath($filepath);
$spreadsheet = IOFactory::load($realpath);
$sheet = $spreadsheet->getActiveSheet();
$rows = [];
foreach ($sheet->toArray() as $index => $row) {
if ($index == 0) continue; // skip header
$rows[] = $row;
}
Inside above if condition, after loading all rows data in $rows variable, set the batch operation.
$batch = [
'title' => $this->t('Importing Students...'),
'operations' => [],
'finished' => [self::class, 'batchFinished'],
];
foreach ($rows as $i => $row) {
$batch['operations'][] = [
[get_class($this), 'processRow'], [$row, $i]
];
}
batch_set($batch);
Here processRow callback function processes each row, Drupal’s batch API executes processRow to process rows in batches.
‘batchFinished’ is the call back where we are showing a summary of the batch execution.
Implement processRows as below.
public static function processRow($row, $i, array &$context) {
$card_number = trim($row[0]);
$student_name = trim($row[1]);
$class_value = trim($row[2]);
$bus_route_name = trim($row[3]);
// Check for duplicate card number (title)
$existing = \Drupal::entityTypeManager()->getStorage('node')
->loadByProperties([
'type' => 'student',
'title' => $card_number,
]);
if (!empty($existing)) {
$context['results']['duplicates'][] = [
'student_name' => $student_name,
'card_number' => $card_number,
];
return; // skip
}
$node = \Drupal\node\Entity\Node::create([
'type' => 'student',
'title' => $card_number,
'field_student_name' => $student_name,
'field_class' => $class_value,
'field_bus_point' => $bus_route_name,
]);
$node->save();
// update counter
if (!isset($context['results']['imported_count'])) {
$context['results']['imported_count'] = 0;
}
$context['results']['imported_count']++;
$context['message'] = t('Processed @name', ['@name' => $student_name]);
}
Here “$context” variable is very important as it will update the status of batches to the batch API. for each row we are creating a node using Node::create() function.
After creation of nodes , we update $context array. Here $context[‘results’][‘imported_count’] this the batch counter and messages are stored in $context[‘message’]. Later we will retrieve this message variable in batchFinished call back function.
Next we have implement batchFinished call back as below.
public static function batchFinished($success, $results, $operations) {
if (isset($results['imported_count'])) {
\Drupal::messenger()->addStatus(t('Total students imported: @count', [
'@count' => $results['imported_count'],
]));
}
if (!empty($results['duplicates'])) {
$list = '<ul>';
foreach ($results['duplicates'] as $dup) {
$list .= '<li>' . $dup['student_name'] . ' (Card Number: ' . $dup['card_number'] . ')</li>';
}
$list .= '</ul>';
\Drupal::messenger()->addWarning(t('Skipped duplicate card numbers: @list', ['@list' => $list]));
}
}
After completion of all messages this function will execute.
Access the page using path – /admin/content/student-import-sheet
The page looks like as below.
Complete source code you can download here.
