CSV から SQL へ: 見積もりバグなしでスプレッドシートを INSERT ステートメントに変換します

最終的にこれをビルドさせたタスクは退屈で、持っていなかった1 時間かかりました クライアントから1,800 の製品行のスプレッドシートを手渡され、翌朝のデモの前にステージングデータベースにロードするように頼まれました APIもインポート画面もなく、CSVとMySQLテーブルだけを待っています 私は誰もが初めて行うことをしました ファイルを開き、形状を正しくするために手書きでINSERTステートメントをいくつか書き、値をコピーし始めました 3 行後にアポストロフィが入った製品名、 O'Brien's Tools、全体のステートメントを破った、なぜならシングルクォートは文字列を早期に閉じ、MySQLは残りの部分にチョークした スプレッドシートからの手書きSQLが5 分間の仕事ではないことに気づいた瞬間、それは1,800 のチャンスが間違ったステップを引用地雷原を構築した CSVからSQLへのコンバーター、そしてこのガイドは、その中のすべてのオプションの背後にある推論です。

tl;dr: CSV から SQL へのコンバーターは CSV ファイルを読み取り、を生成します CREATE TABLE あんど INSERT データベースが保存する必要があるステートメント Toolzツールは、RFC 4180 ステートマシンでファイルを解析し、各列が整数、10 進数、ブール値、またはテキストであるかどうかを推測し、MySQL、PostgreSQL、またはSQLiteの識別子を引用し、すべての値を正しくエスケープし、空のセルを変換します NULL。 CSVを貼り付け、方言を選択し、すぐに実行できるスクリプトをコピーする。すべてがクライアント側で実行され、アップロードもサインアップも行われず、オフラインで動作する。

私はLaravelとReactでSaaS製品をビルドし、私はWordPressプラグインを出荷しているので、表形式のデータをデータベースにロードすることは、1 回限りのものではなく、毎週の雑用ですクライアントエクスポートである場合もあれば、新鮮な環境のためのシードファイルである場合もあれば、テストのためのクイックフィクスチャである場合もあります CSVは常にそれが到着するフォーマットであり、SQLは常にそれが終わる必要がある場所です 手で一方から他方へ手に入れることは、まさにブラウザツールが消去すべき反復的でエラーが発生しやすい作業の種類であるため、これは、そのアポストロフィが私のインポートを壊したときに私が持っていたいガイドです。

CSVからSQLへのコンバーターとは何ですか?

CSVからSQLへのコンバーターは、カンマ区切りの値の行を取得し、テーブルを作成してデータをロードするSQLステートメントを生成します。 CSV自体は緩やかに標準化されているだけです - RFC 4180 は、一般的な形式を記述し、既存の実践を口述するのではなく文書化することを明示している。 最初の行を列名として読み、続くすべての行を記録として扱い、次の2 つのことを発する: a CREATE TABLE 賢明な型を持つ列を定義するステートメントと、一連のステートメント INSERT 値を運ぶステートメント 出力は、TablePlus や DBeaver などのデータベース クライアントに貼り付けたり、マイグレーションにドロップしたり、パイプに挿入したりできるプレーンな SQL スクリプトです mysql、 psql、または sqlite3 コマンドラインでテーブルを構築し、ワンステップで入力します。

カテゴリが存在する理由は、翻訳が軽視しやすい方法で手間がかからないためです すべてのテキスト値をシングルクォートでラップする必要があります 値内のすべてのシングルクォートは、文字列を終了しないように2 倍にする必要があります 数字とブール値は引用されないままにする必要があります そうしないと、データベースはそれらをテキストとして保存し、あなたの WHERE price > 100 は奇妙な振る舞いをします。 空の細胞は通常になる必要があります NULL 空の文字列ではなく。 spaces や reserved words を持つ列名は、データベースに適した文字で引用する必要があります。 miss any one of these across a few thousand rows and the whole batch fails, often with an error that points at the wrong line.コンバータは、これらのルールのすべてを毎回同じ方法で適用します。これは、価値提案全体です。

ツールはどのように各column& #39; sタイプを決定しますか?

これは、便利なコンバータを愚かなコンバータから分離する部分です。 素朴なツールはすべての列を作ります TEXT そして、それは完了を呼び出します, これは技術的に動作しますが、数字が文字列としてソートされ、キャストせずに算術を行うことができないテーブルを与えます. The Toolz CSV to SQL ツール 代わりに、列内のすべての値をスキャンし、それらすべてに適合する最も狭いタイプを選択します。

一度見れば論理は単純です もし列の空でない値が全て整数なら列は整数型になります もし全ての値が数値で 小数点を持つものもあるなら浮動型になります もし全ての値が単語なら true や false、ブール値になります.他のものは,混合された内容や文字のあるものを含めて,テキストに戻ります.人を良い方法で引っ掛ける意図的な例外が1 つあります: のような値です. 007 や 00123 は整数ではなくテキストのままです。なぜなら、先頭にゼロがあると、ほとんどの場合、列が ID、郵便番号、または電話番号であることを意味し、ゼロが重要で、数値として保存すると失われる可能性があるためです。その 1 つのルールにより、複数の製品 SKU 列が静かに破損するのを防ぐことができました。

すべての列をテキストにしたい場合は、推論をオフにすることができます。これは、読み込み後に自分で型を変更する予定がある場合の安全な選択です。しかし、一般的なケースでは、ツールに型を推測させることは、生成されることを意味します CREATE TABLE データをフラット化するのではなく一致させ、テーブルは存在する瞬間に使用できます。

どのSQL方言を選ぶべきですか?

SQL は英語のスペルが標準であるのと同じように標準です。つまり、すべてのデータベースには独自のアクセントがあります。選択した方言は、出力内の 3 つの点を変更します。識別子の引用方法、型名の呼び方、true と false の書き方です。 4 つのオプションがどのように異なるかは次のとおりです。

懸念	mysql	PostgreSQL	SQLite を	Standard SQL
識別子の引用	バックティック `col`	二重引用符 "col"	二重引用符 "col"	二重引用符 "col"
整数型	INT	INTEGER	INTEGER	INTEGER
十進型	DOUBLE	DOUBLE PRECISION	REAL	REAL
ブール型	TINYINT(1)	BOOLEAN	INTEGER	BOOLEAN
テキストの種類	VARCHAR(255)	TEXT	TEXT	TEXT
ブール値	1 / 0	TRUE / FALSE	1 / 0	TRUE / FALSE

実用的なガイダンスは、差異が表面的なものではないため、方言を実際にロードしているデータベースに一致させることです。 MySQL は、ANSI モードでない限り、PostgreSQL が求める二重引用符で囲まれた識別子を拒否しますが、PostgreSQL には拒否されません TINYINT。 SQLiteは実ブール型が全くないため、ブール型は整数になるため、ツールは書き込みを行う 1 あんど 0 mysql と SQLite の両方の場合 TRUE あんど FALSE postgresql と標準 SQL の場合。確信が持てない場合、またはポータブルなものを書いている場合は、標準 SQL が最も保守的な選択です。ステートメントが生成されると、いつでもステートメントを実行できます SQL フォーマッタ 出力が移行ファイルに入る前に、出力をプリティプリントします。

ツールでCSVをSQLに変換するにはどうすればよいですか?

フローが意図的に短くなります。 「CSV」を入力ボックスに貼り付けるか、「サンプルの読み込み」をクリックして、id、名前、役割、ブール値、給与列を含む作業例を表示します。

テーブル名をターゲットテーブルを呼び出すべきものに設定し、SQL 方言を選択します。次に、出力オプションを決定します。 " CREATE TABLE" を残す テーブルがまだ存在しない場合はオンにするか、オフにしてテーブルのみを生成します INSERT すでに存在するテーブルに読み込むときのステートメント。 "infer column types" を入力したテーブルの場合はオンに、すべてをテキストにするにはオフにします。 "Multi-row INSERT" を選択します。多くの値タプルを備えた 1 つのコンパクトなステートメントの場合、最も速く読み込まれるか、オフにして 1 つを取得します INSERT 行ごとに、バージョン管理に優しく、行を個別に実行できます。 "空のセルを NULL" として残します。特に空の文字列を保存したい場合を除き、オンにします。

区切り文字は、自動検出または手で設定できます 自動検出 スコア カンマ、セミコロン、タブ、パイプ それぞれが最初の数行を同じ数の列にどの程度一貫して分割するかによって、ヨーロッパのセミコロンファイルとタブ区切りのエクスポートを正しく処理します。 「SQL に変換」をクリックすると、行と列のカウントとともに出力が表示され、さらに重複する列名またはぼろぼろの行に関する警告が表示されるので、それをコピーして実行します。 エクスポートがヘッダー行なしで到着した場合は、" をオフにします。 最初の行は header" で、ツールは列に名前を付けます column_1、 column_2、 などなどがあり、すべての行をデータとして扱います。

なぜ正しい価値逃避がこれほど重要なのでしょうか?

誤ったエスケープは単なるバグではなく、セキュリティ上の脆弱性のクラスであるためです。私の最初のインポートを手作業で壊したアポストロフィは、SQL インジェクションの背後にあるのと同じメカニズムです。値内の 1 つの引用符は、エスケープされない場合、文字列を早期に終了し、後続のものを SQL として解釈できるようにします。アプリケーションでは、パラメータ化されたクエリでこれを解決します。データベース ドライバーは値とコードを厳密に分離します。CSV から静的 SQL スクリプトを生成する場合、その分離がないため、生成されたテキスト自体ではエスケープが正しい必要があります。

ツールは ANSI SQL ルールに従います: テキスト値はシングルクォートでラップされ、値内の任意のシングルクォートは 2 倍になります。 と言うわけです O'Brien's Tools なる 'O''Brien''s Tools'、サポートされているデータベースのすべてが元の文字列として読み返します。 numbers と booleans は引用符なしで発行されるため、適切な型として保存され、識別子は dialect' s 自身の文字で引用されるため、と呼ばれる列 order や select 予約された単語と衝突しません これはまさに人間が時間の圧力の下で間違って取得し、ツールは、それを自動化の全体のポイントである、毎回正しく取得するような退屈な正しさです 脱出がフォーマット間で比較する方法に興味があるなら、 CSVからJSONへのコンバーター JSON 側からも同じ問題に直面しています。エスケープ文字は 2 倍の引用符ではなくバックスラッシュです。

機密性の高いCSVデータをオンラインで変換しても安全ですか?

このツールの場合、はい、その理由はページ上の約束ではなくアーキテクチャです。CSV の解析、列タイプの推論、値のエスケープ、ステートメントの構築は、すべてのステップで、独自のブラウザ タブ内で JavaScript として実行されます。アップロードもサーバー往復も行われず、ログも保存もされません。browser's ネットワーク タブを開いて「変換: リクエストなし」をクリックすると、それを証明できます。ページが読み込まれると、インターネットから完全に切断でき、SQL が生成され続けます。

それは重要人々がSQLに変換するCSVは、多くの場合、企業が持っている最も機密性の高いファイルである顧客テーブル、注文履歴、ユーザーレコード、および価格データはすべて、データベースに着地する前にCSVとしてサーバーにファイルをアップロードするコンバーターは、どんなに善意であっても、プライベートスプレッドシートを他の誰かに& #39; sログエントリにしますクライアント側の処理は質問を完全に削除します: データは起動したマシンから離れることはありませんそのデフォルトは、Toolz.dev上のすべてのツールにわたって意図的であり、私は長い引数をに書き出しました オンラインツール向けデータプライバシーガイド 完全な推論が必要な方へ.

実際のワークフローでは、CSV to SQL はどこに当てはまりますか?

変換がジョブ全体になることはほとんどなく、パイプライン内の 1 つのステーションです。私が最も頻繁にヒットするパターンはシードです。クライアントがスプレッドシートを送信し、それを SQL スクリプトに変換し、そのスクリプトを実行してステージングまたは開発データベースにデータを入力して、アプリが現実的に動作できるようにします。出力はライブ接続ではなくプレーン スクリプトであるため、シード ファイルとしてレポにチェックインし、プル リクエストでレビューし、どの環境でも再実行するのは簡単です。

その周りのツールは、データが次に何をしているかによって異なります。変換する前に、ファイルを並べ替え可能なグリッドとして観察する必要がある場合は、 CSV ビューア 同じ RFC 4180 をテーブルとして解析して、不正な行が不正な形式になる前に見つけることができるようにします INSERT.宛先がデータベースではなく API または config ファイルの場合、 CSVからJSONへのコンバーター 代わりに適切なホップであり、その兄弟です JSONからCSVへ スプレッドシートとしてデータを返さなければならない場合、その逆を処理します。そして、SQL が存在すると、 SQL フォーマッタ 移行のために読みやすいものに整理します これらのどれもデータをアップロードしないので、考え直さずに同じプライベートファイルにチェーンできます この種のデータ作業用のキットを組み立てている場合、私の Web 開発者ツールキット ガイド 駒がどのように接続されるかを説明します。

知っておく価値のある限界は何ですか?

制限について正直であることは、ツールを信頼することの一部です。コンバータは標準を生成します CREATE TABLE あんど INSERT ステートメントとは、主キー、外部キー、インデックス、または列の制約を推測しないことを意味します。なぜなら、その情報はいずれもフラット CSV には存在しないからです。各列に対して合理的な型を選択します。しかし VARCHAR(255) mysql のテキストはデフォルトであり、測定値ではありません。長い説明の列がある場合は、それを拡張するとよいでしょう TEXT ロード後、および列があるはずの場合は DATE や DATETIME このツールは形式を間違って推測しないように日付をテキストとして扱うため、変更する必要があります。

の マルチロー INSERT はコンパクトで高速ですが、非常に大きなファイルでは非常に長い単一のステートメントが生成され、一部のデータベースでは、1 つのステートメントのサイズまたはプレースホルダーの数が上限になります。数万行を読み込んで制限に達している場合は、1 つに切り替えます INSERT 行ごとに、より大きなスクリプトを取引して、データベースが常に受け入れるステートメントを作成します。最後に、このツールは、入力全体がブラウザのメモリに存在するため、マルチギガバイトのファイルをストリーミングするためではなく、ロード スクリプトを生成するためのものです。日常的なエクスポート、シード ファイル、フィクスチャの場合、これはほとんどの人が実際に持っているものですが、これらの制限のいずれも噛みつきません。それらが存在することを知ることは、ツールをうまく使用することと、それに驚かれることとの単なる違いです。

よくある質問

CSVファイルをSQLに変換するにはどうすればよいですか? CSVを に貼り付けます CSVからSQLへのコンバーター、テーブル名を設定し、SQL 方言を選択します。 header 行を列名として読み込み、各列タイプを推測し、を生成します CREATE TABLE プラス INSERT データベースでコピーして実行できるステートメント すべてはブラウザで発生するため、ファイルがアップロードされることはありません。

どのSQLデータベースをサポートしていますか? コンバータはMySQL、PostgreSQL、SQLite、標準SQLのステートメントを出力します 選択する方言は識別子の引用と型とブール構文を制御するため、スクリプトは編集なしでそのデータベースで実行されます。 MySQLはバックティックとを使用します TINYINT(1) booleans、postgresql と標準 SQL はダブルクォーテーションを使用します TRUE や FALSE。

列の種類はどのように決定されますか? 各列はデータ内のすべての値に対してチェックされます。空でない値がすべて整数の場合、列は整数型になり、すべての数値は 10 進数型になり、すべての真または偽の値はブール型になり、その他のものはテキストになります。 などの先頭がゼロである値 007 通常、重要な識別子であるため、テキストは維持されます。推論をオフにして、すべての列テキストを作成できます。

テーブルを作成しますか、それともインサートのみを作成しますか? デフォルトでは両方です。 a を発します CREATE TABLE 推論された列タイプの後に、 INSERT 発言。 を回すことができます CREATE TABLE テーブルがすでに存在し、行のみをロードする必要がある場合はオフになります。

引用や特殊文字はどのように扱われますか? テキスト値はシングル クォートでラップされ、値内のシングル クォートは 2 倍になります。これは標準の SQL エスケープです O'Brien なる 'O''Brien'。 数字とブール値は引用符で囲まれず、識別子は MySQL のバックティックまたは他の方言の二重引用符で引用されるため、次のような予約語になります order 声明を破らない.

空のセルはどうなりますか? デフォルトでは空のセルになります NULL、これは通常、欠損値に求めるものです。 empty-as-NULL オプションを代わりに格納することを好む場合は、empty-as-NULL オプションをオフにし、空のセルは 2 つのシングル クォーテーションになります。

ヘッダ行のないCSVを変換できますか? はい。 header オプションをオフにして、ツールが列に名前を付けます column_1、 column_2、 などと続けて、すべての行をデータとして扱う。 header 行なしで出荷する raw エクスポートに便利です。

CSVファイルはどこにでもアップロードされていますか? いいえ 解析とSQL生成はブラウザでJavaScriptとして実行されます 何も送信、ログ、保存されず、ツールはロードされるとオフラインで動作し続けます。変換中にネットワークタブを見て確認できます。