1

test.csv looks like this:

"Code","Member","Position","ModDate"
"AAN","Smith, John",,1/21/2020 0:00:00
"AAN","Doe, Ron",,1/21/2020 0:00:00
"AAN","Doe, Jane","C",1/21/2020 0:00:00

Note that 2 values in the Position column are missing. They're blank. There are no quotes, no '' or "", just empty space between 2 commas: ,,.

Is it possible to import such values as NULL into SQLite using the sqlite3 CLI? So far, it'll only import them as empty strings: ''. (Yes, I could use Python or run UPDATE ... SET (see later), but I'm wondering if a simple CLI command will suffice.)

Put another way, I want to run this query to get rows with missing positions:

SELECT *
FROM tbl
WHERE position IS NULL
;

But so far I can only achieve that result with this query:

SELECT *
FROM tbl
WHERE position = ''
;

First I tried the default:

sqlite3 -csv test.db ".import test.csv tbl"

As I said, this imports null values as empty strings.

sqlite3's -help text mentions a -nullvalue option:

-nullvalue TEXT      set text string for NULL values. Default ''

I assume this is for importing. I try this:

sqlite3 -csv -nullvalue ",," test.db ".import test.csv tbl"

No luck. And I don't know what else to try. I can't find any further explanation of the -nullvalue option in SQLite's docs or any examples with Google.

Perhaps I could just update these values after importing, as answered here:

sqlite3 -csv test.db ".import test.csv tbl"
sqlite3 test.db
sqlite> UPDATE tbl SET position = NULL WHERE position = '';

However, this isn't entirely satisfactory. First, I have multiple CSVs and multiple columns within each of them that I must update. This would require repetition. Is there a better way?

ardaar
  • 1,164
  • 9
  • 19
  • Doesn't it insert `NULL` when you import without any `-nullvalue` parameter? idk maybe try the CSV Lint plugin for Noptepad++ https://github.com/BdR76/CSVLint and just convert the csv file to an SQL insert script, it automatically converts empty values to `NULL` though not sure if the output script is compatible with SQLite3. – BdR Dec 08 '21 at 14:02
  • You mean `sqlite3 -csv -nullvalue test.db ".import test.csv tbl"`? That doesn't seem valid. Entering this just continues the line, like the command expects to be finished. I'd just use Python instead of a manual process in a text editor. I was wondering if it's possible with the sqlite CLI. – ardaar Dec 08 '21 at 16:57
  • I have a perl script for importing CSV into sqlite that can do this among many other things. Let me know if you're interested. – Shawn Dec 08 '21 at 17:13
  • @Shawn, I can do it in Python if there really is no way to do it with the sqlite3 CLI. – ardaar Dec 08 '21 at 17:37
  • CSV files do not have data types, and CSV files do not have a NULL value concept, so every value SQLite imports is imported to the database with `sqlite3_bind_text`. If you want any other behavior, you'll need to use something other than the SQLite shell. – Anon Coward Dec 09 '21 at 01:49
  • @AnonCoward: Yes, but the CLI lets you specify which values the parser should consider NULL. Is there no way to make the `sqlite3` CLI import this specific pattern--`,,`--as NULL? – ardaar Dec 09 '21 at 13:29
  • "CLI lets you specify which values the parser should consider NULL" There is no such functionality. `.nullvalue` lets you control what is displayed when a NULL value is displayed from a query, nothing more. – Anon Coward Dec 09 '21 at 14:37
  • @AnonCoward: I see. If you're sure there's no way to do what I want with the CLI, write it as an answer and I'll mark yours as The Answer. – ardaar Dec 09 '21 at 19:09

0 Answers0