Bulk Import Legacy .xls Files into Laravel with PhpSpreadsheet

I had a system migration job for a client. The source was Excel files exported one at a time by the legacy system, which had accumulated into almost seven hundred .xls files, plus more than twenty .xlsx master files that staff had merged manually. The whole folder was around 258 MB, with another three MS Access files serving as interfaces that linked to those Excel files to read them. I needed to write an importer to pull the whole collection into a Laravel web application.

In this article, I call the datasets Funds A through F. I have removed all organization names, actual filenames, actual paths, and monetary amounts. What remains is the actual code and the problems I encountered.

The problem that made the migration necessary

Those .accdb files did not store any data themselves. They simply linked to Excel files using absolute paths on one staff member's drive. That meant copying them to another machine immediately left them unable to open. Staff could not cover for one another, and backups could not be restored to a usable state unless the same drive letter was used. That was the real reason for moving to the web, not a desire for something new.

First symptom: unreadable Thai headers

Reading the first six rows of a raw .xls file produced this:

row0 ["'�͡��§ҹ�ѹ��� 27/04/2569 ", ...]              # "ออกรายงานวันที่ ..."
row1 [..., "'�ͧ�عࢵ 04 ࢵ 4 ��к���'", ...]           # หัวรายงานบรรทัดที่ 2
row3 [..., "'�ѧ��Ѵ 1700 �ԧ�����'", ..., "'˹��§ҹ [ORG_CODE] [ORG_NAME]'"]
row5 ['REP', '�ӴѺ���', 'TRAN_ID', 'HN', 'AN', 'PID', '���� - ʡ��',
      '������������', '�ѹ����ѡ��', '�ѹ��˹���', '�����ط��', '',
      'Error Code', '�ͧ�ع', '��������ԡ��', '����Ѻ�觵��']

This revealed two things at once. First, the header was not in the first row. It was at row index 5 because the five rows above it were the report heading. Second, all the Thai column names had turned into mojibake because the old file used tis-620 encoding, but ASCII tokens such as REP TRAN_ID HN AN PID Error Code had all survived intact.

That meant code like array_search('ลำดับที่', $header) would not work at all.

Second symptom: columns differ between datasets

Comparing the actual headers of two datasets by index gave this:

กองทุน A (120 คอลัมน์): 0=REP No. 1=ลำดับที่ 2=TRAN_ID 3=HN 4=AN 5=PID 6=ชื่อ-สกุล
                        7=? 8=วันที่ทำรายการ 9=วันที่ปิดรายการ 10=(ยอด) 11=(ยอด)
                        12=จ่ายจาก  13=Error Code  14=กองทุนหลัก 15=กองทุนย่อย
กองทุน B  (59 คอลัมน์): 0=REP    1=ลำดับที่ 2=TRAN_ID 3=HN 4=AN 5=PID 6=ชื่อ - สกุล
                        7=? 8=วันที่ทำรายการ 9=วันที่ปิดรายการ 10=(ยอด) 11=(ยอด)
                        12=Error Code  13=กองทุน  14=...

Fund A had a "Paid from" column inserted at index 12, shifting Error Code to 13, while the other funds had it at the usual index 12. Hardcoding a single value across datasets would read the wrong error status for the entire dataset without raising any visible error, because the adjacent cells also contained actual data.

/** column index maps ต่อชุดข้อมูล (header เป็น tis-620 เลยต้อง map by ตำแหน่ง) */
private const MAPS = [
    'FUND_A' => ['rep' => 0, 'seq' => 1, 'tran' => 2, 'hn' => 3, 'pid' => 5, 'name' => 6, 'vdate' => 8, 'ddate' => 9, 'comp' => [10, 11], 'err' => 13],
    'FUND_B' => ['rep' => 0, 'seq' => 1, 'tran' => 2, 'hn' => 3, 'pid' => 5, 'name' => 6, 'vdate' => 8, 'ddate' => 9, 'comp' => [10, 11], 'err' => 12],
    'FUND_C' => ['rep' => 0, 'seq' => 1, 'tran' => 2, 'hn' => 3, 'pid' => 5, 'name' => 6, 'vdate' => 8, 'ddate' => 9, 'comp' => [10, 11], 'err' => 12],
    'FUND_D' => ['rep' => 0, 'seq' => 1, 'tran' => 2, 'hn' => 3, 'pid' => 5, 'name' => 6, 'vdate' => 8, 'ddate' => 9, 'comp' => [10, 11], 'err' => 12],
    'FUND_E' => ['rep' => 0, 'seq' => 1, 'tran' => 2, 'hn' => 3, 'pid' => 5, 'name' => 6, 'vdate' => 8, 'ddate' => 9, 'comp' => [10, 11], 'err' => 12],
    'FUND_F' => ['rep' => 0, 'seq' => 1, 'tran' => 2, 'hn' => 3, 'pid' => 5, 'name' => 6, 'vdate' => 8, 'ddate' => 9, 'comp' => [10, 11], 'err' => 12],
];

Declaring it this way makes it immediately clear that only err differs. Adding a new dataset takes one more line without touching the logic. Another detail is that I made comp an array of two columns because the amount was split across two cells that needed to be added together. Reading just one cell would silently lose half the amount.

Third symptom: running out of memory when opening a master file

The largest master file had 120 columns and around twenty-eight thousand rows, with a file size of about 14.8 MB. Loading the entire workbook at once with PhpSpreadsheet immediately exhausted memory under the default memory_limit. There were three layers to the cause.

The biggest improvement came from a read filter, excluding cells before objects were even created.

// จำกัดการอ่านเฉพาะชีทเป้าหมาย + คอลัมน์ A-O (ที่ใช้จริง) เพื่อกัน memory ล้มกับ master ใหญ่
$want = $sheetName ?: 'Detail';
try {
    $reader = IOFactory::createReaderForFile($path);
    $reader->setReadDataOnly(true);
    $reader->setReadFilter($this->columnFilter(15)); // A..O
    try {
        $reader->setLoadSheetsOnly($want);
    } catch (\Throwable) {
        // reader อาจไม่รองรับ — โหลดทั้งหมด
    }
    $ss = $reader->load($path);
} catch (\Throwable $e) {
    return $this->fail('อ่านไฟล์ไม่สำเร็จ: '.$e->getMessage());
}

$sheet = $ss->getSheetByName($want) ?? $ss->getSheetByName('Detail') ?? $ss->getActiveSheet();
$rows = $sheet->toArray(null, true, false, false); // 0-indexed
$ss->disconnectWorksheets();

There are four points in this block I want to draw attention to. First, createReaderForFile() lets PhpSpreadsheet detect whether the file is .xls or .xlsx, without guessing from the extension. Second, setReadDataOnly(true) drops all styles and formatting. This saves a lot because files from legacy systems tend to have colors and borders on every cell. Third, setLoadSheetsOnly() needs a try/catch because some readers do not have this method, and it can throw an exception if the sheet name does not match. Fourth, the last parameter in toArray(null, true, false, false) is false, which prevents column letters from being used as keys, producing a 0-indexed array that matches MAPS exactly. Finally, disconnectWorksheets() releases memory immediately after conversion to an array.

The filter itself is this short: an anonymous class ready to copy and use.

/** อ่านเฉพาะคอลัมน์ 1..$maxCol (ลด memory กับไฟล์ที่มีร้อยคอลัมน์) */
private function columnFilter(int $maxCol): IReadFilter
{
    return new class($maxCol) implements IReadFilter
    {
        public function __construct(private int $max) {}

        public function readCell($column, $row, $worksheetName = ''): bool
        {
            return Coordinate::columnIndexFromString($column) <= $this->max;
        }
    };
}

readCell() is called for every cell before PhpSpreadsheet decides whether to parse it. Returning false means the cell is never created as an object, which is completely different from loading it and discarding it afterward. I also use Coordinate::columnIndexFromString() instead of comparing letters myself, because direct letter comparisons break as soon as AA AB AC appear.

# ต้องดัน memory_limit ตอนรัน ไม่ใช่ ini_set ในโค้ด (PhpSpreadsheet กินตอน load)
php -d memory_limit=1536M artisan claim:import-masters /data/import/op

# บนเซิร์ฟเวอร์: รันจาก CLI เท่านั้น ไม่ผ่าน HTTP (ใช้เวลา ~2-3 นาที/master ใหญ่)

The read filter solved the memory issue, but each large master file still took two to three minutes, which would definitely hit max_execution_time and a gateway timeout if run over HTTP. I therefore decided from the start that this job had to run exclusively as an artisan command, while the web interface would accept only the small files staff updated daily.

Find the header row with a token that will not get garbled

The five-row report heading at the top did not have a consistent height. Some files had four rows, others six. I scanned for the header instead of hardcoding its position.

// find header row (contains 'TRAN_ID')
$hdr = -1;
foreach ($rows as $i => $r) {
    foreach ($r as $c) {
        if (is_string($c) && strtoupper(trim($c)) === 'TRAN_ID') { $hdr = $i; break 2; }
    }
    if ($i > 12) break;
}
if ($hdr < 0) {
    return $this->fail('ไม่พบหัวตาราง (TRAN_ID) ในชีท '.$sheet->getTitle().' — ตรวจว่าเป็นไฟล์ REP/APPEAL');
}

break 2 exits both loops at once, while if ($i > 12) break; gives up early, avoiding a scan of the entire file if it is the wrong type. This line also serves as validation. Files in unsupported formats fail immediately with a Thai message that tells staff what to do next, instead of running for another two minutes only to return an empty result.

One rule that filters out all the junk rows

Files from the legacy system had many intervening rows: repeated sub-headers, total rows, and blank rows. At first, I checked each case individually and felt I would never cover them all. I eventually switched to a single rule: "A data row must have a numeric sequence number."

$parsed = [];
$repNos = [];
for ($i = $hdr + 1; $i < count($rows); $i++) {
    $r = $rows[$i];
    $seq = $r[$map['seq']] ?? null;
    if (! is_numeric($seq)) continue;            // skip sub-header / blanks
    $repno = trim((string) ($r[$map['rep']] ?? ''));
    [$pk, $yy, $mm] = $this->period($repno);
    $comp = 0.0;
    foreach ($map['comp'] as $c) $comp += $this->money($r[$c] ?? 0);
    $tran = trim((string) ($r[$map['tran']] ?? ''));
    $parsed[] = [
        'fund' => $fund, 'kind' => $kind, 'rep_no' => $repno, 'seq' => (int) $seq,
        'tran_id' => $tran, 'hn' => trim((string) ($r[$map['hn']] ?? '')),
        'pid' => trim((string) ($r[$map['pid']] ?? '')), 'name' => trim((string) ($r[$map['name']] ?? '')),
        'ptype' => 'OP', 'visit_date' => $this->date($r[$map['vdate']] ?? null),
        'discharge_date' => $this->date($r[$map['ddate']] ?? null), 'comp_total' => $comp,
        'comp_source' => '', 'error_code' => trim((string) ($r[$map['err']] ?? '')),
        'sub_fund' => '', 'period_key' => $pk, 'period_yy' => $yy, 'period_mm' => $mm,
    ];
    $repNos[$repno] = true;
}

As for the three conversion helpers, I wrote all of them defensively.

private function money($v): float
{
    if ($v === null || $v === '') return 0.0;
    $s = str_replace([',', '฿', ' '], '', (string) $v);
    if ($s === '-' || $s === 'nan') return 0.0;
    return is_numeric($s) ? (float) $s : 0.0;
}

private function date($v): ?string
{
    $s = trim((string) ($v ?? ''));
    if ($s === '' || $s === '-' || $s === '0') return null;
    return mb_substr($s, 0, 19);
}

/** @return array{0:?int,1:?int,2:?int} period_key(yymm), yy, mm */
private function period(string $repno): array
{
    $d = preg_replace('/\D/', '', $repno);
    if (strlen($d) >= 4) {
        $yy = (int) substr($d, 0, 2);
        $mm = (int) substr($d, 2, 2);
        if ($mm >= 1 && $mm <= 12 && $yy >= 50 && $yy <= 99) {
            return [$yy * 100 + $mm, $yy, $mm];
        }
    }
    return [null, null, null];
}

money() is necessary because the source supplies numbers as strings containing commas. Some cells contain a hyphen, and others contain nan, left over from someone opening the file in another tool and exporting it again. Casting directly with (float) would silently produce zero.

For date(), I deliberately avoid parsing into DateTime and simply store the first 19 characters as a string. The source mixes Common Era and Buddhist Era dates, and guessing wrong and ending up 543 years off is far more dangerous than keeping the raw value for now.

period() was the most painful one because the period has no column of its own. It has to be extracted from the first four digits of the document number as yymm, with a two-digit Buddhist Era year. It also needs sanity checks to ensure the month is within 1-12 and the year is within a plausible range. Otherwise, an oddly formatted document number could become a period with month 47 in the database.

Handle duplicate files with a natural key rather than a UNIQUE index

This is the heart of the article. Users who do not work in IT will inevitably attach the same file again, whether because they are unsure the previous attempt succeeded or because they clicked twice. The system needs to handle that without inflating the numbers.

// replace mode (master): ลบข้อมูลชุดนี้ทั้งหมดก่อน แล้วคำนวณใหม่จากไฟล์
if ($replace) {
    DB::table('rep_records')->where('fund', $fund)->where('kind', $kind)->delete();
}

// dedup: preload existing natural keys for these rep_no (ข้ามถ้า replace เพราะเพิ่งลบ)
$existing = [];
if (! $replace) {
    foreach (array_chunk(array_keys($repNos), 400) as $chunk) {
        DB::table('rep_records')
            ->where('fund', $fund)->where('kind', $kind)->whereIn('rep_no', $chunk)
            ->select('rep_no', 'seq', 'tran_id')->get()
            ->each(function ($e) use (&$existing) {
                $existing[$e->rep_no.'|'.$e->seq.'|'.$e->tran_id] = true;
            });
    }
}

$insert = [];
$dup = 0;
foreach ($parsed as $row) {
    $nk = $row['rep_no'].'|'.$row['seq'].'|'.$row['tran_id'];
    if (isset($existing[$nk])) { $dup++; continue; }
    $existing[$nk] = true;   // guard against in-file duplicates too
    $insert[] = $row;
}

foreach (array_chunk($insert, 500) as $chunk) {
    DB::table('rep_records')->insert($chunk);
}

There are four points to emphasize. First, never query once per row. I preload the existing keys into a hash map once and check them with isset(), which is O(1), instead of issuing twenty-eight thousand SELECT queries. The difference is enormous. Second, whereIn needs to be split into chunks because databases limit the number of parameters per statement. Third, there is the line $existing[$nk] = true; after the decision. This is the line that also prevents duplicates within the same file. Fourth, inserts run in chunks of 500 rows.

With this approach, the resulting behavior is that attaching the same file any number of times is safe and does not inflate the numbers. That is what idempotent means for an import job.

Actual results from the log table

I keep one log row per imported file. I have rounded the numbers and changed the filenames in this example, but the proportions are real.

-- คอลัมน์: orig_name, fund, kind, sheet, rows_total, rows_inserted, rows_duplicate, status
('REP_FUND_A.xlsx',        'FUND_A', 'rep',    'Detail',   28068, 28053, 15, 'ok'),
('REP_FUND_A_APPEAL.xlsx', 'FUND_A', 'appeal', 'Detail',     144,   144,  0, 'ok'),
('REP_FUND_B.xlsx',        'FUND_B', 'rep',    'Detail',   11447, 11447,  0, 'ok'),
('FUND_C_REP.xlsx',        'FUND_C', 'rep',    'Detail',    9310,  9310,  0, 'ok'),
('FUND_D_STM.xlsx',        'FUND_D', 'rep',    'SHEET_D',   1325,  1325,  0, 'ok'),
('FUND_E_STM_OP.xlsx',     'FUND_E', 'rep',    'SHEET_E',    296,   296,  0, 'ok'),
('FUND_F_STM_OP.xlsx',     'FUND_F', 'rep',    'SHEET_F',     17,    17,  0, 'ok');

The first line is the most interesting: 28,068 rows read, 28,053 actually inserted, and 15 duplicates, even though this was a single file that had not been attached twice. That means the master file contained duplicate rows within itself from when staff copied and pasted files together. If deduplication only checked for "duplicates already in the DB," those fifteen rows would have gone in without anyone noticing.

Another detail is that the sheet column does not contain Detail for every file, because staff chose their own sheet names in some master files. Instead of guessing in the code, I store a manifest in the config.

/*
 * ไฟล์ master ที่ระบบใช้เป็นแหล่งคำนวณ — 1 ไฟล์ต่อชุดข้อมูล.
 * key = ตำแหน่งไฟล์ (relative ใต้โฟลเดอร์ต้นทาง) · sheet ที่มีข้อมูลรายเคส · fund · kind
 */
return [
    'masters' => [
        [
            'label' => 'กองทุน A · ผลส่งเบิก (REP)',
            'file' => 'FUND_A/FUND_A_REP/REP_FUND_A.xlsx', 'sheet' => 'Detail', 'fund' => 'FUND_A', 'kind' => 'rep',
        ],
        [
            'label' => 'กองทุน A · อุทธรณ์ (APPEAL)',
            'file' => 'FUND_A/FUND_A_REP/REP_FUND_A_APPEAL.xlsx', 'sheet' => 'Detail', 'fund' => 'FUND_A', 'kind' => 'appeal',
        ],
        // ... ชุดอื่น: sheet ไม่ใช่ 'Detail' เสมอไป แต่ละไฟล์เจ้าหน้าที่ตั้งชื่อเอง
    ],
];

ETL uncovers human errors, not just machine errors

While inspecting files before the import, I found that statement files for two different funds had exactly the same size, down to the byte. When I opened them, every cell was identical. This meant someone had overwritten one with the other using Save As while consolidating files. One fund's entire dataset was missing, replaced by the other fund's data.

Before writing an importer, inspect the files with a ten-line script

This is the habit I most want to recommend. Do not start by writing the importer. Open the files first to see what they actually look like. For old .xls files, I use Python with xlrd because it opens them much faster during exploration.

import glob, xlrd
f = sorted(glob.glob(r'/data/import/op/**/eclaim*.xls', recursive=True))
b = xlrd.open_workbook(f[0])
s = b.sheet_by_name('Detail')
print('dims', s.nrows, s.ncols, 'sheets', b.sheet_names())
for r in range(min(8, s.nrows)):
    row = s.row_values(r)
    if any(str(x).strip() == 'TRAN_ID' for x in row):
        print('hdr row', r, [str(x)[:14] for x in row][:18])
        break

These ten lines answer three questions that need answers before writing the actual code: which index contains the header row, whether column counts really differ between datasets, and which sheets are in each file. Skipping this step and going straight to the importer means revising the code another five times as discoveries emerge later.

A sequence you can follow

1. Inspect sample files from every dataset with a short script first. Note where the header row is, how many columns there are, and the sheet names.
2. Declare a column map for each dataset as a const using indexes rather than column names, then find the header row with an ASCII token that will not get garbled by encoding.
3. Open the file with setReadDataOnly + IReadFilter to limit columns + setLoadSheetsOnly wrapped in try/catch, then use 0-indexed toArray and call disconnectWorksheets immediately.
4. Filter out junk rows with one easy-to-check rule, such as requiring a numeric sequence number, then write your own normalizers for money and dates. Do not cast directly.
5. Deduplicate with a natural key + a preloaded hash map to catch both duplicates against the DB and duplicates within the same file, then insert in chunks.
6. Run it as an artisan command with php -d memory_limit, not over HTTP, and keep one audit log row per file so users see the new and duplicate counts every time.

My personal takeaway from this job is one sentence: if the source is a file that humans can edit manually at any time, design the importer on the assumption that the file will be malformed, and have it tell you as early as possible instead of trying to read everything successfully, because a successful read that produces incorrect data costs far more than stopping and reporting a problem. To adapt this structure to other kinds of source files, just change the column map and row-filtering rules. The rest can be reused as a whole wherever it fits. If I have left anything out or made a typo, I will come back and fix it later.