interpret hex as numbers, when importing csv into sqlite3

Question

I have trouble with hex numbers in sqlite.

Given a CSV file with numbers, one of them is written as hex (0x1)

❯ cat my.csv
A,B
1,0x1

Import it and check the schema

❯ sqlite3 my.db ".import --csv my.csv somet"
❯ sqlite3 my.db ".schema somet"
CREATE TABLE IF NOT EXISTS "somet"(
"A" TEXT, "B" TEXT);

Now we can select A as a real number (no single quotes), but B not.

❯ sqlite3 my.db "select * from somet where a = 0x1;"
1|0x1

❯ sqlite3 my.db "select * from somet where b = 0x1;"

This is unexpected to me. We can see that SQLite understand hex representation of digits, when filtering on column A, but not on B.

Whe looking at the output for A, we also see that b hasnt been stored as a number, but as a litteral hex string it seems.

So, given sqlite3 understands hex representation, and it will use numbers as "numbers", when importing csv, why is B not imported as number?

When importing CSV, it's better to create the table (with column types) before and then read the data, skipping the header row, see Shawn's answer. For converting hexstrings in general, SQLite version 3.38 and above, this can be done via hexstr ->> '$'. See my answer to another question for details — Wolf
– Wolf, Commented Jul 26 at 17:43

Shawn · Accepted Answer · 2024-09-18 20:41:00Z

3

CSV import via the sqlite3 shell inserts values as strings. Any conversion to other storage types is done by the underlying INSERT based on column types and their affinity.

The B column of your table has a TEXT type, which means it has text affinity; everything is stored as a string (except for nulls and blobs). If you give the column a type that has numeric affinity, like INTEGER, strings that contain integer literals would be converted to ints, except

... hexadecimal integer literals are not considered well-formed and are stored as TEXT. (This is done for historical compatibility with versions of SQLite prior to version 3.8.6 2014-08-15 where hexadecimal integer literals were first introduced into SQLite.)

You'd have to change your ETL process to replace those hex values with base 10 integers before importing (and create the table with the correct column types ahead of time), or otherwise write a more intelligent import script.

answered Sep 18, 2024 at 20:41

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shawn Over a year ago

Reading and fully understanding sqlite.org/datatype3.html is absolutely vital to effectively using sqlite.

Wolf Jul 26 at 17:51

When loading CSV with column headers, the --skip option should be mentioned.

Collectives™ on Stack Overflow

interpret hex as numbers, when importing csv into sqlite3

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related