Excelã§UTF-8ã®CSVãæååããªãåã蟌ã
Microsoft Excelã§CSVãã¡ã€ã«ãéããšæååãããŠãŠãããããšããã·ãŒã³ã¯ãã¡ããã¡ããèŠãããŸããææ°ã®æåã®ã¯ã©ãŠãå€æ 管çã·ã¹ãã ã®ã¬ããŒãããŒã¿ãCSVã§ããŠã³ããŒãããŠéããããæååãã§èªããªããšããã·ãŒã³ãèªåã®å Žåæ¯åãããŸãïŒãµãã¹ã¯ã®ã¯ã©ãŠãã·ã¹ãã ã§ã³ã¬ã¯ã¡ãã£ãšé ·ããããšæããŸãïŒã
ããŠããã®Excelã®æååãåé¡ã§ãããçŸåšææ°ã®Microsoft365ã®Excelã§ã¯ããã€ãã®åé¿æ段ãšå¯Ÿå¿çããããŸããå€å žçãªãã¯ããã¯ãå«ããŠããŸãšããŠã¿ãŸããã
ç®æ¬¡
ä»åå©çšããããŒã«ç
- ã¡ã¢åž³
- Power Query
- Excel CSV I/O
ä»åç°¡åã«äœ¿ããããŒã«ã§ããã€ãã®UTF8ã®CSVãåã蟌ãã§ã¿ãããšæããŸãããµã³ãã«ããŒã¿ã¯ãå人æ å ±ãã¹ãããŒã¿ãžã§ãã¬ãŒã¿ãå©çšããŠçæããŠããŸãã
ãªããã®çŸè±¡ãèµ·ããŠããŸãã®ãïŒ
å²ããšé·å¹ŽèŠãããããŠãããã®UTF8ã®æåã³ãŒãã®CSVæååããã©ãã«ã§ãããæ§æ¥ãããã£ãShift-JIS圢åŒã®CSVã§ã¯ããã«ã¯ãªãã¯ã§éããŠãæååãããããšã¯ãããŸãããããããæè¿ã®åºå¹¹æ¥åã·ã¹ãã ããŠã§ããµãŒãã¹ã§åºåãããå€ãã®CSVã¯UTF8ã§ãšã³ã³ãŒããããCSVã§ãã
ãã®UTF8ã«ã¯2çš®é¡ãã£ãŠãBOMä»ããšBOMç¡ãããããBOM(Byte Order MarkïŒãšã¯ãããŒã¿ã®å é ã«å ¥ããã³ãŒãã§ãæåã³ãŒãã®èå¥ã®çºã«å©çšãããŠããã®ã§ãããå€ãã®ã¢ããªãBOMç¡ãã§ãèªåå€å®ããŠããããã®ãå€ãäžãExcelã¯æªã ã«ãBOMæããã§ãªããã°UTF8ãšå€å®ããªããšããä»æ§ã®çµæãã®ãããªãã©ãã«ãèµ·ããŠããŸããã€ãŸããUTF8ã ããæååãããã®ã§ã¯ãªããBOMç¡ãUTF8ã ããåé¡ãèµ·ããŠãã®ã§ãã
ã¯ã©ãŠããµãŒãã¹ããããŠããŒãããå€ãã®CSVããBOMç¡ãUTF8ãã§ããããšãå€ãããã®ãŸãŸããã«ã¯ãªãã¯ããŠæ¯åã®ããã«æååããèŠã矜ç®ã«ãªãããã§ãã
å³ïŒãããªã£ãŠããŸãã®ã«ã¯çç±ããã
察å¿ç
Power Queryã䜿ãäºäŸ
ããã«ã¯ãªãã¯ã§éãã®ã§ã¯ãªããExcelã®åã蟌ã¿æ©èœãå©çšããŠãBOMç¡ãUTF8ã®CSVãåã蟌ãäºãå¯èœã§ãã以äžã®æé ã§åã蟌ã¿ãŸãã
- ããŒã¿ã¿ããéãããããã¹ããŸãã¯CSVããããã¯ãªãã¯ãã
- UTF8ã®CSVãæå®ãã
- ãã€ã¢ãã°ãåºãããå ã®ãã¡ã€ã«ã®æå®ãã65001 : UTF-8ããæå®ãã
- èªã¿èŸŒã¿ã®é£ã®âŒãã¯ãªãã¯ããŠãèªã¿èŸŒã¿å ãéžã¶ãããŒã¿ã®å€æãã¯ãªãã¯ãããšPower Queryãèµ·åãã
- ããŒã¿ã®ã€ã³ããŒããã€ã¢ãã°ã§ã¯ãæ¢åã®ã¯ãŒã¯ã·ãŒããéžã³ãã»ã«ãã¯ãªãã¯ããŠãOKãã¯ãªãã¯ãã
- ã¯ãšãªãäœæãããŠãæå®ã®å Žæã«ããŒãã«ãšããŠåºåãããŸãã
CSVã®éäžã«æ¹è¡ããããšå¥ã¬ã³ãŒãã«ãªã£ãŠããŸãã®ã§ããã®ç¹ã¯èŠæ³šæã§ãã
å³ïŒå€éšããŒã¿åã蟌ã¿æ©èœã䜿ã
å³ïŒæåã³ãŒãæå®ãããšæååã解æ¶
ã¡ã¢åž³ã䜿ãäºäŸ
æããããå€å žçãªãã¯ããã¯ã§ãããªãã ããã çãã®ææ³ããã£ãŠãããããªãããšæããŸãããã®ææ³ã«ã¯2ãã¿ãŒã³ãããã©ã¡ãã§ãã£ãŠãåé¡ãªããExcelã§çŽæ¥éããŸãã
ANSIã«ããŠä¿åãçŽã
ã¡ã¢åž³ã§äžæŠCSVãã¡ã€ã«ãéããŠãååãä»ããŠä¿åãå®è¡ããéã«ãæåã³ãŒãããANSIãã«ããäºã§ãShift-JISã«å€æãããŠä¿åãããã®ã§ãExcelã§çŽæ¥éããããã«ãªãã
å³ïŒãªãã ããã Shift-JISããã¿ãŒ
BOMæãUTF8ãšããŠä¿åãçŽã
äžèšã®å ŽåãShift-JISãšãªããããå Žåã«ãã£ãŠã¯æ¬¡ã®ã·ã¹ãã ã§äœ¿ãå Žåã«å ·åãæªãããšããããŸãïŒUTF8ãããªããšåãä»ããªããšããã·ã¹ãã ããã£ããããŸãïŒããã®å Žåã«ã¯ååãä»ããŠä¿åããå Žåã«ã¯ããUTF-8 ïŒBOMä»ãïŒãã«ããããšã§ãUTF8ãç¶æãã€ã€ãBOMã®ä»ããCSVãšãªãã®ã§ãExcelã§ãã·ã¹ãã åŽã§ãå©çšãåºæ¥ãŸãã
â»äœããBOMä»ãã ãšæ£ããåäœããªãã·ã¹ãã ãšããã®ãååšããã®ã§ããã®èŸºã¯ããèŠæ¥µããŠé©çšããŸãããã
å³ïŒBOMä»ãã§ä¿åããã ãã§ã
Excel CSV I/Oã䜿ãäºäŸ
ã¢ãã€ã³ã®ã€ã³ã¹ããŒã«
Excel CSV I/Oãšããã¢ãã€ã³ãå°å ¥ããŠããäºã§ããã¬ãŒã³ããã¹ããšããŠåã蟌ãã®ã容æã«ãªããŸããå°å ¥æé ãšäœ¿ãæ¹ã¯ä»¥äžã®éãããŸãããã åã蟌ãã ããããªããExcelãåæã«è§£éããŠé ã®0ãåãé€ãããããªãããã«ã€ã³ããŒãããŠãããã¢ãã€ã³ããã®ããŒã«ã
- ãŠã§ããµã€ãããExcelCsvIO.zipãããŠã³ããŒãã解åããïŒããã¥ã¡ã³ããã©ã«ããªã©ã«è§£åããŸãããïŒ
- äžã«å ¥ã£ãŠãinstall.vbsãããã«ã¯ãªãã¯ããŠå®è¡ãã
- ã¢ãã€ã³ãã€ã³ã¹ããŒã«ããã
- Excelãèµ·åãããšã¡ãã¥ãŒã«CSV I/Oã衚瀺ãããããããªãå Žåã¯ä»¥äžã®æé
- éçºã¿ããéãã衚瀺ããŠããããã«ããŸãããã
- Excelã¢ãã€ã³ãã¯ãªãã¯ãã
- ãã€ã¢ãã°ãéããããåç §ãã¯ãªãã¯
- 2.ã®äžã«å ¥ã£ãŠããExcelCsvIO.xlamããéžæãã
- Excelcsvioã衚瀺ãããã§ãã¯ãããç¶æ ã«ãªããOKãã¯ãªãã¯ããŠéãã
å³ïŒæåã§ã€ã³ã¹ããŒã«ããæ¹æ³
ã¢ãã€ã³ã䜿ã£ãŠã¿ã
CSV I/Oã®ã¿ããéããŠãã以äžã®æé ã§åã蟌ã
- ã€ã³ããŒããã¯ãªãã¯ãã
- ã«ã³ããUTF-8ãCR+LFãéžæãããŠãç¶æ ã§ãã€ã³ããŒããã¯ãªãã¯ãã
- ãã¡ã€ã«ãéžæãã
- åã蟌ãŸãã
- éããã¯ãªãã¯ã§ããã€ã¢ãã°ãéãã
äœãã2.ã®æ¹è¡ã³ãŒãã®éšåã§ãCR+LFã ãšå šéšã1åã§åã蟌ãŸããŠããŸãã±ãŒã¹ãããã®ã§ããã®å ŽåLFãCRã«å€æŽãããšããŸãåã蟌ãŸããŸãïŒæåã³ãŒãã ããããªãæ¹è¡ã³ãŒããéãã±ãŒã¹ãããã®ã§CSVã¯æ¬åœã«é¢åèãïŒãããã«ãšã¯ã¹ããŒããå¯èœã§ããã®ã¢ãã€ã³ã¯å ¥ããŠãããšè²ã ãšæéãæžã£ãŠäŸ¿å©ã§ãã
å³ïŒã€ã³ããŒããã€ã¢ãã°
é¢é£ãªã³ã¯
- ç°¡åã«æååãåé¿ïŒUNICODE (UTF-8) ã® CSV ããŒã¿ã衚瀺ããæ¹æ³
- CSVãã¡ã€ã«ãæååããããã«ãšã¯ã»ã«ã§éãæ¹æ³ãContact Form 7ÃFlamingoã
- Excelã«å€§èŠæš¡ãªæ©èœè¿œå ïœMicrosoftã8æã®æŽæ°ãŸãšããçºè¡š