从csv导入时如何获取记录为NULL的缺失值 [英] How can I get missing values recorded as NULL when importing from csv

问题描述

我有多个大型csv文件，每个文件在许多地方都有缺失的值.当我将csv文件导入SQLite时，由于另一个应用程序希望缺少的数据由NULL表示，因此我希望将丢失的值记录为NULL.我目前的方法无法产生理想的结果.

I have multiple, large, csv files, each of which has missing values in many places. When I import the csv file into SQLite, I would like to have the missing values recorded as NULL for the reason that another application expects missing data to be indicated by NULL. My current method does not produce the desired result.

示例CSV文件(test.csv)是:

An example CSV file (test.csv) is:

12|gamma|17|delta
67||19|zeta
96|eta||theta
98|iota|29|

第一行完成；其他每行都有(或打算显示！)单个缺失项.当我使用以下命令导入时:

The first line is complete; each of the other lines has (or is meant to show!) a single missing item. When I import using:

.headers on
.mode column
.nullvalue NULL
CREATE TABLE t (
  id1     INTEGER  PRIMARY KEY,
  a1      TEXT,
  n1      INTEGER,
  a2      TEXT
);
.import test.csv t
SELECT
  id1, typeof(id1),
  a1,  typeof(a1),
  n1,  typeof(n1),
  a2,  typeof(a2)
FROM t;

结果是

id1   typeof(id1)  a1      typeof(a1)  n1  typeof(n1)  a2      typeof(a2)
----  -----------  ------  ----------  --  ----------  ------  ----------
12    integer      gamma     text      17  integer     delta   text                      
67    integer                text      19  integer     zeta    text                      
96    integer      eta       text          text        theta   text                      
98    integer      iota      text      29  integer             text

因此缺少的值已变成文本.我希望获得有关如何确保所有缺失值都为NULL的一些指导.

so the missing values have become text. I would appreciate some guidance on how to ensure that all missing values become NULL.

推荐答案

sqlite3将值作为文本导入，似乎没有一种方法可以将空值视为空值.

sqlite3 imports values as text and there does not seem to be a way to make it treat empty values as nulls.

但是，您可以在导入后自行更新表，将空字符串设置为null，例如

However, you can update the tables yourself after import, setting empty strings to nulls, like

UPDATE t SET a1=NULL WHERE a1='';

对每列重复一次.

您还可以为此类更新创建触发器:

You can also create a trigger for such updates:

CREATE TRIGGER trig_a1 AFTER INSERT ON t WHEN new.a1='' BEGIN
  UPDATE t SET a1=NULL WHERE rowid=new.rowid;
END;

这篇关于从csv导入时如何获取记录为NULL的缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！