SQLizer Logo

Easily convert files into SQL databases

Number Handling in SQLizer

SQLizer will detect when your data fits in one of the numeric data types, and will create the appropriate database column types for your chosen target database.

For example, let's consider this CSV file: 

ID, Numbers
1, 123
2, 0.1
3, 3.14

The ID column only contains integers but the 'Numbers' column contains a mixture of integers and decimals. The number of the digits, and decimal places varies but the maximum number of digits necessary to store all the data is 5 digits, with 2 decimal places.

If we give that CSV to SQLizer, it gives us the following SQL: 

CREATE TABLE IF NOT EXISTS numbers (
    `ID` INT,
    `Numbers` NUMERIC(5, 2)
);
INSERT INTO numbers VALUES
    (1, 123),
    (2, 0.1),
    (3, 3.14);
IF NOT EXISTS (
    select * from sysobjects where name='numbers' and xtype='U'
) CREATE TABLE numbers (
    [ID] INT,
    [Numbers] NUMERIC(5, 2)
);
INSERT INTO numbers VALUES
    (1, 123),
    (2, 0.1),
    (3, 3.14);
CREATE TABLE IF NOT EXISTS "numbers" (
    "ID" INT,
    "Numbers" NUMERIC(5, 2)
);
INSERT INTO "numbers" VALUES
    (1, 123),
    (2, 0.1),
    (3, 3.14);
CREATE TABLE IF NOT EXISTS numbers (
    ID INTEGER,
    Numbers NUMERIC
);
INSERT INTO numbers VALUES
    (1, 123),
    (2, 0.1);

Notice how the 'Numbers' column is given the NUMERIC(5, 2) data type, except on SQLite where that kind of precision isn't supported.

SQLizer will also detect when numbers need to be stored in bigger integer types. For example, the following CSV contains a very large integer: 

ID, Numbers, Big Numbers
1, 2147483645, 2147483648
2, 2147483646, 2147483649
3, 2147483647, 2147483650

The values in the 'Numbers' column will fit within a 32 bit integer, but the values in the 'Big Numbers' column won't. They'll need a different data type. When we run this file through SQLizer, we get the following SQL: 

CREATE TABLE IF NOT EXISTS big_numbers (
    `ID` INT,
    `Numbers` INT,
    `Big_Numbers` BIGINT
);
INSERT INTO big_numbers VALUES
    (1, 2147483645, 2147483648),
    (2, 2147483646, 2147483649),
    (3, 2147483647, 2147483650);
IF NOT EXISTS (
    select * from sysobjects where name='big_numbers' and xtype='U'
) CREATE TABLE big_numbers (
    [ID] INT,
    [Numbers] INT,
    [Big_Numbers] BIGINT
);
INSERT INTO big_numbers VALUES
    (1, 2147483645, 2147483648),
    (2, 2147483646, 2147483649),
    (3, 2147483647, 2147483650);
CREATE TABLE IF NOT EXISTS "big_numbers" (
    "ID" INT,
    "Numbers" INT,
    "Big_Numbers" BIGINT
);
INSERT INTO "big_numbers" VALUES
    (1, 2147483645, 2147483648),
    (2, 2147483646, 2147483649),
    (3, 2147483647, 2147483650);
CREATE TABLE IF NOT EXISTS big_numbers (
    ID INTEGER,
    Numbers INTEGER,
    Big_Numbers INTEGER
);
INSERT INTO big_numbers VALUES
    (1, 2147483645, 2147483648),
    (2, 2147483646, 2147483649),
    (3, 2147483647, 2147483650);

Notice the 'Big_Numbers' column is given the BIGINT data type, except on SQLite.