sql - 连接文本文件并将它们导入 SQLite DB

Question

假设我有数千个逗号分隔的文本文件，每个文件有 1050 列(无标题)。有没有办法将所有文本文件连接并导入到一个表中，一个 SQLite 中的数据库(理想情况下，我会使用 R 和 sqldf 与 SQlite 进行通信)。

IE。，

每个文件被调用，table1.txt，table2.txt，table3.txt；所有不同的行数，但相同的列类型，以及 IDs 列(每个文件的第一列)中的不同唯一 ID。

table1.txt
id1,20.3,1.2,3.4
id10,2.1,5.2,9.3
id21,20.5,1.2,8.4
table2.txt
id2,20.3,1.2,3.4
id92,2.1,5.2,9.3
table3.txt
id3,1.3,2.2,5.4
id30,9.1,4.4,9.3

实际示例几乎相同，但列和行更多。如您所见，每个文件中的第一列对应一个唯一 ID。

现在我希望我在 super 表中的新表，在数据库中，super.db 是(也是唯一索引的):

super.db - name of the DB
mysupertable - name of the table in the DB
myids,v1,v2,v3
id1,20.3,1.2,3.4
id10,2.1,5.2,9.3
id21,20.5,1.2,8.4
id2,20.3,1.2,3.4
id92,2.1,5.2,9.3
id3,1.3,2.2,5.4
id30,9.1,4.4,9.3

作为引用，我使用的是 SQLite3；我正在寻找一个可以在后台运行而无需交互式登录到 sqlite3 解释器的 SQL 命令，即 IMPORT bla INTO，...

我可以在 unix 中尝试:

cat *.txt > allmyfiles.txt

然后是一个 .sql 文件，

CREATE TABLE test (myids varchar(255), v1 float, v2 float, v3 float);
.separator ,
.import output.csv test

但是这个命令不起作用，因为我使用的是 R sqldf library , 和 dbGetQuery(db, sql)而且我不知道如何在 R 中创建这样的字符串而不会出错。

p.s.我问了一个类似的 Q 从数据库中追加表，但这次我需要追加/导入文本文件而不是数据库中的表。

Answer 1

如果您仍然使用 sqlite 数据库文件，您可能需要考虑使用 RSQLite。

install.packages( "RSQLite" )        # will install package "DBI"
library( RSQLite )
db <- dbConnect( dbDriver("SQLite"), dbname = "super.db" )

你仍然可以在 R 中使用 unix 命令，它应该比 R 中的任何循环都快，使用 system()命令:

system( "cat *.txt > allmyfiles.txt" )

前提是您的 allmyfiles.txt具有一致的格式，您可以将其作为 data.frame 导入 R

allMyFiles <- read.table( "allmyfiles.txt", header = FALSE, sep = "," )

并按照@Martín Bel 的建议将其写入您的数据库，例如

dbWriteTable( db, "mysupertable", allMyFiles, overwrite = TRUE, append = FALSE )

编辑:

或者，如果您不想通过 R 路由数据，您可以再次使用 system()命令。这可能会让你开始:

您有一个文件，其中包含您想要进入 SQLite 的数据，名为 allmyfiles.txt。 .创建一个名为 table.sql 的文件与此内容(显然结构必须匹配):

CREATE TABLE mysupertable (myids varchar(255), v1 float, v2 float, v3 float);
.separator ,
.import allmyfiles.txt mysupertable

并从 R 中调用它

system( "sqlite3 super.db < table.sql" )

这应该避免通过 R 路由数据，但仍然在 R 内完成所有工作。