1个小时终于让我搭建这个的任务很无聊,我没有这个任务,一个客户端递给我一张1800个产品行的电子表格,让我在第二天早上演示之前加载到他们的暂存数据库中,没有API,没有导入屏幕,只是一个CSV和一个MySQL表等着它,我做了大家第一次做的事情:我打开文件,手写几个INSERT语句,把形状弄对了,开始复制值在三行之后一个产品名称,里面有一个撇号, O'Brien's Tools,打破了整个陈述,因为单引号提前收线,其余的mysql哽咽,那就是你从电子表格中意识到手写sql不是五分钟工作的那一刻,它是一个引号雷区,有1800次踩错的机会,所以我构建了 CSV 到 SQL 转换器并且本指南是其中每个选项背后的推理。
TL;博士: CSV 到 SQL 转换器读取 CSV 文件并生成
CREATE TABLE並INSERTstatements 一个数据库需要存储它,Toolz 工具用 RFC 4180 状态机解析文件,推断每一列是整数、十进制、布尔值还是文本,引用 MySQL、PostgreSQL 或 SQLite 的标识符,正确转义每个值,并将空单元格变为NULL、粘贴CSV,挑选你的方言,复制一个可以马上跑的脚本,一切都运行客户端:不上传,不注册,离线工作。
Laravel 和 React 上构建 SaaS 产品,并运送 WordPress 插件,因此将表格数据加载到数据库中是每周的杂务,而不是一次性的。有时它是客户端导出,有时是新鲜环境的种子文件,有时是测试的快速固定装置。CSV 始终是它到达的格式,SQL 始终是它需要结束的地方。用手从一个到另一个正是浏览器工具应该删除的那种重复的、容易出错的工作,所以这是我希望当那个撇号破坏我的导入时我所拥有的指南。
什么是 CSV 到 SQL 转换器?
CSV到SQL转换器,取出一行逗号分隔的值,并生成创建表并将数据加载到其中的SQL语句,CSV本身只是松散的标准化- RFC 4180 describes the common form,并明确它记录了现有的实践而不是口述它,它将第一行读作列名,将后面的每一行视为记录,并发出两件事:a CREATE TABLE 定义具有合理类型的列和一组的语句 INSERT 携带值的语句。输出是一个普通的 SQL 脚本,您可以粘贴到 TablePlus 或 DBeaver 等数据库客户端中,掉入迁移或管道到 mysql、 psql、或者 sqlite3 命令行一步构建和填充表。
Category 存在的原因是翻译是用容易被低估的方式摆弄的,每个文本值都要用单个引号包裹,一个值内部的每个单引号都要加倍,这样就不会终止字符串,数字和布尔值必须不被引用,否则数据库会将它们存储为文本和你的 WHERE price > 100 will行为怪异,空细胞通常需要变成 NULL 而不是空字符串。带有空格或保留单词的列名称必须用适合您的数据库的字符引用。在几千行中错过这些中的任何一个,整个批次都会失败,通常会出现指向错误行的错误。转换器每次都以相同的方式应用所有这些规则,这就是整个值命题。
器如何决定每一列's类型?
Zhe是把有用的转换器与哑巴的转换器分开的部分,Yi个天真的工具,让每一列 TEXT 并称之为"完成",这在技术上有效,但会为您提供一个表,其中数字排序为字符串,如果不进行转换,您就无法进行算术。工具 CSV 到 SQL 工具 相反,扫描列中的每个值并选择适合所有值的最窄类型。
Logic一看就直白,如果一列中每一个非空值都是整数,那一列就变成整数型,如果每一个值都是数字但有些有小数点,那就变成浮动型,如果每一个值都是字 true 或 false的,它变成了布尔值,其他任何东西,包括混合内容和任何带有字母的东西,都回落到文本中,有一个故意的例外,它以一种好的方式抓住了人们:一个像这样的值 007 或 00123 stays text,而不是整数,因为前导零几乎总是意味着该列是一个ID,一个邮政编码,或者一个电话号码,其中零是重要的,如果存储为数字,就会丢失。那个单一的规则使我免于默默损坏多个产品SKU列。
如果您宁愿每一列都是文本,您可以关闭推理,这是您计划在加载后自行更改类型时的安全选择。但对于常见情况,让工具推断类型意味着生成 CREATE TABLE 匹配数据而不是将其压平,并且该表在存在时就可用。
SQL方言应该选择哪种?
SQL是标准 英语拼写是标准的方式,也就是说每个数据库都有自己的口音,你挑的方言在输出中改变了三件事:标识符是如何引用的,类型名称叫什么,真假是如何写的,下面是四个选项的不同之处。
| 忧 | 我的SQL | PostgreSQL | SQLite | 标准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 会拒绝 PostgreSQL 想要的双引号标识符,除非您处于 ANSI 模式,并且 PostgreSQL 没有 TINYINT.SQLite 根本没有真正的布尔类型,所以布尔值变成整数,这就是工具写入的原因 1 並 0 对于 MySQL 和 SQLite 但是 TRUE 並 FALSE postgresql和标准sql来说,如果你不确定或者你在写一些便携的东西,标准sql是最保守的选择,一旦生成语句你总能通过the运行 SQL 格式化程序 在输出进入迁移文件之前对其进行漂亮打印。
如何使用该工具将 CSV 转换为 SQL?
流故意短。将您的 CSV 粘贴到输入框中,或单击"加载样本"以查看带有 id、名称、角色、布尔值和薪资列的工作示例。
将表名设置为目标表应调用的任何内容,然后选择您的 SQL 方言。然后决定输出选项。如果表尚不存在,请留下 "包含 CREATE TABLE" on,或将其关闭以仅生成 INSERT 当您加载到已经存在的表中时,请保留 "为键入的表推断列类型 " 打开,或关闭以使所有内容都文本。选择 "多行 INSERT "对于一个具有许多值元组的紧凑语句,加载速度最快,或将其关闭以获得一个 INSERT per row,这对于版本控制来说更加友好,可以让你单独运行行。将"空单元格留为NULL" on,除非你特别想要存储空字符串。
delimiter 可以自动检测或手动设置 自动检测得分逗号、分号、制表符和管道 根据每个分号如何一致地将前几行分割成相同数量的列,这正确地处理了欧洲分号文件和制表符分隔的导出 单击转换为 SQL 输出显示行数和列数,加上任何有关重复列名或粗糙行的警告 复制它并运行 如果导出到达时没有头行,请关闭 "第一行是 header",工具命名列 column_1、 column_2依此类推,然后将每一行视为数据。
为什么正确的价值逃逸如此重要?
因为错误的转义不仅仅是一个错误,它是一类安全漏洞。用手破坏我的第一次导入的撇号与 SQL 注入背后的机制相同:值内部的单个引用,如果没有转义,则提前结束字符串,并让后面的所有内容解释为 SQL。在应用程序中,您通过参数化查询来解决此问题,其中数据库驱动程序将值和代码严格分开。当您从 CSV 生成静态 SQL 脚本时,您没有这种分离,因此转义必须在生成的文本本身中正确。
该工具遵循 ANSI SQL 规则:文本值用单引号包裹,值内的任何单引号都加倍。所以 O'Brien's Tools 变成 'O''Brien''s Tools'的,它支持的数据库中的每一个都读回为原始字符串,数字和布尔值在没有引号的情况下被发出,因此它们被存储为正确的类型,标识符被引用为方言'自己的字符,因此有一个列称为 order 或 select does not collide with a reserved word, 这正是人类在时间压力下错了,工具每次都对的那种繁琐的正确,这正是自动化它的全部意义,如果你很好奇的是逃逸是如何跨格式比较的, the CSV 到 JSON 转换器 JSON方面面临同样的问题,逃逸字符是反斜杠而不是双引号。
CSV敏感数据在线转换安全吗?
对于这个工具,是的,原因是架构而不是页面的承诺。每一步,解析 CSV、推断列类型、转义值和构建语句,在您自己的浏览器选项卡内以 JavaScript 形式运行。没有上传,没有服务器往返,也没有记录或存储任何内容。您可以通过打开浏览器's 网络选项卡并单击"转换:没有请求离开页面"。一旦页面加载,您可以完全断开与互联网的连接,并且它会不断生成 SQL。
这很重要,因为人们转换为 SQL 的 CSV 通常是企业拥有的最敏感的文件。客户表、订单历史记录、用户记录和定价数据在落入数据库之前都以 CSV 的身份传输。将您的文件上传到服务器的转换器,无论多么善意,都会将私有电子表格变成其他人's 日志条目。客户端处理完全消除了问题:数据永远不会离开它启动的机器。该默认值是故意的遍及 toolz。dev 上的每个工具,我在其中写下了较长的参数 在线工具的数据隐私指南 对于任何想要完整推理的人。
CSV 到 SQL 在真正的工作流程中适合何处?
转换很少是整个作业,它是一个管道中的一个站。我最常点击的模式是播种:客户端发送电子表格,我将其转换为 SQL 脚本,我运行该脚本来填充分期或开发数据库,因此应用程序有现实的数据可以对抗。由于输出是纯脚本而不是实时连接,因此很容易将 repo 作为种子文件检查,在拉取请求中进行审查,并在任何环境中重新运行。
它周围的工具取决于数据下一步在做什么。如果我在转换之前需要将文件作为可排序网格进行观察,则 CSV 查看器 renders相同的rfc 4180解析为表,这样我就可以发现一个格式错误的行,然后它变成格式错误的 INSERT。如果目的地是API或配置文件而不是数据库,则 CSV 到 JSON 转换器 相反,这是正确的跳跃,也是它的兄弟姐妹 json to csv 当我需要将数据作为电子表格交回时,请处理相反的问题。一旦 SQL 存在, SQL 格式化程序 tidies它变成一个可读的东西进行迁移, 这些都没有上传你的数据, 所以你可以把它们链在同一个私人文件上, 而无需再考虑, 如果你正在为这种数据工作组装一个套件, 我的 web开发人员工具包指南 浏览各个部分的连接方式。
有哪些值得了解的极限?
对极限的诚实是信任工具的一部分。转换器生成标准 CREATE TABLE 並 INSERT statements,这意味着它不会推断主键、外键、索引或列约束,因为这些信息都不存在于一个扁平的csv中,它为每一列选择一个合理的类型,但是 VARCHAR(255) mysql文本是默认的,而不是测量;如果你有一列长描述,你可能想将其拓宽为 TEXT 加载后,如果您有一列应该如此 DATE 或 DATETIME 您需要更改它,因为该工具将日期视为文本以避免猜错格式。
的多行 INSERT 是紧凑而快速的,但是非常大的文件会产生一个非常长的语句,并且有些数据库会限制一个语句的大小或占位符的数量,如果加载了数万行并达到限制,则切换到一个 INSERT per row,它交易一个更大的脚本来用于一个数据库将永远接受的语句 最后,工具是用于生成加载脚本,而不是用于流式传输一个多千兆字节的文件,因为整个输入都生活在浏览器内存中,对于日常的导出,种子文件和固定装置,这是大多数人实际拥有的,这些限制都没有咬,知道它们存在只是很好地使用工具和惊讶它的区别。
常见问题
如何将 CSV 文件转换为 SQL?
将 CSV 粘贴到 CSV 到 SQL 转换器、设置表名,选择你的SQL方言,它把头行读作列名,推断出每种列类型,生成一个 CREATE TABLE 加加 INSERT 语句可以在数据库中复制和运行。一切都发生在浏览器中,因此文件永远不会上传。
SQL数据库支持哪些?
MySQL、PostgreSQL、SQLite、标准SQL的语句,转换器输出,你挑选的方言控制标识符引用和类型和布尔语法,所以脚本运行在那个数据库中,没有编辑,MySQL使用回溯和 TINYINT(1) booleans,而 PostgreSQL 和标准 SQL 使用双引号和 TRUE 或 FALSE。
它如何决定列类型?
每列都根据数据中的每个值进行检查,如果所有非空值都是整数,则该列成为整数类型,所有数字成为十进制类型,所有真值或假值成为布尔类型,其他任何值都成为文本值,前导为零,例如 007 stays text,因为它通常是一个重要的标识符,你可以关闭推理,使每一列文本。
它创建表还是仅创建插入?
默认情况下两者都是。它发出a CREATE TABLE 带有推断列类型的语句,后跟 INSERT 语句。你可以转动 CREATE TABLE 当表已经存在并且您只需要加载行时关闭。
报价和特殊字符是如何处理的?
文本值用单引号包裹,值内的任何单引号都加倍,这是标准的 SQL 转义,因此 O'Brien 变成 'O''Brien'。数字和布尔值没有被引用,标识符用 MySQL 的回溯引用或其他方言的双引号引用,因此保留了类似的单词 order 不打破声明。
空细胞会发生什么?
默认情况下,空单元格变为 NULL的,这通常是您想要的缺失值,如果您更喜欢存储空字符串,请关闭空即 NULL 选项,空单元格将成为两个单引号。
我可以转换没有头行的 CSV 吗?
是关闭头选项,工具会命名列 column_1、 column_2(以此类推),然后将每一行都视为数据,这对于没有头行发货的原始出口非常有用。
我的 CSV 文件是否上传到任何地方? No。Parsing和SQL生成在你的浏览器中以JavaScript运行。没有传输,记录或存储任何内容,并且该工具一旦加载就保持离线工作,您可以在转换时通过查看网络选项卡来确认。
