はじめに
国土数値情報ダウンロードサイトから地理空間情報データをダウンロードした場合、属性フィールドには実際の値ではなくコードが格納されており、外部参照によってコードリストと実際の値を紐付る形式になっているものが多くあります。
行政区域データの場合、全国地方公共団体コードが N03_007 フィールドに格納されています。そして属性の型として コードリスト「行政区域コード」のExcelファイルが定義されています。このファイルには行政区域コードに対応する都道府県名、市町村名などが格納されています。
DuckDB でシェープファイルを読み書きしてみるの記事では行政区域データを読み込むことが出来たので、これにコードリストを使って市町村名などのフィールドの結合にトライしてみます。
DuckDB での Excelの読み込み
DuckDBのドキュメントExcel Importに読み込み方法が記載されています。
read_xlsx()関数のパラメータでは、ファイルの指定、シートの指定、セル範囲の指定、ヘッダーの有無、型の扱い方の指定が出来るようです。
データの確認
ファイルAdminiBoundary_CD.xlsx をまずは普通にExcelアプリで開いて内容を確認します。
上から2行目まではタイトルと空行なので読み取り不要のようです。
3行目にカラム名がありますが、セルのテキスト内に改行が含まれているようです…大丈夫かな？
値の含まれていないNULLのセルもたくさんあります。
とりあえず read_xlsx() で読んでみる
まずは札幌市に関するセルの範囲を指定("A3:E:15")して読み込んでみます。
D SELECT * FROM read_xlsx("AdminiBoundary_CD.xlsx", range = "A3:E15");
Invalid Input Error:
read_xlsx: Failed to parse cell 'C5': Could not convert string '札幌市' to DOUBLE
C4セルが NULL のため、C5セルの文字列を DOUBLEとして読もうとしてエラーになるようです。
セルの型を指定する
値をDOUBLEではなく全てVARCHARとして扱うように all_varchar = trueをパラメータとして追加します。
D SELECT * FROM read_xlsx("AdminiBoundary_CD.xlsx", range = "A3:E15", all_varchar = true);
┌────────────────┬──────────────────────┬─────────────────────────────────┬────────────────────────────────────┬──────────────────────────────────────┐
│ 行政区域コード │ 都道府県名\n（漢字… │ 市区町村名\n（漢字）シクチョ… │ 都道府県名\n（ｶﾅ）トドウフケンメイ │ 市区町村名\n（ｶﾅ）シクチョウソンメイ │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├────────────────┼──────────────────────┼─────────────────────────────────┼────────────────────────────────────┼──────────────────────────────────────┤
│ 01584 │ ﾄｳｷｮｳﾄ │ NULL │ ﾎｯｶｲﾄﾞｳ │ NULL │
│ 01585 │ 北海道 │ 札幌市 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼ │
│ 01586 │ 北海道 │ 札幌市中央区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼｻｷｮｳｸ │
│ 01601 │ 北海道 │ 札幌市北区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾅｶｷﾞｮｳｸ │
│ 01602 │ 北海道 │ 札幌市東区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾋｶﾞｼﾔﾏｸ │
│ 01603 │ 北海道 │ 札幌市白石区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼｼﾓｷﾞｮｳｸ │
│ 01604 │ 北海道 │ 札幌市豊平区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾐﾅﾐｸ │
│ 01605 │ 北海道 │ 札幌市南区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼｳｷｮｳｸ │
│ 01606 │ 北海道 │ 札幌市西区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾌｼﾐｸ │
│ 01607 │ 北海道 │ 札幌市厚別区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾔﾏｼﾅｸ │
│ 01608 │ 北海道 │ 札幌市手稲区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾆｼｷｮｳｸ │
│ 01609 │ 北海道 │ 札幌市清田区 │ ﾎｯｶｲﾄﾞｳ │ ｵｵｻｶｼﾐﾔｺｼﾞﾏｸ │
├────────────────┴──────────────────────┴─────────────────────────────────┴────────────────────────────────────┴──────────────────────────────────────┤
│ 12 rows 5 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
無事に読み込めまし…た…と思ったら、行政区域コードの値が Excelに格納されている値と異なっています（泣）
都道府県名（漢字）に"ﾄｳｷｮｳﾄ"やら、市区町村名()に"ｷｮｳﾄｼｻｷｮｳｸ"などの値も入っています（号泣）
これではちょっと使い物になりませんね…
ひとまずテーブルsapporo_admin_code を作って格納しておきます。
D CREATE TABLE sapporo_admin_code AS SELECT * FROM read_xlsx("AdminiBoundary_CD.xlsx", range = "A3:E15", all_varchar = true);
カラムを選択して値を取り出してみる
カラム"市区町村名（ｶﾅ）"の値を取り出してみます。カラム名はコピペして入れちゃいます。
D SELECT "市区町村名\n（ｶﾅ）シクチョウソンメイ" FROM sapporo_admin_code;
Binder Error:
Referenced column "市区町村名\n（ｶﾅ）シクチョウソンメイ" not found in FROM clause!
Candidate bindings: "市区町村名
（ｶﾅ）シクチョウソンメイ", "市区町村名
（漢字）シクチョウソンメイカンジ", "都道府県名
（ｶﾅ）トドウフケンメイ", "都道府県名
（漢字）トドウフケンメイカンジ", "行政区域コード"
LINE 1: SELECT "市区町村名\n（ｶﾅ）シクチョウソンメイ" FROM sapporo_admin_code;
カラム名の中の文字('\n')が改行として認識されていないようです。これでは値が取り出せませんね…ムムム
仕方ないので Excel内のカラム名は無視して値部分のセルだけ読み込むことにします。パラメータに header = falseを追加します。
D CREATE OR REPLACE TABLE sapporo_admin_code AS SELECT * FROM read_xlsx("AdminiBoundary_CD.xlsx", range = "A4:E15", all_varchar = true, header = false);
D SELECT * FROM sapporo_admin_code;
┌─────────┬─────────┬──────────────┬─────────┬──────────────┐
│ A │ B │ C │ D │ E │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────┼─────────┼──────────────┼─────────┼──────────────┤
│ 01584 │ ﾄｳｷｮｳﾄ │ NULL │ ﾎｯｶｲﾄﾞｳ │ NULL │
│ 01585 │ 北海道 │ 札幌市 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼ │
│ 01586 │ 北海道 │ 札幌市中央区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼｻｷｮｳｸ │
│ 01601 │ 北海道 │ 札幌市北区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾅｶｷﾞｮｳｸ │
│ 01602 │ 北海道 │ 札幌市東区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾋｶﾞｼﾔﾏｸ │
│ 01603 │ 北海道 │ 札幌市白石区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼｼﾓｷﾞｮｳｸ │
│ 01604 │ 北海道 │ 札幌市豊平区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾐﾅﾐｸ │
│ 01605 │ 北海道 │ 札幌市南区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼｳｷｮｳｸ │
│ 01606 │ 北海道 │ 札幌市西区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾌｼﾐｸ │
│ 01607 │ 北海道 │ 札幌市厚別区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾔﾏｼﾅｸ │
│ 01608 │ 北海道 │ 札幌市手稲区 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾄｼﾆｼｷｮｳｸ │
│ 01609 │ 北海道 │ 札幌市清田区 │ ﾎｯｶｲﾄﾞｳ │ ｵｵｻｶｼﾐﾔｺｼﾞﾏｸ │
├─────────┴─────────┴──────────────┴─────────┴──────────────┤
│ 12 rows 5 columns │
└───────────────────────────────────────────────────────────┘
カラム名はABCDEとして読み込まれました。
たくさん行を読んでみる
札幌市だけでなく北海道全体のレコードを読み込んでみます。
D SELECT * FROM read_xlsx("AdminiBoundary_CD.xlsx", range = "A4:E237", all_varchar = True, header = False);
INTERNAL Error:
Attempted to access index 9394 within vector of size 9239
Stack Trace:
0 _ZN6duckdb9ExceptionC2ENS_13ExceptionTypeERKNSt3__112basic_stringIcNS2_11char_traitsIcEENS2_9allocatorIcEEEE + 64
1 _ZN6duckdb17InternalExceptionC1ERKNSt3__112basic_stringIcNS1_11char_traitsIcEENS1_9allocatorIcEEEE + 20
2 _ZN6duckdb17InternalExceptionC1IJyyEEERKNSt3__112basic_stringIcNS2_11char_traitsIcEENS2_9allocatorIcEEEEDpT_ + 140
3 _ZNK6duckdb6vectorINS_8string_tELb1EEixEm + 164
4 _ZN6duckdb11SheetParser6OnCellERKNS_11XLSXCellPosENS_12XLSXCellTypeERNS_6vectorIcLb1EEEy + 632
5 _ZN6duckdb15SheetParserBase12OnEndElementEPKc + 252
6 doContent + 1020
7 contentProcessor + 64
8 XML_ParseBuffer + 432
9 _ZN6duckdb9XMLParser5ParseEPKcyb + 64
10 _ZN6duckdbL7ExecuteERNS_13ClientContextERNS_18TableFunctionInputERNS_9DataChunkE + 336
11 duckdb::PhysicalTableScan::GetData(duckdb::ExecutionContext&, duckdb::DataChunk&, duckdb::OperatorSourceInput&) const + 80
12 duckdb::PipelineExecutor::FetchFromSource(duckdb::DataChunk&) + 124
13 duckdb::PipelineExecutor::Execute(unsigned long long) + 236
14 duckdb::PipelineTask::ExecuteTask(duckdb::TaskExecutionMode) + 236
15 duckdb::ExecutorTask::Execute(duckdb::TaskExecutionMode) + 160
16 duckdb::Executor::ExecuteTask(bool) + 252
17 duckdb::ClientContext::ExecuteTaskInternal(duckdb::ClientContextLock&, duckdb::BaseQueryResult&, bool) + 64
18 duckdb::PendingQueryResult::ExecuteInternal(duckdb::ClientContextLock&) + 60
19 duckdb::PendingQueryResult::Execute() + 56
20 duckdb_shell_sqlite3_print_duckbox + 368
21 duckdb_shell::ShellState::ExecutePreparedStatement(sqlite3_stmt*) + 964
22 duckdb_shell::ShellState::ExecuteSQL(char const*, char**) + 452
23 duckdb_shell::ShellState::RunOneSqlLine(char*) + 104
24 duckdb_shell::ShellState::ProcessInput() + 916
25 main + 3140
26 start + 2840
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors
エラーが発生して読み込ませんでした…
nightly build をインストールしてみる
GitHubリポジトリで Issue を読んでいたところバグフィックスされているらしい記述がありました。
正式リリースではありませんが Nightly build版をインストールしてみます。
FORCE INSTALL excel FROM core_nightly;
LOAD excel;
値が化けていた読み込みをもう一度試してみます。
E200", all_varchar = true);
正しい値が読み込まれました！
D SELECT * FROM read_xlsx("AdminiBoundary_CD.xlsx", range = "A4:E100", all_varchar = True, header = False);
┌─────────┬─────────┬──────────────┬─────────┬──────────────┐
│ A │ B │ C │ D │ E │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────┼─────────┼──────────────┼─────────┼──────────────┤
│ 01000 │ 北海道 │ NULL │ ﾎｯｶｲﾄﾞｳ │ NULL │
│ 01100 │ 北海道 │ 札幌市 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼ │
│ 01101 │ 北海道 │ 札幌市中央区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ │
│ 01102 │ 北海道 │ 札幌市北区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼｷﾀｸ │
│ 01103 │ 北海道 │ 札幌市東区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼﾋｶﾞｼｸ │
│ 01104 │ 北海道 │ 札幌市白石区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼｼﾛｲｼｸ │
│ 01105 │ 北海道 │ 札幌市豊平区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼﾄﾖﾋﾗｸ │
│ 01106 │ 北海道 │ 札幌市南区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼﾐﾅﾐｸ │
│ 01107 │ 北海道 │ 札幌市西区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼﾆｼｸ │
│ 01108 │ 北海道 │ 札幌市厚別区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼｱﾂﾍﾞﾂｸ │
│ 01109 │ 北海道 │ 札幌市手稲区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼﾃｲﾈｸ │
│ 01110 │ 北海道 │ 札幌市清田区 │ ﾎｯｶｲﾄﾞｳ │ ｻｯﾎﾟﾛｼｷﾖﾀｸ │
│ 01202 │ 北海道 │ 函館市 │ ﾎｯｶｲﾄﾞｳ │ ﾊｺﾀﾞﾃｼ │
│ 01203 │ 北海道 │ 小樽市 │ ﾎｯｶｲﾄﾞｳ │ ｵﾀﾙｼ │
│ 01204 │ 北海道 │ 旭川市 │ ﾎｯｶｲﾄﾞｳ │ ｱｻﾋｶﾜｼ │
│ 01205 │ 北海道 │ 室蘭市 │ ﾎｯｶｲﾄﾞｳ │ ﾑﾛﾗﾝｼ │
│ 01206 │ 北海道 │ 釧路市 │ ﾎｯｶｲﾄﾞｳ │ ｸｼﾛｼ │
│ 01207 │ 北海道 │ 帯広市 │ ﾎｯｶｲﾄﾞｳ │ ｵﾋﾞﾋﾛｼ │
│ 01208 │ 北海道 │ 北見市 │ ﾎｯｶｲﾄﾞｳ │ ｷﾀﾐｼ │
│ 01209 │ 北海道 │ 夕張市 │ ﾎｯｶｲﾄﾞｳ │ ﾕｳﾊﾞﾘｼ │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │
│ 01395 │ 北海道 │ ニセコ町 │ ﾎｯｶｲﾄﾞｳ │ ﾆｾｺﾁｮｳ │
│ 01396 │ 北海道 │ 真狩村 │ ﾎｯｶｲﾄﾞｳ │ ﾏｯｶﾘﾑﾗ │
│ 01397 │ 北海道 │ 留寿都村 │ ﾎｯｶｲﾄﾞｳ │ ﾙｽﾂﾑﾗ │
│ 01398 │ 北海道 │ 喜茂別町 │ ﾎｯｶｲﾄﾞｳ │ ｷﾓﾍﾞﾂﾁｮｳ │
│ 01399 │ 北海道 │ 京極町 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳｺﾞｸﾁｮｳ │
│ 01400 │ 北海道 │ 倶知安町 │ ﾎｯｶｲﾄﾞｳ │ ｸｯﾁｬﾝﾁｮｳ │
│ 01401 │ 北海道 │ 共和町 │ ﾎｯｶｲﾄﾞｳ │ ｷｮｳﾜﾁｮｳ │
│ 01402 │ 北海道 │ 岩内町 │ ﾎｯｶｲﾄﾞｳ │ ｲﾜﾅｲﾁｮｳ │
│ 01403 │ 北海道 │ 泊村 │ ﾎｯｶｲﾄﾞｳ │ ﾄﾏﾘﾑﾗ │
│ 01404 │ 北海道 │ 神恵内村 │ ﾎｯｶｲﾄﾞｳ │ ｶﾓｴﾅｲﾑﾗ │
│ 01405 │ 北海道 │ 積丹町 │ ﾎｯｶｲﾄﾞｳ │ ｼｬｺﾀﾝﾁｮｳ │
│ 01406 │ 北海道 │ 古平町 │ ﾎｯｶｲﾄﾞｳ │ ﾌﾙﾋﾞﾗﾁｮｳ │
│ 01407 │ 北海道 │ 仁木町 │ ﾎｯｶｲﾄﾞｳ │ ﾆｷﾁｮｳ │
│ 01408 │ 北海道 │ 余市町 │ ﾎｯｶｲﾄﾞｳ │ ﾖｲﾁﾁｮｳ │
│ 01409 │ 北海道 │ 赤井川村 │ ﾎｯｶｲﾄﾞｳ │ ｱｶｲｶﾞﾜﾑﾗ │
│ 01421 │ 北海道 │ 北村 │ ﾎｯｶｲﾄﾞｳ │ ｷﾀﾑﾗ │
│ 01422 │ 北海道 │ 栗沢町 │ ﾎｯｶｲﾄﾞｳ │ ｸﾘｻﾜﾁｮｳ │
│ 01423 │ 北海道 │ 南幌町 │ ﾎｯｶｲﾄﾞｳ │ ﾅﾝﾎﾟﾛﾁｮｳ │
│ 01424 │ 北海道 │ 奈井江町 │ ﾎｯｶｲﾄﾞｳ │ ﾅｲｴﾁｮｳ │
│ 01425 │ 北海道 │ 上砂川町 │ ﾎｯｶｲﾄﾞｳ │ ｶﾐｽﾅｶﾞﾜﾁｮｳ │
├─────────┴─────────┴──────────────┴─────────┴──────────────┤
│ 97 rows (40 shown) 5 columns │
└───────────────────────────────────────────────────────────┘
さいごに
read_xlsx()関数を使ってExcelファイルを読み込むのはまだまだハードルがあるようですね…
今のところ、いったんCSVファイルに保存してそちらを読むしか回避策が思いつきません。無念
nightly build 版では不具合が解消されていることが確認できました。正式リリースが待たれます。