SQLite3-如何从csv导入NULL值 [英] Sqlite3 - How to import NULL values from csv

问题描述

我已将mysql表转储为CSV。在此CSV文件中，NULL值写为 \N

现在，我想将此数据导入sqlite数据库，但是我无法告诉sqlite \N 为空值。它会将其视为字符串，并且该列值存储为 asN而不是NULL。

I have dumped a mysql table as CSV. In this CSV file, the NULL values are written as \N
Now I want to import this data into sqlite database, but I am unable to tell sqlite that \N are null values. It is treating it as a string and that column value is stored as "\N" instead of NULL.

任何人都可以指导如何使用 .nullvalue 点命令。我无法将 \N 设置为空值。

Can anyone guide in how to use .nullvalue dot command from sqlite. I am unable to set \N as nullvalue.

sqlite> .show
nullvalue: ""
sqlite> .nullvalue \N
sqlite> .show
nullvalue: "N"
sqlite> .nullvalue '\N'
sqlite> .show
nullvalue: "\\N"
sqlite> .nullvalue "\N"
sqlite> .show
nullvalue: "N"
sqlite> .nullvalue \\N
sqlite> .show
nullvalue: "\\N"
sqlite> .nullvalue '\'N
Usage: .nullvalue STRING
sqlite> .nullvalue '\\'N
Usage: .nullvalue STRING
sqlite> .nullvalue \\N
sqlite> .show
nullvalue: "\\N"
sqlite>

这是每个 nullvalue

sqlite> .import /tmp/mysqlDump.csv employee
sqlite> select count(*) from employee where updatedon='\N';
94143
sqlite> select count(*) from employee where updatedon is null;
0

我如何告诉sqlite治疗 \N 作为NULL值？我不能使用空字符串作为NULL值，因为我的数据包含空字符串。

How can I tell sqlite to treat \N as NULL value? I cannot use empty string as NULL value as my data contains empty strings.

推荐答案

CSV文件仅包含文本值。无法从CSV文件导入NULL值。

CSV files contain only text values. It is not possible to import NULL values from a CSV file.

要将 \N 值转换为NULL ，只需在事后使用UPDATE即可。

To convert the \N values into NULLs, just use UPDATE afterwards:

UPDATE employee SET updatedon = NULL WHERE updatedon = '\N';

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