SQLite: import float data from the CSV file as numeric with sqlite3.exe utility

Question

I use Command Line Shell For SQLite on Windows within Git Bash console.

Let there be products.csv file in the working directory

name,price
"product A",125.00
"product B",8.99
"product C",20.50
"product D",109.99

I do import to a new products table

sqlite> .mode csv
sqlite> .import products.csv products

I check table schema. The price column is TEXT

sqlite> .schema products
CREATE TABLE products(
  "name" TEXT,
  "price" TEXT
);

As the result ordering records by price is alphabetical

sqlite> SELECT * FROM products ORDER BY price;
"product D",109.99
"product A",125.00
"product C",20.50
"product B",8.99

How to change the price column type to float in SQLite3?

P.S. The following solution does not work for me (I tried REAL, NUMERIC and FLOAT).

sqlite> UPDATE products SET price = CAST(price AS REAL);
sqlite> .schema products
CREATE TABLE products(
  "name" TEXT,
  "price" TEXT
);

Answer 1

You can create the table first and then import into the table. SQLite will import any headers as a data row when you do this so delete before hand or delete from table afterward.

create table products(name text, price float);
.mode csv
.import newproducts.csv products

where newproducts.csv is file without headers

"product A",125.00
"product B",8.99
"product C",20.50
"product D",109.99