MS Excel でUTF-8 文字コードの CSVファイルを開く 文字化け回避 / SUBSTITUTE 増殖問題

それを回避するにはCSVファイルの先頭に BOM をつけるだけです。

解決方法はこれ: https://stackoverflow.com/questions/60873673/how-to-add-a-utf-8-bom-in-kotlin

具体例

たとえばポケモンのCSVを生成する例。

// pokemon.main.kts

@file:Repository("https://repo1.maven.org/maven2/")
@file:DependsOn("com.opencsv:opencsv:5.5")

import java.io.*
import com.opencsv.CSVReader
import com.opencsv.CSVWriter

typealias Cell = String
data class Row(val cells: List<Cell>)
data class Table(val rows: List<Row>)

val createPokemonTable: ()->Table = {
    val row1 = Row("type,pokemon1,pokemon2,pokemon3".split(","))
    val row2 = Row("normal,Eevee,Pidgeot,Jigglypuff".split(","))
    val row3 = Row("electric,Pikachu,Raichu,Voltorb".split(","))
    val row4 = Row("water,Squirtle,Wartortle,Psyduck".split(","))
    val row5 = Row("fire,Charmander,Charmeleon,Ponyta".split(","))

    Table(listOf(row1,row2,row3,row4,row5))
}

val UTF_8 = Charsets.UTF_8

val tmpFile = File("pokemon_without_bom.csv")
val csvFile = File("pokemon_with_bom.csv")

val csvWriter = CSVWriter(OutputStreamWriter(FileOutputStream(tmpFile), UTF_8))

createPokemonTable().rows.forEach { row->
    csvWriter.writeNext( row.cells.toTypedArray() )
}

csvWriter.close()

// ここで BOM を追加:
val source = tmpFile.readText(UTF_8)
csvFile.writeText("\uFEFF" + source, UTF_8)

実行。

$ kotlin pokemon.main.kts

CSVファイルが意図通り生成できているか確認。

$ cat pokemon_without_bom.csv
"type","pokemon1","pokemon2","pokemon3"
"normal","Eevee","Pidgeot","Jigglypuff"
"electric","Pikachu","Raichu","Voltorb"
"water","Squirtle","Wartortle","Psyduck"
"fire","Charmander","Charmeleon","Ponyta"

この BOM なしの pokemon_without_bom.csv を MS Excel で開いても文字化けします。 代わりに BOM つきにした pokemon_with_bom.csv を開けば、文字化けなしにCSVファイルを開くことができます。

SUBSTITUTE 増殖問題（未解決）

A1 にある文字列を SUBSTITUTE 関数(MS Excel関数)を何回も適用してnormalizeしたい場合。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"／",""),"/",""),",",""),"，",""),"％","%")," ",""),"ｰ","-"),"−","-"),"㎎","mg")

読み辛い上に置き換えしたいものを追加したり削除したりするのが苦痛すぎる。

これを Kotlin で書けば次のような処理です。

val normalize: (String)->String = { name->
    name.replace("／", "")
        .replace("/","")
        .replace(",","")
        .replace("，","")
        .replace("％", "%")
        .replace(" ","")
        .replace("ｰ","-")
        .replace("−","-")
        .replace("㎎","mg")
}

これも大差はない。 そこで、置換リストを作成して、それを fold すれば少しはましになる。

val normalize: (String)->String = { name->
    val list = listOf(
        Pair("／", ""),
        Pair("/",""),
        Pair(",",""),
        Pair("，",""),
        Pair("％", "%"),
        Pair(" ",""),
        Pair("ｰ","-"),
        Pair("−","-"),
        Pair("㎎","mg"))

    list.fold(name) { acc, pair->
        acc.replace(pair.first, pair.second)
    }
}

このようなコードが MS Excel で使えればよいのですが。

fold に相当する MS Excel 関数はないのか？と思ってしらべたところ reduce が使えるようです。

• https://support.microsoft.com/en-us/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb

ただし Microsoft 365 のみ。手元にある MS Excel では使えなかった。

そもそもこのようなケースで意図通り使えるのかも不明だが。

Syntax:

=REDUCE([initial_value], array, lambda(accumulator, value, body))

array の部分がセルの範囲を指定する例しか見つからなかったので、 このケースがそのまま使えるようには思えなかった。 置換情報をセルに書けばいいのかもしれないけれど。

以上です。