SQLite 3 CSV 导入到表
我将此作为开始的资源 - http://www.pantz.org /software/sqlite/sqlite_commands_and_general_usage.html
目前我正在使用内置的 SQLite 数据库创建一个 AIR 程序。在进行 SQL 查询方面，我可以说是一个十足的菜鸟。
表列类型
我有一个相当大的 Excel 文件（14K 行），我已将其导出到 CSV 文件。它有 65 列不同的数据类型（主要是整数、浮点数和短字符串，可能还有一些布尔值）。我不知道正确的导入形式以保留列结构，也不知道每个数据库列选择的最佳数据格式。我可以对此使用一些意见。
表创建实用程序
是否有一个实用程序可以读取 XLS 文件并根据列标题生成快速查询语句以减轻手动查询的痛苦？我看到了这篇文章，但它似乎面向预先存在的 CSV 文件，并使用了 python（我也是菜鸟）
提前感谢您的宝贵时间。 J
I am using this as a resource to get me started - http://www.pantz.org/software/sqlite/sqlite_commands_and_general_usage.html
Currently I am working on creating an AIR program making use of the built in SQLite database. I could be considered a complete noob in making SQL queries.
table column types
I have a rather large excel file (14K rows) that I have exported to a CSV file. It has 65 columns of varying data types (mostly ints, floats and short strings, MAYBE a few bools). I have no idea about the proper form of importing so as to preserve the column structure nor do I know the best data formats to choose per db column. I could use some input on this.
table creation utils
Is there a util that can read an XLS file and based on the column headers, generate a quick query statement to ease the pain of making the query manually? I saw this post but it seems geared towards a preexisting CSV file and makes use of python (something I am also a noob at)
Thank you in advance for your time.
J
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQLite3 的列类型基本上可以归结为：
通常在 CSV 文件中你会遇到字符串 (TEXT)、十进制数字 ( FLOAT) 和整数 (INT)。如果性能并不重要，那么这些几乎是您需要的唯一三种列类型。 （
CHAR(80)在磁盘上比TEXT小，但对于几千行来说，这并不是什么大问题。）就将数据放入列而言，SQLite3 使用类型强制将输入数据类型转换为列类型（只要转换有意义）。因此，您所要做的就是指定正确的列类型，SQLite 会以正确的方式存储它。
例如，数字
-1230.00、字符串"-1230.00"和字符串"-1.23e3"都将强制转换为数字 1230当存储在FLOAT列中时。请注意，如果 SQLite3 无法应用有意义的类型转换，它将仅存储原始数据，而根本不会尝试对其进行转换。 SQLite3 非常乐意将
"Hello World!"插入到FLOAT列中。这通常是一件坏事。请参阅 有关列类型和转换的 SQLite3 文档，了解 gem，例如：
SQLite3's column types basically boil down to:
Generally in a CSV file you will encounter strings (TEXT), decimal numbers (FLOAT), and integers (INT). If performance isn't critical, those are pretty much the only three column types you need. (
CHAR(80)is smaller on disk thanTEXTbut for a few thousand rows it's not so much of an issue.)As far as putting data into the columns is concerned, SQLite3 uses type coercion to convert the input data type to the column type whereever the conversion makes sense. So all you have to do is specify the correct column type, and SQLite will take care of storing it in the correct way.
For example the number
-1230.00, the string"-1230.00", and the string"-1.23e3"will all coerce to the number 1230 when stored in aFLOATcolumn.Note that if SQLite3 can't apply a meaningful type conversion, it will just store the original data without attempting to convert it at all. SQLite3 is quite happy to insert
"Hello World!"into aFLOATcolumn. This is usually a Bad Thing.See the SQLite3 documentation on column types and conversion for gems such as: