CODEWITHSUNDEEP
sqlitecsvimportblobuuid
hedgar2017
hedgar2017

Reputation: 1657

Import BLOBs from a CSV to an SQLite table

How should I put UUIDs into a CSV file in order to make SQLite .import command to load them into table as 128-bit BLOBs?

Upvotes: 0

Views: 906

Answers (2)

hedgar2017
hedgar2017

Reputation: 1657

SQLite is unable to import BLOBs from CSV.

The solution is to convert CSV to an SQL-statement file and execute it:

sqlite3 database.db < database.sql

If you pipe from an application, 100000-row chunks are the most optimal amount per process instance.

If you try to pipe many gigabytes at once, sqlite3 will crash with an Out of memory error.

Upvotes: 2

Shawn
Shawn

Reputation: 52539

As far as I know, the only ways to generate a blob from the sqlite3 shell are using the zeroblob(), randomblob() and readfile() sql functions, CASTing a value, or as a base 16 blob literal (X'1234ABCD').

If your UUIDs are already represented as big endian 128 bit binary numbers in the CVS file, you might be able to do something like UPDATE table SET uuid = CAST(uuid AS BLOB); after the import. If they're a textual representation like 123e4567-e89b-12d3-a456-426655440000 you could write a user-defined function to do the conversion, and using it with a similar post-import UPDATE.

Upvotes: 2

Related Questions