skysan87/csv-loader.js

## csv-loader.js
async function openFilePicker () {
  const pickerOpts = {
    types: [
      {
        accept: {
          'text/*': ['.csv', '.tsv']
        }
      },
    ],
    excludeAcceptAllOption: true,
    multiple: false
  }

  const [fileHandle] = await showOpenFilePicker(pickerOpts)
  const file = await fileHandle.getFile()

  const extension = file.name.split('.').pop()
  const parseType = {
    'csv': ',',
    'tsv': '\t'
  }
  return {
    text: toText(await file.arrayBuffer()),
    splitter: parseType[extension]
  }
}

function toText (arrayBuffer) {
  const unit8Array = new Uint8Array(arrayBuffer)
  const detectedEncoding = Encoding.detect(unit8Array)

  console.log('File Encoding: ', detectedEncoding)

  if (detectedEncoding === 'UTF8') {
    Encoding.codeToString(unit8Array)
  }

  const unicodeArray = Encoding.convert(unit8Array, { from: detectedEncoding, to: 'UNICODE' })
  return Encoding.codeToString(unicodeArray)
}

## csv-sql.html
<!DOCTYPE html>
<html lang="ja">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/encoding-japanese/2.0.0/encoding.min.js"></script>
  <script src="./csv-loader.js"></script>
  <!-- document: https://sql.js.org/documentation/ -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.min.js"></script>
  <script>
    class DB {
      static instance = null

      static async getInstance () {
        if (!DB.instance) {
          await DB.init()
        }
        return DB.instance
      }

      static async init () {
        const SQL = await initSqlJs({
          // .wasm
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${file}`
        })
        DB.instance = new SQL.Database()
      }
    }
  </script>
  <title>CSV to SQlite</title>
</head>

<body>
  <div>
    <button onclick="loadCsv()">CSV読込</button>
  </div>
  <fieldset>
    <legend>SQL実行</legend>
    <p>
      <button onclick="execQuery()">クエリ実行</button>
      <span>テーブル名: <span id="tablename"></span></span>
    </p>
    <textarea id="query" style="resize: none; width: 100%;" rows="3" placeholder="SELECT文に対応"></textarea>
    <textarea id="status" style="resize: none; width: 100%; color: white; background-color: cadetblue;" rows="5"
      readonly></textarea>
  </fieldset>

  <fieldset>
    <legend>実行結果(CSV)</legend>
    <textarea id="result" style="resize: none; width: 100%;" rows="20" readonly></textarea>
  </fieldset>

</body>


<script>
  const TABLE = 'tmp_data'

  window.addEventListener('load', async () => {
    document.getElementById('tablename').innerText = TABLE
    try {
      document.querySelectorAll('button').forEach(el => el.disabled = true)
      await DB.init()
      setStatus('DB initialized')
    } catch (errror) {
      setStatus(errror)
    } finally {
      document.querySelectorAll('button').forEach(el => el.disabled = false)
    }
  })

  async function loadCsv () {
    try {
      setStatus()
      document.querySelectorAll('button').forEach(el => el.disabled = true)
      // csvファイルを読込
      const { text, splitter } = await openFilePicker()

      const rows = []
      // TODO: パフォーマンス検証
      text.split('\n')
        .forEach(row => {
          if (row !== '') {
            rows.push(row.split(splitter).map(col => col.replace(/^["']|["']$/g, "")))
          }
        })
      const header = rows.shift()

      const db = await DB.getInstance()
      db.run(`DROP TABLE IF EXISTS ${TABLE}; CREATE TABLE IF NOT EXISTS ${TABLE}(${header.map(h => `${h} TEXT`).join(',')});`)
        .run(`INSERT INTO ${TABLE}(${header.join(',')}) VALUES ${rows.map(r => `(${r.map(r => `'${r}'`).join(',')})`).join(',')};`)

      setStatus('CSV取込完了')
    } catch (error) {
      setStatus(error)
    } finally {
      document.querySelectorAll('button').forEach(el => el.disabled = false)
    }
  }

  async function execQuery () {
    try {
      // reset
      setStatus()
      output()

      const db = await DB.getInstance()
      const query = document.getElementById('query')?.value ?? ''
      if (query === '') return

      // 最初のクエリのみ実行
      const [realQuery] = query.split(';', 1)

      // 入力パラメータチェック
      // 禁止コマンド
      const isNotAllowedCmd = realQuery.toUpperCase().split(' ').includes('INSERT', 'UPDATE', 'DELETE', 'DROP')
      // ワイルドカード付
      const isNotAllowedCmdWithWc = /^.*(insert|delete|update|drop)\*.*$/i.test(realQuery)
      if (isNotAllowedCmd || isNotAllowedCmdWithWc) {
        throw new Error('SELECT文のみ実行可能')
      }

      const result = db.exec(realQuery)
      // データなし
      if (!result) return

      output(generateResult(result))
    } catch (error) {
      setStatus(error)
    }
  }

  function setStatus (text = '') {
    document.getElementById('status').value = text
  }

  function generateResult (data) {
    if (!data) return ''
    // 最初のクエリの結果のみ表示する
    const { columns, values } = data[0]
    return [columns.join(','), ...values.map(v => v.join(','))].join('\n')
  }

  function output (text = '') {
    document.getElementById('result').value = text
  }
</script>

</html>
	async function openFilePicker () {
	const pickerOpts = {
	types: [
	{
	accept: {
	'text/*': ['.csv', '.tsv']
	}
	},
	],
	excludeAcceptAllOption: true,
	multiple: false
	}

	const [fileHandle] = await showOpenFilePicker(pickerOpts)
	const file = await fileHandle.getFile()

	const extension = file.name.split('.').pop()
	const parseType = {
	'csv': ',',
	'tsv': '\t'
	}
	return {
	text: toText(await file.arrayBuffer()),
	splitter: parseType[extension]
	}
	}

	function toText (arrayBuffer) {
	const unit8Array = new Uint8Array(arrayBuffer)
	const detectedEncoding = Encoding.detect(unit8Array)

	console.log('File Encoding: ', detectedEncoding)

	if (detectedEncoding === 'UTF8') {
	Encoding.codeToString(unit8Array)
	}

	const unicodeArray = Encoding.convert(unit8Array, { from: detectedEncoding, to: 'UNICODE' })
	return Encoding.codeToString(unicodeArray)
	}
	<!DOCTYPE html>
	<html lang="ja">

	<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<script src="https://cdnjs.cloudflare.com/ajax/libs/encoding-japanese/2.0.0/encoding.min.js"></script>
	<script src="./csv-loader.js"></script>
	<!-- document: https://sql.js.org/documentation/ -->
	<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.min.js"></script>
	<script>
	class DB {
	static instance = null

	static async getInstance () {
	if (!DB.instance) {
	await DB.init()
	}
	return DB.instance
	}

	static async init () {
	const SQL = await initSqlJs({
	// .wasm
	locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${file}`
	})
	DB.instance = new SQL.Database()
	}
	}
	</script>
	<title>CSV to SQlite</title>
	</head>

	<body>
	<div>
	<button onclick="loadCsv()">CSV読込</button>
	</div>
	<fieldset>
	<legend>SQL実行</legend>
	<p>
	<button onclick="execQuery()">クエリ実行</button>
	<span>テーブル名: <span id="tablename"></span></span>
	</p>
	<textarea id="query" style="resize: none; width: 100%;" rows="3" placeholder="SELECT文に対応"></textarea>
	<textarea id="status" style="resize: none; width: 100%; color: white; background-color: cadetblue;" rows="5"
	readonly></textarea>
	</fieldset>

	<fieldset>
	<legend>実行結果(CSV)</legend>
	<textarea id="result" style="resize: none; width: 100%;" rows="20" readonly></textarea>
	</fieldset>

	</body>


	<script>
	const TABLE = 'tmp_data'

	window.addEventListener('load', async () => {
	document.getElementById('tablename').innerText = TABLE
	try {
	document.querySelectorAll('button').forEach(el => el.disabled = true)
	await DB.init()
	setStatus('DB initialized')
	} catch (errror) {
	setStatus(errror)
	} finally {
	document.querySelectorAll('button').forEach(el => el.disabled = false)
	}
	})

	async function loadCsv () {
	try {
	setStatus()
	document.querySelectorAll('button').forEach(el => el.disabled = true)
	// csvファイルを読込
	const { text, splitter } = await openFilePicker()

	const rows = []
	// TODO: パフォーマンス検証
	text.split('\n')
	.forEach(row => {
	if (row !== '') {
	rows.push(row.split(splitter).map(col => col.replace(/^["']\|["']$/g, "")))
	}
	})
	const header = rows.shift()

	const db = await DB.getInstance()
	db.run(`DROP TABLE IF EXISTS ${TABLE}; CREATE TABLE IF NOT EXISTS ${TABLE}(${header.map(h => `${h} TEXT`).join(',')});`)
	.run(`INSERT INTO ${TABLE}(${header.join(',')}) VALUES ${rows.map(r => `(${r.map(r => `'${r}'`).join(',')})`).join(',')};`)

	setStatus('CSV取込完了')
	} catch (error) {
	setStatus(error)
	} finally {
	document.querySelectorAll('button').forEach(el => el.disabled = false)
	}
	}

	async function execQuery () {
	try {
	// reset
	setStatus()
	output()

	const db = await DB.getInstance()
	const query = document.getElementById('query')?.value ?? ''
	if (query === '') return

	// 最初のクエリのみ実行
	const [realQuery] = query.split(';', 1)

	// 入力パラメータチェック
	// 禁止コマンド
	const isNotAllowedCmd = realQuery.toUpperCase().split(' ').includes('INSERT', 'UPDATE', 'DELETE', 'DROP')
	// ワイルドカード付
	const isNotAllowedCmdWithWc = /^.(insert\|delete\|update\|drop)\.*$/i.test(realQuery)
	if (isNotAllowedCmd \|\| isNotAllowedCmdWithWc) {
	throw new Error('SELECT文のみ実行可能')
	}

	const result = db.exec(realQuery)
	// データなし
	if (!result) return

	output(generateResult(result))
	} catch (error) {
	setStatus(error)
	}
	}

	function setStatus (text = '') {
	document.getElementById('status').value = text
	}

	function generateResult (data) {
	if (!data) return ''
	// 最初のクエリの結果のみ表示する
	const { columns, values } = data[0]
	return [columns.join(','), ...values.map(v => v.join(','))].join('\n')
	}

	function output (text = '') {
	document.getElementById('result').value = text
	}
	</script>

	</html>