Import from CSV or Excel
In this tutorial, you will let users drop or pick a CSV or Excel (.xlsx) file, parse it in the browser, and preview column headers before loading rows into Handsontable DataGrid. You will learn how to use PapaParse for CSV and the built-in ImportFile plugin for Excel, infer column types, and show clear errors.
Overview
This recipe shows a small UI with:
- A drag-and-drop zone and a hidden file input.
- File type detection by extension (
.csvvs.xlsx) and routing to the right parser. - PapaParse for CSV and the built-in
ImportFileplugin for Excel workbooks. - A header preview before you commit data to the grid.
- Clear error messages for wrong type, empty files, and malformed content.
Difficulty: Intermediate
Time: ~20 minutes
Libraries: papaparse (npm in the docs build; a CDN script at runtime for your own HTML pages). Excel needs none: the ImportFile plugin is part of Handsontable.
Drop a .csv or .xlsx file here, or pick a source.
No data loaded yet
Drop a CSV or Excel file above, choose a file, or load the sample data to populate the table.
import Handsontable from 'handsontable/base';import { registerAllModules } from 'handsontable/registry';
registerAllModules();
const CDN_PAPAPARSE = 'https://cdn.jsdelivr.net/npm/papaparse@5.7.0/papaparse.min.js';
const scriptPromises = new Map();
function loadScript(src) { const cached = scriptPromises.get(src);
if (cached) { return cached; }
const p = new Promise((resolve, reject) => { const existing = document.querySelector(`script[data-cdn="${src}"]`);
if (existing) { existing.addEventListener('load', () => resolve(), { once: true }); existing.addEventListener('error', () => reject(new Error(`Failed to load ${src}`)), { once: true }); if (existing.getAttribute('data-loaded') === '1') { resolve(); }
return; }
const s = document.createElement('script');
s.src = src; s.async = true; s.dataset.cdn = src; s.onload = () => { s.setAttribute('data-loaded', '1'); resolve(); }; s.onerror = () => reject(new Error(`Failed to load ${src}`)); document.head.appendChild(s); });
scriptPromises.set(src, p);
return p;}
async function ensurePapa() { if (typeof window.Papa !== 'undefined') { return window.Papa; }
await loadScript(CDN_PAPAPARSE);
if (typeof window.Papa === 'undefined') { throw new Error('PapaParse did not register on window.'); }
return window.Papa;}
function extensionOf(name) { const i = name.lastIndexOf('.');
return i >= 0 ? name.slice(i + 1).toLowerCase() : '';}
function showError(el, message) { if (!el) { return; }
el.textContent = message; el.hidden = false;}
function clearError(el) { if (!el) { return; }
el.textContent = ''; el.hidden = true;}
function normalizeCellValue(value) { if (value === null || value === undefined) { return null; }
if (typeof value === 'number' || typeof value === 'boolean') { return value; }
if (value instanceof Date) { return value.toISOString().slice(0, 10); }
const text = String(value).trim();
return text === '' ? null : text;}
function mapRowByHeaders(row, headers) { const out = {};
for (const key of headers) { out[key] = normalizeCellValue(row[key]); }
return out;}
function processPapaResults(results) { if (results.errors.length > 0) { throw new Error(results.errors[0].message || 'CSV parse error.'); }
const fields = results.meta.fields?.filter((f) => f !== undefined && f !== '') ?? [];
if (fields.length === 0) { throw new Error('No header row found in the CSV.'); }
const rows = results.data.map((row) => mapRowByHeaders(row, fields));
if (rows.length === 0) { throw new Error('No data rows after the header.'); }
return { headers: fields, rows };}
function parseCsvText(text, PapaRef) { const trimmed = text.trim();
if (!trimmed) { throw new Error('The file is empty.'); }
const parsed = PapaRef.parse(trimmed, { header: true, dynamicTyping: true, skipEmptyLines: 'greedy', transformHeader: (h) => h.trim(), });
return processPapaResults(parsed);}
async function parseCsvFile(file, PapaRef) { return new Promise((resolve, reject) => { PapaRef.parse(file, { header: true, dynamicTyping: true, skipEmptyLines: 'greedy', transformHeader: (h) => h.trim(), complete: (results) => { try { resolve(processPapaResults(results)); } catch (e) { reject(e instanceof Error ? e : new Error(String(e))); } }, error: (err) => reject(err instanceof Error ? err : new Error(String(err))), }); });}
async function parseXlsxFile(file) { // A throwaway grid in a hidden element runs the ImportFile plugin. `apply: false` returns the // mapped sheet without touching the grid, and a refused file rejects with a message that says why. const host = document.createElement('div');
host.hidden = true; document.body.appendChild(host);
const parser = new Handsontable(host, { data: [[]], importFile: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/ });
try { const result = await parser.getPlugin('importFile').importFromBlob('xlsx', file, { colHeaders: 'firstRow', apply: false, }); const rawHeaders = Array.isArray(result.colHeaders) ? result.colHeaders : []; const headers = rawHeaders.map((cell, i) => { const text = String(cell ?? '').trim();
return text === '' ? `Column ${i + 1}` : text; });
if (headers.length === 0) { throw new Error('No header row found in the Excel sheet.'); }
const rows = [];
for (const line of result.data) { const obj = {}; let allEmpty = true;
headers.forEach((key, c) => { obj[key] = normalizeCellValue(line[c]); allEmpty = allEmpty && obj[key] === null; });
if (!allEmpty) { rows.push(obj); } }
if (rows.length === 0) { throw new Error('No data rows after the header.'); }
return { headers, rows }; } finally { parser.destroy(); host.remove(); }}
async function parseFile(file) { const ext = extensionOf(file.name);
if (ext === 'csv') { const PapaRef = await ensurePapa();
return parseCsvFile(file, PapaRef); }
if (ext === 'xlsx') { return parseXlsxFile(file); }
throw new Error('Unsupported file type. Use a .csv or .xlsx file.');}
const SAMPLE_CSV = `Product,Category,In stock,PriceWidget A,Hardware,true,19.99Widget B,Hardware,false,24.5Service Pack,Services,true,0`;
function columnsFromHeaders(headers, rows) { return headers.map((data) => { const values = rows.map((row) => row[data]).filter((v) => v !== null);
if (values.length > 0 && values.every((v) => typeof v === 'number')) { return { data, type: 'numeric' }; }
if (values.length > 0 && values.every((v) => typeof v === 'boolean')) { return { data, type: 'checkbox' }; }
return { data, type: 'text' }; });}
const gridContainer = document.querySelector('#example1');const emptyEl = document.querySelector('#import-empty');const errEl = document.querySelector('#import-error');const fileInput = document.querySelector('#import-file');const dropzone = document.querySelector('#import-dropzone');const sampleBtn = document.querySelector('#import-load-sample');
let hot = null;
function loadIntoGrid({ headers, rows }) { const columns = columnsFromHeaders(headers, rows);
if (!hot) { if (emptyEl) { emptyEl.hidden = true; } gridContainer.hidden = false; hot = new Handsontable(gridContainer, { data: rows, columns, colHeaders: headers, rowHeaders: true, height: 'auto', width: '100%', // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/ }); } else { hot.updateSettings({ colHeaders: headers, columns }); hot.loadData(rows); }}
async function handleFile(file) { clearError(errEl);
if (!file) { return; }
if (file.size === 0) { showError(errEl, 'The file is empty.');
return; }
try { const payload = await parseFile(file);
loadIntoGrid(payload); } catch (e) { showError(errEl, e instanceof Error ? e.message : String(e)); }}
fileInput?.addEventListener('change', () => { const f = fileInput.files?.[0];
handleFile(f); fileInput.value = '';});
dropzone?.addEventListener('dragover', (ev) => { ev.preventDefault(); dropzone.classList.add('import-dropzone--active');});
dropzone?.addEventListener('dragleave', () => { dropzone.classList.remove('import-dropzone--active');});
dropzone?.addEventListener('drop', (ev) => { ev.preventDefault(); dropzone.classList.remove('import-dropzone--active'); const f = ev.dataTransfer?.files?.[0];
handleFile(f);});
sampleBtn?.addEventListener('click', async () => { clearError(errEl);
try { const PapaRef = await ensurePapa(); const payload = parseCsvText(SAMPLE_CSV, PapaRef);
loadIntoGrid(payload); } catch (e) { showError(errEl, e instanceof Error ? e.message : String(e)); }});import Handsontable from 'handsontable/base';import { registerAllModules } from 'handsontable/registry';import type { GridSettings } from 'handsontable/settings';import Papa from 'papaparse';
registerAllModules();
type ParsedPayload = { headers: string[]; rows: Record<string, string | number | boolean | null>[];};
type ParsedCellValue = string | number | boolean | null;type ParsedRow = Record<string, ParsedCellValue>;
const CDN_PAPAPARSE = 'https://cdn.jsdelivr.net/npm/papaparse@5.7.0/papaparse.min.js';
declare global { interface Window { Papa?: typeof Papa; }}
const scriptPromises = new Map<string, Promise<void>>();
function loadScript(src: string): Promise<void> { const cached = scriptPromises.get(src);
if (cached) { return cached; }
const p = new Promise<void>((resolve, reject) => { const existing = document.querySelector<HTMLScriptElement>(`script[data-cdn="${src}"]`);
if (existing) { existing.addEventListener('load', () => resolve(), { once: true }); existing.addEventListener( 'error', () => reject(new Error(`Failed to load ${src}`)), { once: true }, ); if (existing.getAttribute('data-loaded') === '1') { resolve(); }
return; }
const s = document.createElement('script');
s.src = src; s.async = true; s.dataset.cdn = src; s.onload = () => { s.setAttribute('data-loaded', '1'); resolve(); }; s.onerror = () => reject(new Error(`Failed to load ${src}`)); document.head.appendChild(s); });
scriptPromises.set(src, p);
return p;}
async function ensurePapa(): Promise<typeof Papa> { if (typeof window.Papa !== 'undefined') { return window.Papa; }
await loadScript(CDN_PAPAPARSE);
if (typeof window.Papa === 'undefined') { throw new Error('PapaParse did not register on window.'); }
return window.Papa;}
function extensionOf(name: string): string { const i = name.lastIndexOf('.');
return i >= 0 ? name.slice(i + 1).toLowerCase() : '';}
function showError(el: HTMLElement | null, message: string): void { if (!el) { return; }
el.textContent = message; el.hidden = false;}
function clearError(el: HTMLElement | null): void { if (!el) { return; }
el.textContent = ''; el.hidden = true;}
function normalizeCellValue(value: unknown): ParsedCellValue { if (value === null || value === undefined) { return null; }
if (typeof value === 'number' || typeof value === 'boolean') { return value; }
if (value instanceof Date) { return value.toISOString().slice(0, 10); }
const text = String(value).trim();
return text === '' ? null : text;}
function mapRowByHeaders(row: Record<string, unknown>, headers: string[]): ParsedRow { const out: ParsedRow = {};
for (const key of headers) { out[key] = normalizeCellValue(row[key]); }
return out;}
function processPapaResults(results: Papa.ParseResult<Record<string, unknown>>): ParsedPayload { if (results.errors.length > 0) { throw new Error(results.errors[0].message || 'CSV parse error.'); }
const fields = results.meta.fields?.filter((f) => f !== undefined && f !== '') ?? [];
if (fields.length === 0) { throw new Error('No header row found in the CSV.'); }
const rows = (results.data as Record<string, unknown>[]).map((row) => mapRowByHeaders(row, fields));
if (rows.length === 0) { throw new Error('No data rows after the header.'); }
return { headers: fields, rows };}
function parseCsvText(text: string, PapaRef: typeof Papa): ParsedPayload { const trimmed = text.trim();
if (!trimmed) { throw new Error('The file is empty.'); }
const parsed = PapaRef.parse<Record<string, unknown>>(trimmed, { header: true, dynamicTyping: true, skipEmptyLines: 'greedy', transformHeader: (h) => h.trim(), });
return processPapaResults(parsed);}
async function parseCsvFile(file: File, PapaRef: typeof Papa): Promise<ParsedPayload> { return new Promise((resolve, reject) => { PapaRef.parse<Record<string, unknown>>(file, { header: true, dynamicTyping: true, skipEmptyLines: 'greedy', transformHeader: (h) => h.trim(), complete: (results) => { try { resolve(processPapaResults(results)); } catch (e) { reject(e instanceof Error ? e : new Error(String(e))); } }, error: (err) => reject(err instanceof Error ? err : new Error(String(err))), }); });}
async function parseXlsxFile(file: File): Promise<ParsedPayload> { // A throwaway grid in a hidden element runs the ImportFile plugin. `apply: false` returns the // mapped sheet without touching the grid, and a refused file rejects with a message that says why. const host = document.createElement('div');
host.hidden = true; document.body.appendChild(host);
const parser = new Handsontable(host, { data: [[]], importFile: true, // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/ });
try { const result = await parser.getPlugin('importFile').importFromBlob('xlsx', file, { colHeaders: 'firstRow', apply: false, }); const rawHeaders = (Array.isArray(result.colHeaders) ? result.colHeaders : []) as unknown[]; const headers = rawHeaders.map((cell, i) => { const text = String(cell ?? '').trim();
return text === '' ? `Column ${i + 1}` : text; });
if (headers.length === 0) { throw new Error('No header row found in the Excel sheet.'); }
const rows: ParsedRow[] = [];
for (const line of result.data as unknown[][]) { const obj: ParsedRow = {}; let allEmpty = true;
headers.forEach((key, c) => { obj[key] = normalizeCellValue(line[c]); allEmpty = allEmpty && obj[key] === null; });
if (!allEmpty) { rows.push(obj); } }
if (rows.length === 0) { throw new Error('No data rows after the header.'); }
return { headers, rows }; } finally { parser.destroy(); host.remove(); }}
async function parseFile(file: File): Promise<ParsedPayload> { const ext = extensionOf(file.name);
if (ext === 'csv') { const PapaRef = await ensurePapa();
return parseCsvFile(file, PapaRef); }
if (ext === 'xlsx') { return parseXlsxFile(file); }
throw new Error('Unsupported file type. Use a .csv or .xlsx file.');}
const SAMPLE_CSV = `Product,Category,In stock,PriceWidget A,Hardware,true,19.99Widget B,Hardware,false,24.5Service Pack,Services,true,0`;
function columnsFromHeaders(headers: string[], rows: ParsedRow[]): GridSettings['columns'] { return headers.map((data) => { const values = rows .map((row) => row[data]) .filter((v): v is string | number | boolean => v !== null);
if (values.length > 0 && values.every((v) => typeof v === 'number')) { return { data, type: 'numeric' as const }; }
if (values.length > 0 && values.every((v) => typeof v === 'boolean')) { return { data, type: 'checkbox' as const }; }
return { data, type: 'text' as const }; });}
const gridContainer = document.querySelector<HTMLElement>('#example1')!;const emptyEl = document.querySelector<HTMLElement>('#import-empty');const errEl = document.querySelector<HTMLElement>('#import-error');const fileInput = document.querySelector<HTMLInputElement>('#import-file');const dropzone = document.querySelector<HTMLElement>('#import-dropzone');const sampleBtn = document.querySelector<HTMLButtonElement>('#import-load-sample');
let hot: Handsontable | null = null;
function loadIntoGrid({ headers, rows }: ParsedPayload): void { const columns = columnsFromHeaders(headers, rows);
if (!hot) { if (emptyEl) { emptyEl.hidden = true; } gridContainer.hidden = false; hot = new Handsontable(gridContainer, { data: rows, columns, colHeaders: headers, rowHeaders: true, height: 'auto', width: '100%', // licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/ }); } else { hot.updateSettings({ colHeaders: headers, columns }); hot.loadData(rows); }}
async function handleFile(file: File | null | undefined): Promise<void> { clearError(errEl);
if (!file) { return; }
if (file.size === 0) { showError(errEl, 'The file is empty.');
return; }
try { const payload = await parseFile(file);
loadIntoGrid(payload); } catch (e) { showError(errEl, e instanceof Error ? e.message : String(e)); }}
fileInput?.addEventListener('change', () => { const f = fileInput.files?.[0];
handleFile(f); fileInput.value = '';});
dropzone?.addEventListener('dragover', (ev) => { ev.preventDefault(); dropzone.classList.add('import-dropzone--active');});
dropzone?.addEventListener('dragleave', () => { dropzone.classList.remove('import-dropzone--active');});
dropzone?.addEventListener('drop', (ev) => { ev.preventDefault(); dropzone.classList.remove('import-dropzone--active'); const f = ev.dataTransfer?.files?.[0];
handleFile(f);});
sampleBtn?.addEventListener('click', async () => { clearError(errEl);
try { const PapaRef = await ensurePapa(); const payload = parseCsvText(SAMPLE_CSV, PapaRef);
loadIntoGrid(payload); } catch (e) { showError(errEl, e instanceof Error ? e.message : String(e)); }});.import-csv-excel-wrap { display: flex; flex-direction: column; margin: 0 -1rem;}
.import-dropzone { padding: 1.5rem 1rem; text-align: center; border: 0; border-bottom: 1px solid var(--sl-color-gray-5, #e0e0e0); border-radius: 0; background: transparent; color: var(--sl-color-gray-2, #555555); font-size: var(--sl-text-xs, 0.75rem); transition: background 0.15s ease;}
.import-dropzone p { margin: 0 0 0.75rem;}
.import-dropzone.import-dropzone--active { background: var(--sl-color-gray-6, #eeeeee);}
.import-actions { display: inline-flex; align-items: center; gap: 0.5rem;}
.import-file-label { display: inline-block; cursor: pointer;}
.import-file-label span,.import-sample-btn { display: inline-flex; align-items: center; padding: 0.375rem 0.75rem; border: 1px solid var(--sl-color-gray-5, #e0e0e0); border-radius: 0; background: var(--sl-color-gray-6, #eeeeee); color: var(--sl-color-gray-2, #555555); font-family: var(--sl-font, Inter, system-ui, -apple-system, sans-serif); font-size: var(--sl-text-xs, 0.75rem); font-weight: 500; line-height: 1.4; cursor: pointer; transition: background-color 0.15s, color 0.15s, border-color 0.15s;}
.import-file-label:hover span,.import-sample-btn:hover { background: var(--sl-color-gray-5, #e0e0e0); color: var(--sl-color-white, #333333);}
.import-file-label input { position: absolute; width: 1px; height: 1px; opacity: 0; pointer-events: none;}
.import-msg { margin: 0; padding: 0.75rem 1rem; border-radius: 0; font-size: var(--sl-text-xs, 0.75rem);}
.import-msg--error { border-bottom: 1px solid var(--sl-color-red, #ef4444); background: var(--sl-color-red-low, rgba(239, 68, 68, 0.12)); color: var(--sl-color-red, #ef4444);}
.import-empty { display: flex; flex-direction: column; align-items: center; justify-content: center; gap: 0.5rem; padding: 2.5rem 1rem; min-height: 200px; color: var(--sl-color-gray-2, #555555); text-align: center;}
.import-empty[hidden] { display: none;}
.import-empty-icon { display: inline-flex; align-items: center; justify-content: center; width: 40px; height: 40px; margin-bottom: 0.25rem; border: 1px solid var(--sl-color-gray-5, #e0e0e0); color: var(--sl-color-gray-3, #777777);}
.import-empty-title { margin: 0; color: var(--sl-color-white, #333333); font-size: var(--sl-text-sm, 0.875rem); font-weight: 600;}
.import-empty-text { margin: 0; max-width: 38ch; font-size: var(--sl-text-xs, 0.75rem); line-height: 1.5;}
#example1 { padding: 1rem; min-height: 180px;}
#example1[hidden] { display: none;}<div class="import-csv-excel-wrap"> <div class="import-dropzone" id="import-dropzone" tabindex="0" role="button" aria-label="Drop a CSV or Excel file here" > <p>Drop a <code>.csv</code> or <code>.xlsx</code> file here, or pick a source.</p> <div class="import-actions"> <label class="import-file-label"> <span>Choose file</span> <input id="import-file" type="file" accept=".csv,.xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,text/csv" /> </label> <button type="button" id="import-load-sample" class="import-sample-btn">Load sample data</button> </div> </div>
<div id="import-error" class="import-msg import-msg--error" hidden></div>
<div id="import-empty" class="import-empty"> <span class="import-empty-icon" aria-hidden="true"> <svg width="20" height="20" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round"> <rect x="3" y="3" width="18" height="18"/> <path d="M3 9h18M3 15h18M9 3v18M15 3v18"/> </svg> </span> <p class="import-empty-title">No data loaded yet</p> <p class="import-empty-text">Drop a CSV or Excel file above, choose a file, or load the sample data to populate the table.</p> </div>
<div id="example1" hidden></div></div>CDN scripts (no bundler)
For a plain HTML page, load Handsontable plus PapaParse from a CDN (pin the version to match what you test):
<script src="https://cdn.jsdelivr.net/npm/papaparse@5.7.0/papaparse.min.js"></script>The JavaScript and React examples load the same URL on demand when window.Papa is missing, so the pattern matches a script-tag setup. The Angular and Vue examples import papaparse from npm instead.
Accept files and detect type
Use
accepton the file input and checkfile.nameto route.csvand.xlsxto the right parser. Reject everything else with a short message.The file input and drop zone (HTML)
<div class="import-dropzone" id="import-dropzone" tabindex="0" role="button"><p>Drop a <code>.csv</code> or <code>.xlsx</code> file here, or pick a source.</p><div class="import-actions"><label class="import-file-label"><span>Choose file</span><input id="import-file" type="file"accept=".csv,.xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,text/csv" /></label><button type="button" id="import-load-sample" class="import-sample-btn">Load sample data</button></div></div>What’s happening:
- The
acceptattribute restricts the system file picker to.csvand.xlsx. This is a hint to the browser only — you must validate the extension in JavaScript as well. - The real
<input>is visually hidden (a 1 px, absolutely positioned element withopacity: 0) and activated via the wrapping<label>. Clicking Choose file triggers the file picker without requiring a separate button. - Load sample data parses a bundled CSV string so users can try the grid without having to upload a file first.
- The drop zone has
tabindex="0"androle="button", so keyboard users can focus it. It has no key handler; keyboard users open the file picker with Choose file.
Extension detection
function extensionOf(name) {const i = name.lastIndexOf('.');return i >= 0 ? name.slice(i + 1).toLowerCase() : '';}What’s happening:
- Takes the file name and returns the lowercase extension (
"csv","xlsx", or""if there is none). lastIndexOf('.')handles names likereport.v2.csvcorrectly by taking the final dot.- The result is compared in
parseFileand an error is thrown for anything that is not.csvor.xlsx.
Wiring up file events
fileInput?.addEventListener('change', () => {const f = fileInput.files?.[0];handleFile(f);fileInput.value = ''; // reset so the same file can be re-selected});dropzone?.addEventListener('dragover', (ev) => {ev.preventDefault();dropzone.classList.add('import-dropzone--active');});dropzone?.addEventListener('dragleave', () => {dropzone.classList.remove('import-dropzone--active');});dropzone?.addEventListener('drop', (ev) => {ev.preventDefault();dropzone.classList.remove('import-dropzone--active');const f = ev.dataTransfer?.files?.[0];handleFile(f);});What’s happening:
- Both code paths (file input
changeand drop zonedrop) hand theFileobject to the samehandleFilefunction. ev.preventDefault()ondragoveris required — without it the browser opens the file instead of passing it to the drop handler.- Adding/removing
import-dropzone--activegives visual feedback while a file is dragged over the zone. - Resetting
fileInput.value = ''after processing allows the user to import the same file a second time.
- The
Parse CSV with PapaParse
Lazy-load PapaParse from a CDN
const CDN_PAPAPARSE = 'https://cdn.jsdelivr.net/npm/papaparse@5.7.0/papaparse.min.js';const scriptPromises = new Map();function loadScript(src) {const cached = scriptPromises.get(src);if (cached) return cached;const p = new Promise((resolve, reject) => {const existing = document.querySelector(`script[data-cdn="${src}"]`);if (existing) {existing.addEventListener('load', () => resolve(), { once: true });existing.addEventListener('error', () => reject(new Error(`Failed to load ${src}`)), { once: true });if (existing.getAttribute('data-loaded') === '1') resolve();return;}const s = document.createElement('script');s.src = src; s.async = true; s.dataset.cdn = src;s.onload = () => { s.setAttribute('data-loaded', '1'); resolve(); };s.onerror = () => reject(new Error(`Failed to load ${src}`));document.head.appendChild(s);});scriptPromises.set(src, p);return p;}async function ensurePapa() {if (typeof window.Papa !== 'undefined') return window.Papa;await loadScript(CDN_PAPAPARSE);if (typeof window.Papa === 'undefined') throw new Error('PapaParse did not register on window.');return window.Papa;}What’s happening:
loadScriptinjects a<script>tag and returns aPromisethat resolves on load and rejects on error.- A
Mapkeyed by URL caches the promise so the script is only injected once even ifloadScriptis called multiple times concurrently. - It checks for a pre-existing
<script data-cdn="...">element — this handles the case where the tag was already added (e.g., in the docs preview runner). ensurePapafirst checkswindow.Papa(set when your page loads PapaParse with a<script>tag), and only loads the CDN script when it is absent.
Why lazy-load?
- Avoids loading a parser the user might never need.
- Works with any host page whether or not a CDN tag was already added — no duplicate downloads.
Parse a CSV file
async function parseCsvFile(file, PapaRef) {return new Promise((resolve, reject) => {PapaRef.parse(file, {header: true,dynamicTyping: true,skipEmptyLines: 'greedy',transformHeader: (h) => h.trim(),complete: (results) => {try {resolve(processPapaResults(results));} catch (e) {reject(e instanceof Error ? e : new Error(String(e)));}},error: (err) => reject(err instanceof Error ? err : new Error(String(err))),});});}What’s happening:
header: truetells PapaParse to treat the first row as column names and return each subsequent row as a plain object keyed by those names.results.meta.fieldscontains the ordered list of column names.skipEmptyLines: 'greedy'drops rows that are entirely whitespace — handy for files with trailing newlines.transformHeader: (h) => h.trim()removes accidental leading/trailing spaces from column names.- After parsing, empty or missing cell values are normalized to
nullso Handsontable displays a blank cell rather than the string"undefined"or"null". dynamicTyping: truelets PapaParse return native numbers and booleans where possible.- Numeric and boolean values are preserved as-is; string values are trimmed, and empty strings are normalized to
null.
Parse a CSV text string (for the bundled sample)
function parseCsvText(text, PapaRef) {const trimmed = text.trim();if (!trimmed) throw new Error('The file is empty.');const parsed = PapaRef.parse(trimmed, {header: true,dynamicTyping: true,skipEmptyLines: 'greedy',transformHeader: (h) => h.trim(),});return processPapaResults(parsed);}What’s happening:
- Works identically to
parseCsvFilebut accepts a raw CSV string instead of aFileobject. - PapaParse’s synchronous
parse(string, opts)overload is used here (nocompletecallback needed). - Used when the user clicks Load sample data to parse a CSV string embedded in the script and feed it into the grid.
Parse Excel with the ImportFile plugin
Read the workbook without a library
The built-in
ImportFileplugin reads.xlsxfiles, so Excel needs no parser of its own. The plugin runs on a grid, so the parser creates a throwaway one in a hidden element and destroys it afterwards:async function parseXlsxFile(file) {const host = document.createElement('div');host.hidden = true;document.body.appendChild(host);const parser = new Handsontable(host, {data: [[]],importFile: true,// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});try {const result = await parser.getPlugin('importFile').importFromBlob('xlsx', file, {colHeaders: 'firstRow',apply: false,});const headers = (result.colHeaders ?? []).map((cell, i) => {const text = String(cell ?? '').trim();return text === '' ? `Column ${i + 1}` : text;});if (headers.length === 0) {throw new Error('No header row found in the Excel sheet.');}const rows = [];for (const line of result.data) {const obj = {};let allEmpty = true;headers.forEach((key, c) => {obj[key] = normalizeCellValue(line[c]);allEmpty = allEmpty && obj[key] === null;});if (!allEmpty) {rows.push(obj);}}if (rows.length === 0) {throw new Error('No data rows after the header.');}return { headers, rows };} finally {parser.destroy();host.remove();}}What’s happening:
importFromBlob('xlsx', file, { colHeaders: 'firstRow', apply: false })reads the first sheet and returns the mapped result without touching the grid:result.colHeadersholds the promoted header row andresult.datathe rows below it.- A refused file (an
.xls, a password-protected workbook, a damaged archive) rejects with the plugin’s own message, andhandleFileshows it as it is. For an.xlsor a password-protected file, the message says to save it as an unprotected.xlsx. - Empty header cells get a fallback name (
Column 1,Column 2, …), and rows that are entirely empty are skipped, so both parsers return the same{ headers, rows }shape. normalizeCellValuekeeps numbers and booleans, keeps theYYYY-MM-DDstrings the plugin derives for date cells, trims other text, and turns blank cells intonull.- The throwaway grid is destroyed in
finally, so a rejected file does not leak it. To import straight into a visible grid, with cell types, merges, and formulas (formulas need theFormulasplugin), call the same method on that grid withoutapply: false.
Route to the right parser
async function parseFile(file) {const ext = extensionOf(file.name);if (ext === 'csv') {const PapaRef = await ensurePapa();return parseCsvFile(file, PapaRef);}if (ext === 'xlsx') {return parseXlsxFile(file);}throw new Error('Unsupported file type. Use a .csv or .xlsx file.');}What’s happening:
extensionOfextracts the lowercase extension. Only.csvand.xlsxare handled; anything else throws immediately before any network request is made.- PapaParse is loaded on demand for CSV. Excel goes through the built-in plugin, so nothing is downloaded for it.
- Both parsers return the same shape:
{ headers: string[], rows: object[] }— the rest of the code does not need to know which parser ran.
Load parsed data into the grid
Build column definitions from header names
function columnsFromHeaders(headers, rows) {return headers.map((data) => {const values = rows.map((row) => row[data]).filter((v) => v !== null);if (values.length > 0 && values.every((v) => typeof v === 'number')) {return { data, type: 'numeric' };}if (values.length > 0 && values.every((v) => typeof v === 'boolean')) {return { data, type: 'checkbox' };}return { data, type: 'text' };});}What’s happening:
- Handsontable’s
columnsoption expects an array of column descriptors. Each descriptor’sdataproperty is the key used to read from the row objects. - The helper infers
numericandcheckboxcolumns when all non-empty values in that column are numbers or booleans. Mixed columns stay as'text'.
Lazy-init the grid on the first load
let hot = null;function loadIntoGrid({ headers, rows }) {const columns = columnsFromHeaders(headers, rows);if (!hot) {emptyEl.hidden = true;gridContainer.hidden = false;hot = new Handsontable(gridContainer, {data: rows,columns,colHeaders: headers,rowHeaders: true,height: 'auto',width: '100%',// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});return;}hot.updateSettings({ colHeaders: headers, columns });hot.loadData(rows);}What’s happening:
hotis created on the first successful import only. Before that, the page shows an empty-state panel (emptyEl) andgridContainer(the#example1div) is kepthiddenso users don’t see a single blank cell.- On the first call: hide the empty state, reveal the grid container, and instantiate Handsontable with the parsed data, columns, and headers.
- On subsequent calls: reuse the existing instance —
updateSettings({ colHeaders, columns })reconfigures the grid’s column shape, thenloadData(rows)replaces the data and triggers a full re-render.updateSettingsmust run beforeloadDataso Handsontable knows which keys to read from the row objects.
Load the bundled sample
const SAMPLE_CSV = `Product,Category,In stock,PriceWidget A,Hardware,true,19.99Widget B,Hardware,false,24.5Service Pack,Services,true,0`;sampleBtn?.addEventListener('click', async () => {clearError(errEl);try {const PapaRef = await ensurePapa();const payload = parseCsvText(SAMPLE_CSV, PapaRef);loadIntoGrid(payload);} catch (e) {showError(errEl, e instanceof Error ? e.message : String(e));}});What’s happening:
SAMPLE_CSVis a small CSV string embedded in the script — enough to demonstrate the grid without forcing the user to upload anything.- The handler ensures PapaParse is available, parses the sample synchronously, and hands the result straight to
loadIntoGrid— no preview step in between. - File uploads use the same
loadIntoGridfunction (seehandleFilein Step 5), so both code paths converge on a single grid-population helper.
- Handsontable’s
Handle errors
Error display helpers
function showError(el, message) {if (!el) return;el.textContent = message;el.hidden = false;}function clearError(el) {if (!el) return;el.textContent = '';el.hidden = true;}What’s happening:
- Both functions guard against a
nullelement (the container may be absent in some environments). hiddenis toggled so the error area takes no space when empty — no flickering or layout shift.textContentis used to prevent XSS in error messages that might echo user-provided input.
All error conditions covered
Condition Message Extension is not .csvor.xlsx"Unsupported file type. Use a .csv or .xlsx file."File has zero bytes "The file is empty."CSV has parse errors First error message from PapaParse CSV has no header row "No header row found in the CSV."CSV has no data rows "No data rows after the header."Excel workbook refused (an .xls, a password-protected or damaged file, a workbook past the import limits)The ImportFileplugin’s own messageExcel sheet is empty "No header row found in the Excel sheet."Excel has no data rows "No data rows after the header."The
handleFileasync function is the single place where all parse errors are caught and forwarded toshowError:async function handleFile(file) {clearError(errEl);if (!file) return;if (file.size === 0) {showError(errEl, 'The file is empty.');return;}try {const payload = await parseFile(file);loadIntoGrid(payload);} catch (e) {showError(errEl, e instanceof Error ? e.message : String(e));}}What’s happening:
file.size === 0is checked before any async work to give an instant response for empty files.- Any error thrown by the parsers bubbles up here and is shown to the user.
- On success, the parsed
{ headers, rows }payload goes straight toloadIntoGrid— no intermediate state to reset on failure. - The Load sample data handler uses the same try/catch shape, so sample-parse errors surface in the same error panel.
- Both functions guard against a
Try it quickly
Click Load sample data in the example to populate the grid from the bundled CSV string, or save the snippet from Step 4 as sample.csv and drop it on the zone.
How it works - complete flow
- User picks or drops a file (or clicks Load sample data) — the
change,drop, or sample button event fires and reacheshandleFile/ the sample handler. - File size check — zero-byte files are rejected immediately (file path only).
- Extension routing —
parseFilereads the extension, sends.csvto PapaParse, and sends.xlsxto theImportFileplugin. The sample path always uses PapaParse viaparseCsvText. - Parsing — CSV goes through PapaParse with
header: true; Excel goes through the built-inImportFileplugin withapply: false, which returns the promoted header row and the rows below it. - Normalization — empty cells become
null, and native numbers/booleans are preserved for CSV and Excel. - Grid population —
loadIntoGridreplacescolHeaders,columns, and the data. The first load swaps the empty-state panel for the grid. - Grid renders — Handsontable re-renders with the new columns and data.
- Errors at any step — caught by
handleFileor the sample-parse handler and shown in the error panel.
What you learned
- How to detect the file type by extension and route CSV files through PapaParse and Excel files through the built-in
ImportFileplugin with a single handler function. - How to ship a bundled CSV sample so users can demo the grid without uploading a file first.
- How to replace the column configuration and the data on every import, and how to keep the grid behind an empty-state panel until the first import.
- How to handle errors at each stage — file size, parsing, and grid load — and surface them in a dedicated error panel.
Next steps
- Explore Export to PDF to add a complementary export flow alongside this import feature.
- Explore the ExportFile plugin for built-in Excel export.
Related guides
- Import from Excel - Import straight into a grid, with cell types, merges, formulas, and comments from the file.
- XLSX engines - How the built-in engine and ExcelJS compare, and their limits.
- Dropped features reference - Every key an import or export reports.
- Export to Excel - Export from Handsontable to
.xlsxwith theExportFileplugin.