/32 2021/05/29 #phpcon_okinawa @ttskch 1 PHPͰCSVͷΠϯϙʔτ/ 
 ΤΫεϙʔτʹཱͪ޲͔͏

/32 • ϦεςΟϯά޿ࠂͷձࣾͰWebαʔϏεΛ࡞ͬͯ·ͨ͠ • CTOͱͯ͠ࡏ੶ͨ͠8೥ؒͰɺࣾһ਺2໊ˠ60໊ɺ 
 ച্ن໛5000ສԁˠ27ԯԁʹ·Ͱ੒௕ 💪 💪 💪 • ຊΛॻ͍ͨΓSchooͰߨٛͨ͠Γͱ͍ͬͨ׆ಈ΋ 2 ͖ͨͭͪ ʙ2020/03 (ג)ΧϧςοτίϛϡχέʔγϣϯζCTO 📝 blog.ttskch.com/thank-you-quartet-communications @ttskch

/32 • डୗ։ൃͱࣾ֎CTO/ٕज़ސ໰ۀத৺Ͱ׆ಈͯ͠·͢ • Symfony͕େ޷͖Ͱ͢ ✨ • ڈ೥͸ΞυϕϯτΧϨϯμʔΛ1ਓͰ15೔ॻ͍ͨΓ 
 10ສจࣈͷిࢠॻ੶Λແྉެ։ͨ͠Γ͠·ͨ͠💪💪💪 3 ͖ͨͭͪ 2020/04ʙ ϑϦʔϥϯεWebΤϯδχΞ 👨💻 https://kannade.jp @ttskch

/32 2021/05/29 #phpcon_okinawa @ttskch 4 PHPͰCSVͷΠϯϙʔτ/ 
 ΤΫεϙʔτʹཱͪ޲͔͏

/32 • ࣮͸Excel͸BOM෇͖UTF-8ͷCSVΛ։͚ΔͷͰSJISͰग़ྗ͢Δඞཁ͸ͳ͍ • league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫΛ঺հ • Πϯϙʔτ/ΤΫεϙʔτʹಛԽͨ͠ttskch/bulkonyΛ࢖͑͹ 
 ΋ͬͱ؆୯/ॊೈʹ࣮૷Ͱ͖Δ 5 ✅͜ͷτʔΫͷཁ໿ 10෼͔͠ͳ͍ͷͰαϥοͱͰ͢🙏

/32 6 Excel͸BOM෇͖UTF-8ͷ CSVΛ։͚Δ

/32 7 ҙ֎ͱ஌ΒΕ͍ͯͳ͍ࣄ࣮ https://ja.wikipedia.org/wiki/UTF-8#όΠτॱϚʔΫͷ࢖༻ Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ ٯʹݴ͏ͱɺBOM෇͖Ͱ͋Ε͹UTF-8Ͱ΋ී௨ʹ։͚Δ😳

/32 $fp = fopen('/path/to/output.csv', 'w+');ʊ fwrite($fp, "\xEF\xBB\xBF"); // ϑΝΠϧͷઌ಄ʹBOMΛૠೖ foreach ($data as $row) {ʊ fputcsv($fp, $row);ʊ }ʊ fclose($fp); 8 PHPͰग़ྗ͢Δྫ https://ja.wikipedia.org/wiki/όΠτॱϚʔΫ ϑΝΠϧͷઌ಄ʹ ”EF BB BF” ͱ͍͏όΠφϦΛ෇Ճ͢Δ͚ͩ👌 Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ

/32 $fp = fopen('/path/to/output.csv', 'w+');ʊ fwrite($fp, "\xEF\xBB\xBF"); // ϑΝΠϧͷઌ಄ʹBOMΛૠೖ foreach ($data as $row) {ʊ fputcsv($fp, $row);ʊ }ʊ fclose($fp); 9 PHPͰग़ྗ͢Δྫ https://ja.wikipedia.org/wiki/όΠτॱϚʔΫ ϑΝΠϧͷઌ಄ʹ ”EF BB BF” ͱ͍͏όΠφϦΛ෇Ճ͢Δ͚ͩ👌 Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ

/32 $fp = fopen('/path/to/output.csv', 'w+');ʊ fwrite($fp, "\xEF\xBB\xBF"); // ϑΝΠϧͷઌ಄ʹBOMΛૠೖ foreach ($data as $row) {ʊ fputcsv($fp, $row);ʊ }ʊ fclose($fp); 10 PHPͰग़ྗ͢Δྫ https://ja.wikipedia.org/wiki/όΠτॱϚʔΫ ϑΝΠϧͷઌ಄ʹ ”EF BB BF” ͱ͍͏όΠφϦΛ෇Ճ͢Δ͚ͩ👌 Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ

/32 • ExcelͰ։͍ͯ΋Β͏ͨΊ͚ͩʹSJISͰग़ྗ͢Δඞཁ͸ͳ͍ • UTF-8͔ΒSJISʹม׵͢ΔͱSJISʹଘࡏ͠ͳ͍จࣈ͕ ? ʹͳΔ໰୊ • SJIS͔ΒͷΠϯϙʔτ͸΋ͬͱ໽հͳͷͰɺ 
 ΞϓϦ͕ు͍ͨCSVΛΠϯϙʔτʹ΋࢖͏৔߹͸ಛʹBOM෇͖UTF-8Ұ୒ 11 ✅ BOM෇͖UTF-8Λੵۃతʹ࢖͓͏ Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ ※ SJISͷCSVΛ҆શʹѻ͓͏ͱ͢Δͱߟྀ͢Δ͜ͱ͕৭ʑ͋Γ·͢ʢεϥΠυͷ࠷ޙͰࢀߟURLΛ͝঺հ͠·͢ʣ

/32 12 league/csvΛ࢖ͬͨ Πϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

/32 • ۙ೥Α͘࢖ΘΕ͍ͯΔɺCSVΛ؆୯ʹѻ͏ͨΊͷPHPϥΠϒϥϦ • fgetcsvͱ͔Λ࢖ͬͯࣗྗͰ௿ϨΠϠʔͷίʔυΛॻ͔ͳͯ͘΋ɺ 
 CSVΛ͍͍ײ͡ʹந৅Խͯ͘͠ΕΔ 13 league/csvͱ͸ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

/32 $csv = Reader::createFromPath('/path/to/input.csv');ʊ $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ $rows = $csv->getRecords();ʊ foreach ($rows as $row) {ʊ // ࣮ࡍͷΠϯϙʔτॲཧ } 14 Πϯϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

/32 $csv = Reader::createFromPath('/path/to/input.csv');ʊ $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ $rows = $csv->getRecords();ʊ foreach ($rows as $row) {ʊ // ࣮ࡍͷΠϯϙʔτॲཧ } 15 Πϯϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ શσʔλͷ഑ྻͰ͸ͳ͘ 1ߦͣͭऔಘͰ͖ΔΠςϨʔλͳͷͰ লϝϞϦ

/32 $csv = Reader::createFromPath('/path/to/input.csv');ʊ $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ $rows = $csv->getRecords();ʊ foreach ($rows as $row) {ʊ // ࣮ࡍͷΠϯϙʔτॲཧ } 16 Πϯϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ ϔομʔߦͷҐஔΛએݴͨ͠ͷͰ [ 'ྻ໊' => '஋', 'ྻ໊' => '஋', : ] ͱ͍͏ܗࣜͷ഑ྻσʔλ͕ಘΒΕΔ ͜ΕΛ࢖ͬͯΠϯϙʔτॲཧΛॻ͘

/32 $csv = Writer::createFromPath('php://temp');ʊ $csv->insertOne(['ྻ1', 'ྻ2', 'ྻ3']); // Ұߦૠೖ $csv->insertAll($data); // ೋ࣍ݩ഑ྻͰෳ਺ߦ·ͱΊͯૠೖ // ग़ྗ࣌ʹBOMΛ෇Ճ͢ΔΑ͏ઃఆ $csv->setOutputBOM("\xEF\xBB\xBF");ʊ $csv->output('μ΢ϯϩʔυ.csv'); 17 ΤΫεϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

/32 $csv = Writer::createFromPath('php://temp');ʊ $csv->insertOne(['ྻ1', 'ྻ2', 'ྻ3']); // Ұߦૠೖ $csv->insertAll($data); // ೋ࣍ݩ഑ྻͰෳ਺ߦ·ͱΊͯૠೖ // ग़ྗ࣌ʹBOMΛ෇Ճ͢ΔΑ͏ઃఆ $csv->setOutputBOM("\xEF\xBB\xBF");ʊ $csv->output('μ΢ϯϩʔυ.csv'); 18 ΤΫεϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ ࢦఆͨ͠ϑΝΠϧ໊Ͱ_ μ΢ϯϩʔυͤ͞ΔͨΊͷ HTTPϨεϙϯεΛૹग़

/32 • ಛʹΠϯϙʔτ͸ɺ • ೖྗσʔλͷόϦσʔγϣϯ • Πϯϙʔτ࣮ߦલʹը໘ͰϓϨϏϡʔ ͳͲ͕ඞཁʹͳΔͱ్୺ʹ΍Δ͜ͱ͕૿͑ͯͻͨ͢Βେม😓 19 Ͱ΋ɺ·ͩ·ͩࣗલͰ࣮૷͢΂͖͜ͱ͕ଟ͍… league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

/32 20

/32 21 Ή͠Ζ͜͜Λָ͍ͨ͠…😓

/32 22 ttskch/bulkony

/32 • PHPΞϓϦʹΠϯϙʔτ/ΤΫεϙʔτػೳΛ࣮૷͢Δࡍʹ 
 ͍ͭ΋ॻ͍͍ͯͨ͋Ε͜ΕΛϥΠϒϥϦʹ੾Γग़͠·ͨ͠ • ⚡BOM෇͖UTF-8ͰͷΤΫεϙʔτ • ⚡ϝϞϦޮ཰ʹ഑ྀͨ͠Πϯϙʔτ/ΤΫεϙʔτͷ౔୆Λఏڙ • ⚡Πϯϙʔτ಺༰ͷόϦσʔγϣϯͷ౔୆Λఏڙ • ⚡Πϯϙʔτ಺༰ͷϓϨϏϡʔػೳͷ౔୆Λఏڙ 23 ttskch/bulkonyͱ͸ ttskch/bulkony

/32 use Ttskch\Bulkony\Export\Exporter;ʊ $exporter = new Exporter();ʊ $rowGenerator = new App\RowGenerator(); // ࣗ࡞Ϋϥε // ϑΝΠϧγεςϜʹग़ྗ $exporter->export('/path/to/output.csv', $rowGenerator);ʊ // HTTPϨεϙϯεΛૹग़ $exporter->exportAndOutput('users.csv', $rowGenerator); 24 ར༻ΠϝʔδʢΤΫεϙʔτʣ ttskch/bulkony

/32 use Ttskch\Bulkony\Export\Exporter;ʊ $exporter = new Exporter();ʊ $rowGenerator = new App\RowGenerator(); // ࣗ࡞Ϋϥε // ϑΝΠϧγεςϜʹग़ྗ $exporter->export('/path/to/output.csv', $rowGenerator);ʊ // HTTPϨεϙϯεΛૹग़ $exporter->exportAndOutput('users.csv', $rowGenerator); 25 ར༻ΠϝʔδʢΤΫεϙʔτʣ ttskch/bulkony ͜ͷΫϥεʹCSVߦͷੜ੒खॱΛॻ͘👨💻 ※ ৄࡉ͸READMEΛ͝ࢀর͍ͩ͘͞🙏

/32 use Ttskch\Bulkony\Import\Importer;ʊ $importer = new Importer();ʊ $rowVisitor = new App\RowVisitor(); // ࣗ࡞Ϋϥε // CSVͷͲͷηϧʹόϦσʔγϣϯΤϥʔ͕͋Δ͔ɺ // ΠϯϙʔτʹΑͬͯͲ͜ͷ஋͕ߋ৽͞ΕΔ͔ɺͷ৘ใ͕ಘΒΕΔ $preview = $importer->preview('/path/to/input.csv', $rowVisitor);ʊ // ͦΕΛը໘ʹදࣔ͢Ε͹ϓϨϏϡʔػೳ׬੒ render('some.template', ['preview' => $preview]); 26 ར༻ΠϝʔδʢΠϯϙʔτʣ ttskch/bulkony

/32 use Ttskch\Bulkony\Import\Importer;ʊ $importer = new Importer();ʊ $rowVisitor = new App\RowVisitor(); // ࣗ࡞Ϋϥε // CSVͷͲͷηϧʹόϦσʔγϣϯΤϥʔ͕͋Δ͔ɺ // ΠϯϙʔτʹΑͬͯͲ͜ͷ஋͕ߋ৽͞ΕΔ͔ɺͷ৘ใ͕ಘΒΕΔ $preview = $importer->preview('/path/to/input.csv', $rowVisitor);ʊ // ͦΕΛը໘ʹදࣔ͢Ε͹ϓϨϏϡʔػೳ׬੒ render('some.template', ['preview' => $preview]); 27 ར༻ΠϝʔδʢΠϯϙʔτʣ ttskch/bulkony ͜ͷΫϥεʹߦ͝ͱͷΠϯϙʔτ΍ όϦσʔγϣϯͷϩδοΫΛॻ͘👨💻 ※ ৄࡉ͸READMEΛ͝ࢀর͍ͩ͘͞🙏

/32 28 ΑΖ͚͠Ε͹࢖ͬͯΈ͍ͯͩ͘͞😇 https://github.com/ttskch/bulkony

/32 29 ·ͱΊ

/32 • ࣮͸Excel͸BOM෇͖UTF-8ͷCSVΛ։͚ΔͷͰSJISͰग़ྗ͢Δඞཁ͸ͳ͍ • league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫΛ঺հ • Πϯϙʔτ/ΤΫεϙʔτʹಛԽͨ͠ttskch/bulkonyΛ࢖͑͹ 
 ΋ͬͱ؆୯/ॊೈʹ࣮૷Ͱ͖Δ 30 ✅͜ͷτʔΫͷཁ໿

/32 • SJISͷCSVΛจࣈԽ͚ͤͣʹಡΈॻ͖͢Δํ๏ʹ͍ͭͯ͸ɺ 
 PHPerKaigi 2021ͷए༿ষ͞Μͷηογϣϯ͕େมࢀߟʹͳΓ·͢ • https://youtu.be/pc6ov8HWt1A 31 📝ؔ࿈͢Δ͓͢͢Ί৘ใ

/32 32 @ttskch ʘThanks!ʗ