นำเข้าไฟล์ .xls จากระบบเก่าหลายร้อยไฟล์ลง Laravel ด้วย PhpSpreadsheet

พอดีมีงานย้ายระบบของลูกค้ารายหนึ่ง ต้นทางเป็นไฟล์ Excel ที่ระบบเก่า export ออกมาให้ทีละใบ สะสมกันมาจนเป็นไฟล์ .xls เกือบเจ็ดร้อยไฟล์ บวกไฟล์ .xlsx ที่เจ้าหน้าที่นั่งรวมมือเป็นไฟล์ master อีกยี่สิบกว่าไฟล์ รวมโฟลเดอร์ราวๆ 258 MB แล้วมีไฟล์ MS Access อีกสามใบทำหน้าที่เป็นหน้าจอลิงก์เข้าไปอ่าน Excel พวกนั้นอีกที ผมเลยต้องเขียนตัว importer ดึงทั้งชุดเข้าเว็บ Laravel

ในบทความนี้ผมเรียกชุดข้อมูลว่ากองทุน A ถึง F ตัดชื่อหน่วยงาน ชื่อไฟล์จริง path จริง และตัวเลขยอดเงินออกหมด ที่เหลือคือโค้ดและอาการจริงที่เจอครับ

ปมที่ทำให้ต้องย้ายตั้งแต่แรก

ไฟล์ .accdb พวกนั้นไม่ได้เก็บข้อมูลเองเลย มันแค่ link ไปหาไฟล์ Excel ด้วย absolute path บนไดรฟ์ของเครื่องเจ้าหน้าที่คนเดียว แปลว่าก๊อปไปเครื่องอื่นแล้วเปิดไม่ขึ้นทันที ทำงานแทนกันไม่ได้ สำรองข้อมูลไปก็กู้กลับมาใช้ไม่ได้ถ้าไม่วางไดรฟ์ตัวอักษรเดิม นี่คือเหตุผลจริงที่ต้องยกขึ้นเว็บ ไม่ใช่เพราะอยากได้ของใหม่

อาการที่หนึ่ง: หัวตารางภาษาไทยอ่านไม่ออก

เปิดไฟล์ .xls ดิบขึ้นมาอ่านหกแถวแรก ได้หน้าตาแบบนี้ครับ

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

อ่านออกได้สองอย่างพร้อมกัน หนึ่งคือหัวตารางไม่ได้อยู่แถวแรก มันอยู่แถว index 5 เพราะห้าแถวบนเป็นหัวรายงาน สองคือชื่อคอลัมน์ภาษาไทยกลายเป็น mojibake หมดเพราะไฟล์เก่าเข้ารหัส tis-620 แต่ token ที่เป็น ASCII อย่าง REP TRAN_ID HN AN PID Error Code รอดมาได้ครบ

แปลว่าโค้ดแบบ array_search('ลำดับที่', $header) ใช้ไม่ได้เลยสักบรรทัด

อาการที่สอง: คอลัมน์ของแต่ละชุดไม่ตรงกัน

เทียบหัวตารางจริงสองชุดเรียงตาม index ได้แบบนี้

กองทุน 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=...

กองทุน A มีคอลัมน์ "จ่ายจาก" แทรกเข้ามาที่ index 12 ทำให้ Error Code เลื่อนไปอยู่ที่ 13 ส่วนกองทุนอื่นอยู่ที่ 12 ตามปกติ ถ้า hardcode ตัวเดียวใช้ข้ามชุด สถานะ error จะอ่านผิดทั้งชุดโดยไม่มี error ขึ้นให้เห็นเลย เพราะช่องข้างๆ ก็มีข้อมูลอยู่จริงเหมือนกัน

/** 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],
];

ประกาศแบบนี้แล้วเห็นความต่างทันทีว่ามีแค่ err ตัวเดียวที่ไม่เหมือนกัน เพิ่มชุดข้อมูลใหม่ก็เพิ่มบรรทัดเดียวไม่ต้องแตะ logic เลย สังเกตอีกอย่างคือ comp ผมทำเป็น array สองคอลัมน์ เพราะยอดถูกแยกไว้สองช่องต้องบวกกัน ถ้าอ่านช่องเดียวยอดจะขาดไปครึ่งหนึ่งแบบเงียบๆ

อาการที่สาม: memory ล้มตอนเปิดไฟล์ master

ไฟล์ master ใบใหญ่สุดคือ 120 คอลัมน์ ราวสองหมื่นแปดพันแถว ขนาดประมาณ 14.8 MB PhpSpreadsheet เปิดทีเดียวทั้ง workbook แล้ว memory exhausted ทันทีบนค่า memory_limit มาตรฐาน สาเหตุมีสามชั้นซ้อนกัน

ตัวที่แก้ได้เยอะที่สุดคือ read filter ครับ ตัดตั้งแต่ยังไม่สร้าง object

// จำกัดการอ่านเฉพาะชีทเป้าหมาย + คอลัมน์ 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();

มีสี่จุดในบล็อกนี้ที่ผมอยากให้ดู หนึ่ง createReaderForFile() ให้ PhpSpreadsheet ดมเองว่าเป็น .xls หรือ .xlsx ไม่ต้องเดาจากนามสกุล สอง setReadDataOnly(true) ตัด style กับ format ทิ้งทั้งหมด อันนี้ประหยัดมากเพราะไฟล์จากระบบเก่าชอบใส่สีใส่เส้นขอบทุกเซลล์ สาม setLoadSheetsOnly() ต้องครอบ try/catch เพราะ reader บางตัวไม่มี method นี้ และมันโยน exception ได้ถ้าชื่อ sheet ไม่ตรง สี่ toArray(null, true, false, false) พารามิเตอร์ตัวสุดท้ายเป็น false คือไม่ให้ใช้ตัวอักษรคอลัมน์เป็น key จะได้ array แบบ 0-indexed ที่ตรงกับ MAPS พอดี แล้วปิดท้ายด้วย disconnectWorksheets() คืน memory ทันทีหลังแปลงเป็น array เสร็จ

ตัว filter เองสั้นแค่นี้เอง เป็น anonymous class ที่ก๊อปไปใช้ได้เลย

/** อ่านเฉพาะคอลัมน์ 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() ถูกเรียกทุกเซลล์ก่อนที่ PhpSpreadsheet จะตัดสินใจ parse คืน false ไปเซลล์นั้นไม่ถูกสร้างเป็น object เลย ซึ่งต่างจากการโหลดเข้ามาแล้วค่อยทิ้งทีหลังโดยสิ้นเชิง และผมใช้ Coordinate::columnIndexFromString() แทนการเทียบตัวอักษรเอง เพราะเจอ AA AB AC เมื่อไหร่การเทียบตัวอักษรตรงๆ จะพังทันที

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

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

ใส่ read filter แล้วรอดเรื่อง memory แต่ยังใช้เวลาสองถึงสามนาทีต่อไฟล์ master ใบใหญ่ ซึ่งชน max_execution_time และ gateway timeout แน่นอนถ้ายิงผ่าน HTTP ผมเลยตัดสินใจตั้งแต่ต้นว่างานนี้ต้องเป็น artisan command เท่านั้น ส่วนหน้าเว็บให้รับเฉพาะไฟล์เล็กที่เจ้าหน้าที่อัปเดตรายวัน

หาแถวหัวตารางด้วย token ที่ไม่มีวันเพี้ยน

หัวรายงานห้าแถวบนความสูงไม่คงที่ บางไฟล์สี่แถว บางไฟล์หกแถว ผมเลยไม่ hardcode แต่สแกนหาแทน

// 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 ออกจากสอง loop พร้อมกัน ส่วน if ($i > 12) break; คือยอมแพ้เร็ว ไม่ต้องสแกนทั้งไฟล์ถ้าเป็นไฟล์ผิดชนิด และบรรทัดนี้ยังทำหน้าที่เป็น validation ในตัวด้วย ไฟล์ที่ไม่ใช่รูปแบบที่รองรับจะ error ตั้งแต่วินาทีแรกพร้อมข้อความไทยที่เจ้าหน้าที่อ่านแล้วรู้ว่าต้องทำอะไรต่อ ไม่ใช่ปล่อยให้วิ่งไปอีกสองนาทีแล้วค่อยได้ผลว่าง

กติกาข้อเดียวที่ตัดแถวขยะได้หมด

ไฟล์จากระบบเก่ามีแถวคั่นกลางเยอะมาก ทั้ง sub-header ซ้ำ แถวรวมยอด แถวว่างเปล่า ตอนแรกผมนั่งไล่เช็คทีละกรณีแล้วรู้สึกว่าไม่มีวันครบ สุดท้ายเลยเปลี่ยนเป็นกติกาเดียวว่า "แถวข้อมูลต้องมีเลขลำดับเป็นตัวเลข"

$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;
}

ส่วน helper สามตัวที่แปลงค่า ผมเขียนแบบระแวงไว้ก่อนทั้งหมด

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() ต้องมีเพราะต้นทางส่งตัวเลขมาเป็นสตริงที่มี comma บางช่องเป็นขีดกลาง บางช่องเป็นคำว่า nan ที่ติดมาจากการที่มีคนเคยเปิดด้วยเครื่องมืออื่นแล้ว export กลับ ถ้า cast (float) ตรงๆ จะได้ศูนย์แบบเงียบๆ

date() ผมตั้งใจไม่ parse เป็น DateTime เลย เก็บเป็นสตริง 19 ตัวแรกไปตรงๆ เพราะรูปแบบวันที่ในต้นทางปนกันทั้ง ค.ศ. และ พ.ศ. การเดาผิดแล้วได้วันที่ห่างกัน 543 ปีอันตรายกว่าการเก็บดิบไว้ก่อนเยอะ

period() เป็นตัวที่เจ็บสุด เพราะงวดไม่มีคอลัมน์ของตัวเอง ต้องแกะจากสี่หลักแรกของเลขเอกสารเป็น yymm แบบ พ.ศ. สองหลัก แล้วต้องมี sanity check ว่าเดือนอยู่ในช่วง 1-12 กับปีอยู่ในช่วงที่เป็นไปได้ ไม่งั้นเลขเอกสารรูปแบบแปลกๆ จะกลายเป็นงวดเดือนที่ 47 ในฐานข้อมูล

กันไฟล์ซ้ำด้วย natural key ไม่ใช่ UNIQUE index

หัวใจของบทความนี้อยู่ตรงนี้ครับ ผู้ใช้ที่ไม่ใช่สาย IT จะแนบไฟล์เดิมซ้ำแน่นอน ไม่ว่าจะเพราะไม่แน่ใจว่ารอบก่อนสำเร็จหรือเปล่า หรือเพราะกดสองที ระบบต้องรับมือได้โดยตัวเลขไม่บวม

// 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);
}

จุดที่ต้องเน้นมีสี่ข้อ ข้อแรกคือไม่ query ต่อแถวเด็ดขาด ผม preload คีย์ที่มีอยู่แล้วขึ้น hash map ครั้งเดียวแล้วเช็คด้วย isset() ซึ่งเป็น O(1) แทนที่จะยิง SELECT สองหมื่นแปดพันครั้ง ต่างกันคนละโลก ข้อสองคือ whereIn ต้องแบ่ง chunk เพราะฐานข้อมูลมีเพดานจำนวน parameter ต่อหนึ่ง statement ข้อสามคือบรรทัด $existing[$nk] = true; หลังตัดสินใจแล้ว บรรทัดนี้แหละที่กันซ้ำภายในไฟล์เดียวกันได้ด้วย ข้อสี่คือ insert แบบ chunk 500 แถว

พอทำแบบนี้ผลลัพธ์เชิงพฤติกรรมคือแนบไฟล์เดิมซ้ำกี่ครั้งก็ปลอดภัย ตัวเลขไม่บวม ซึ่งก็คือความหมายของคำว่า idempotent ในงาน import นั่นเอง

ผลจริงที่ได้จากตาราง log

ผมเก็บ log หนึ่งแถวต่อหนึ่งไฟล์ที่นำเข้า ตัวเลขในตัวอย่างนี้ผมปัดและเปลี่ยนชื่อไฟล์แล้ว แต่สัดส่วนของจริง

-- คอลัมน์: 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');

บรรทัดแรกน่าสนใจที่สุด อ่านได้ 28,068 แถว เข้าจริง 28,053 แถว ซ้ำ 15 แถว ทั้งที่เป็นไฟล์เดียวไม่ได้แนบซ้ำ แปลว่า ไฟล์ master มีแถวซ้ำอยู่ในตัวเอง จากตอนที่เจ้าหน้าที่ก๊อปแปะรวมไฟล์ ถ้า dedup กันแค่ "ซ้ำกับที่มีใน DB" อย่างเดียว สิบห้าแถวนี้จะเข้าไปโดยไม่มีใครรู้

สังเกตอีกอย่างคือคอลัมน์ sheet ไม่ได้ชื่อ Detail ทุกไฟล์ เพราะไฟล์ master บางใบเจ้าหน้าที่ตั้งชื่อ sheet เอง ผมเลยไม่เดาในโค้ดแต่เก็บเป็น manifest ใน 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 ทำให้เจอบั๊กที่คนทำ ไม่ใช่แค่บั๊กที่เครื่องทำ

ตอนไล่ตรวจไฟล์ก่อน import ผมเจอว่าไฟล์ statement ของสองกองทุนคนละชุดกัน มีขนาดเท่ากันเป๊ะถึงหลักหน่วยไบต์ พอเปิดดูข้างในก็เหมือนกันทุกช่อง แปลว่ามีคน Save As ทับกันตอนรวมไฟล์ ข้อมูลของกองทุนหนึ่งหายไปทั้งชุดแล้วถูกแทนด้วยของอีกกองทุน

ก่อนเขียน importer ให้เปิดไฟล์ดูก่อนด้วยสคริปต์สิบบรรทัด

อันนี้เป็นนิสัยที่ผมอยากแนะนำที่สุด อย่าเพิ่งเขียน importer ตั้งแต่บรรทัดแรก ให้เปิดไฟล์ดูก่อนว่าหน้าตาจริงเป็นยังไง สำหรับ .xls เก่า ผมใช้ Python กับ xlrd เพราะเปิดเร็วกว่าเยอะตอนสำรวจ

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

สิบบรรทัดนี้ตอบสามคำถามที่ต้องรู้ก่อนเขียนโค้ดจริง คือแถวหัวตารางอยู่ index ไหน จำนวนคอลัมน์ต่างกันจริงไหมระหว่างชุดข้อมูล และมี sheet อะไรบ้างในไฟล์ ถ้าข้ามขั้นนี้ไปเขียน importer เลย จะได้แก้โค้ดซ้ำอีกห้ารอบตามที่ค้นพบทีหลัง

สรุปเป็นลำดับที่เอาไปทำตามได้

1. สำรวจไฟล์ตัวอย่างของทุกชุดข้อมูลด้วยสคริปต์สั้นๆ ก่อน จดไว้ว่าแถวหัวอยู่ตรงไหน มีกี่คอลัมน์ ชื่อ sheet คืออะไร
2. ประกาศ column map ต่อชุดข้อมูลเป็น const โดยใช้ index ไม่ใช่ชื่อคอลัมน์ แล้วหาแถวหัวด้วย token ASCII ที่ไม่เพี้ยนตาม encoding
3. เปิดไฟล์ด้วย setReadDataOnly + IReadFilter จำกัดคอลัมน์ + setLoadSheetsOnly ที่ครอบ try/catch แล้ว toArray แบบ 0-indexed และ disconnectWorksheets ทันที
4. กรองแถวขยะด้วยกติกาข้อเดียวที่ตรวจง่าย เช่น เลขลำดับต้องเป็นตัวเลข แล้วเขียน normalizer ของตัวเองสำหรับเงินและวันที่ อย่า cast ตรงๆ
5. กันซ้ำด้วย natural key + preload hash map ให้กันทั้งซ้ำกับ DB และซ้ำในไฟล์เดียวกัน แล้ว insert แบบ chunk
6. รันเป็น artisan command พร้อม php -d memory_limit ไม่ยิงผ่าน HTTP และเก็บ audit log หนึ่งแถวต่อหนึ่งไฟล์ให้ผู้ใช้เห็นตัวเลขเข้าใหม่กับซ้ำทุกครั้ง

ส่วนตัวผมสรุปงานนี้เป็นประโยคเดียวว่า ถ้าต้นทางเป็นไฟล์ที่มนุษย์แก้มือได้ตลอด ให้ออกแบบ importer โดยสมมติไว้ก่อนว่าไฟล์ผิดรูปแน่ๆ แล้วให้มันบอกเราให้ไวที่สุด ไม่ใช่พยายามอ่านให้ผ่านทุกกรณี เพราะการอ่านผ่านแล้วได้ข้อมูลผิดแพงกว่าการหยุดแล้วฟ้องเยอะครับ ใครจะเอาโครงนี้ไปปรับใช้กับไฟล์ต้นทางแบบอื่น เปลี่ยนแค่ column map กับกติกากรองแถวก็พอ ที่เหลือใช้ซ้ำได้ทั้งชุดตามสะดวก ตรงไหนผมอธิบายตกหล่นหรือพิมพ์ผิด เดี๋ยวกลับมาแก้เพิ่มให้ภายหลังครับ