跳过导入CSV文件到Sqlite时的某些列

Question

假设您有以下CSV中的数据：

请注意，CSV表示逗号分隔值，是一种常见的文件格式，通常用于存储表格数据。

Name, Age, Gender
Jake, 40, M
Bill, 17, M
Suzie, 21, F

在导入上述CSV时，是否可以排除Age变量？我的当前方法是简单地使用cut shell命令。

更新

iluvcapra有一个很好的解决方案适用于小型CSV文件。然而，对于非常大的CSV文件，这种方法效率低下。例如，假设上述CSV非常大，比如30Gb。仅为了立即删除所有年龄数据而加载它们是一种浪费时间。考虑到这一点，有没有更有效的方法将列的子集加载到sqlite数据库中？

我认为最好的选择是使用shell命令cut来削减不必要的列。那个直觉正确吗？在预处理CSV文件以更适合sqlite的版本时，通常使用shell命令吗？

- Jacob H

Answer 1

创建一个带有age列的临时表，然后使用INSERT...SELECT将数据从临时表移动到主表中：

CREATE TEMP TABLE _csv_import (name text, age integer, gender text);
.separator ","
.import file.csv test

INSERT INTO names_genders (name, gender) SELECT name, gender
    FROM _csv_import WHERE 1;
DROP TABLE _csv_import;

编辑：将其更新为一个带有幻影年龄列的视图：

CREATE VIEW names_ages_genders AS 
    SELECT (name, 0 AS age ,gender) FROM names_genders;
CREATE TRIGGER lose_age
    INSTEAD OF INSERT ON names_ages_genders
    BEGIN
        INSERT INTO names_genders (name, gender) 
            VALUES (NEW.name, NEW.gender)
    END;

这将创建一个名为names_ages_genders的视图，该视图会显示每个人都是零岁，并会在任何调用它的INSERT语句中自动忽略年龄字段。未经测试！（我实际上不确定.import能否导入到视图中）。

- iluvcapra

---

谢谢！这对我的示例非常有用。然而，对于大型CSV文件（几个GB），这种方法效率相对较低。我不喜欢加载所有数据，然后立即删除一些数据的想法。 - Jacob H

"过早的优化是万恶之源。" 如果使用临时表，所有操作都将在 RAM 中进行。您还可以在导入表上创建一个 VIEW，该视图具有一个额外的列，该列不映射到 INSTEAD OF INSERT 钩子中的任何列。这样，您就可以使用单个 .import 将其导入，而不是插入到表中，而是插入到视图中。 - iluvcapra

cut也可以，但是cut和.import都不能正确处理CSV文件中的转义。我认为有一个sqlite模块可以将CSV文件映射到虚拟表格因人而异。 - iluvcapra

（添加视图/插入触发器以回答） - iluvcapra

我认为你的意思是说 .import file.csv _csv_import。 - Daniel

Answer 2

如果您希望避免在SQLite中阅读更多内容，并且希望避免使用标准文本处理工具（例如cut和awk）处理CSV文件的风险，则可以考虑使用您喜欢的csv2tsv转换器(*)，按照以下方式进行：

csv2tsv input.csv | cut -f 1,3- > tmp.tsv

cat << EOF | sqlite3 demo.db
drop table if exists demo;
.mode csv
.separator "\t"
.import tmp.tsv demo
EOF

/bin/rm tmp.tsv

请注意，如果input.csv中有文字制表符、换行符或转义的双引号，则上述操作是否能够产生所需效果将取决于使用的csv2tsv工具。

(*) csv2tsv

如果您没有适当的csv2tsv转换工具可供使用，这里有一个简单的python3脚本可以完成任务，处理CSV中嵌入的文字换行符、制表符和两个字符序列"\t"和"\n"。

#!/usr/bin/env python3

# Take care of embedded tabs and newlines in the CSV

import csv, re, sys

if len(sys.argv) > 2 or (len(sys.argv) > 1 and sys.argv[1] == '--help'):
    sys.exit("Usage: " + sys.argv[0] + " [input.csv [output.tsv]]")

csv.field_size_limit(sys.maxsize)

if len(sys.argv) == 3:
    out=open(sys.argv[2], 'w+')
else:
    out=sys.stdout

if len(sys.argv) == 1:
    csvfile=sys.stdin
else:
    csvfile=open(sys.argv[1])

# tabs and newlines ...
def edit(s):
  s=re.sub(r'\\t', r'\\\\t', s)
  s=re.sub(r'\\n', r'\\\\n', s)
  s=re.sub('\t', r'\\t', s)
  return re.sub('\n', r'\\n', s)

reader = csv.reader(csvfile, dialect='excel')
for row in reader:
    line=""
    for s in row:
       s=edit(s)
       if len(line) == 0:
          line = s
       else:
          line += '\t' + s
    print(line)

- peak