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
);

You could create the table ahead of time. "create table products(name text, price float)" and then perform the import. — Todd
– Todd, Commented Dec 15, 2015 at 19:59

mwloda · Accepted Answer · 2015-12-15 21:29:47Z

4

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

edited Dec 15, 2015 at 21:29

mwloda

4911 gold badge6 silver badges14 bronze badges

answered Dec 15, 2015 at 21:14

Todd

6922 gold badges22 silver badges39 bronze badges

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

Comments

CL. · Accepted Answer · 2015-12-15 20:56:15Z

1

Values in CSV files are always imported as strings.

You have to create the table with the types you want, and to change the types afterwards:

UPDATE products
SET price = CAST(price AS NUMERIC);

edited Dec 15, 2015 at 20:56

answered Dec 15, 2015 at 18:25

CL.

182k18 gold badges241 silver badges282 bronze badges

1 Comment

mwloda Over a year ago

I've already tried it. It does not change table. Rows are still ordered alphabetically.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related