SQLizer Logo

Easily convert files into SQL databases

About SQLizer

Security

SQLizer is HTTPS-only, with all converted files kept in secure storage and automatically deleted within 48 hours.

If you are a signed-in user, files are stored for 7 days before being automatically deleted. During this period users can re-download any converted files or choose to manually delete files as soon as a conversion completes.

For more information see our Privacy Policy.

 

How SQLizer works

SQLizer.io can read a variety of files. From data in those files it can generate the necessary SQL code for storing data in a MySQL database. SQLizer can read the following types of file:

Once the data file is uploaded SQLizer analyzes the contents of the file and works out what kind of database table can hold all the data.

Database tables need to know which type of data they hold – such as text, numbers, or dates. But many data files do not explicitly state which type of data they contain. This makes importing data into a database difficult because you need to work the tables out for yourself, which can take hours.

Fortunately, unlike other tools, SQLizer does that job for you!

Let's say you want to convert CSV to SQL. The content of the CSV file looks like this:

Column 1,Column 2,Column 3, Column_4
Hello World, 12345, 01/01/2014, 56.78
 

SQLizer detects that Column_1 contains text, Column_2 contains only whole numbers, Column_3 contains a date, and Column_4 contains a decimal number. The resulting SQL database INSERT script looks like this:

CREATE TABLE test1 (
    Column_1 VARCHAR(11) CHARACTER SET utf8,
    Column_2 INT,
    Column_3 DATETIME,
    Column_4 NUMERIC(4, 2)
);
INSERT INTO test1 VALUES ('Hello World', 12345,'2014-01-01 00:00:00', 56.78);
 

You'll see that SQLizer automatically detects maximum length and precision of columns.

Looking at the above example, SQLizer auto-detected the maximum length of Column_1 as 11 characters and the maximum size and precision of numbers in Column_4 as 4 digits and 2 decimal places.

 

How SQLizer.io handles hierarchical XML and JSON data

Unlike CSV files and database tables, XML and JSON files aren't naturally organized into rows and columns. In order to convert to SQL, SQLizer works out how to flatten XML and JSON data into a tabular form.

With XML each element containing data is converted into a row and attributes converted into columns. If you need to convert XML to SQL your file will look like this:

<?xml version="1.0" encoding="utf-8"?>
<Products>
    <Product name="widgets" id="1234">
        <Order id="1" customer_id="9" order_date="01/01/2014"/>
        <Order id="2" customer_id="3" order_date="01/02/2014"/>
        <Order id="3" customer_id="6" order_date="01/03/2014"/>
        <Order id="4" customer_id="12" order_date="01/04/2014"/>
    </Product>
</Products>
 

SQLizer flattens the above XML data into the following named columns:

  1. Products
  2. Products_Product
  3. Products_Product_name
  4. Products_Product_id
  5. Products_Product_Order
  6. Products_Product_Order_customer_id
  7. Products_Product_Order_order_date
  8. Products_Product_Order_id

The resulting SQL script looks like this:

CREATE TABLE test2 (
	Products NUMERIC(32, 16),
	Products_Product NUMERIC(32, 16),
	Products_Product_name VARCHAR(7) CHARACTER SET utf8,
	Products_Product_id NUMERIC(32, 16),
	Products_Product_Order NUMERIC(32, 16),
	Products_Product_Order_customer_id NUMERIC(32, 16),
	Products_Product_Order_order_date DATETIME,
	Products_Product_Order_id NUMERIC(32, 16)
);
INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,9,'2014-01-01 00:00:00',1);
INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,3,'2014-01-02 00:00:00',2);
INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,6,'2014-01-03 00:00:00',3);
INSERT INTO test2 VALUES (NULL,NULL,'widgets',1234,NULL,12,'2014-01-04 00:00:00',4);
 

With JSON data, properties in an object are converted into columns and entries in a list are treated as new rows.

To convert JSON to SQL, your JSON data will look like this:

[
    {
        "val1": "fizz",
        "result": 3
    },
    {
        "val1": "fizz",
        "val2": "buzz",
        "result": 15
    }
]
 

SQLizer converts the above into the following SQL:

CREATE TABLE test1 (
    list NUMERIC(32, 16),
    list_result NUMERIC(32, 16),
    list_val1 VARCHAR(4) CHARACTER SET utf8,
    list_val2 VARCHAR(4) CHARACTER SET utf8
);
INSERT INTO test1 VALUES (NULL,3,'fizz',NULL);
INSERT INTO test1 VALUES (NULL,15,'fizz','buzz');
 

However, there is an exception to this rule. Some JSON files are formatted with more than 50 properties. In this case, SQLizer treats those properties as if they were rows in a list. Here's what a JSON file would look like with 50 or more properties:

{
    "00001": { "prop1": 1, "prop2": 2 },
    "00002": { "prop1": 1, "prop2": 2 },
    "00003": { "prop1": 1, "prop2": 2 },
    ...
    "99998": { "prop1": 1, "prop2": 2 },
    "99999": { "prop1": 1, "prop2": 2 }
}
 

And here's the JSON converted to SQL:

CREATE TABLE test2 (
    `properties_key` INT,
    `properties_properties_prop1` INT,
    `properties_properties_prop2` INT
);
INSERT INTO test2 VALUES (00001,1,2);
INSERT INTO test2 VALUES (00002,1,2);
INSERT INTO test2 VALUES (00003,1,2);
...
INSERT INTO test2 VALUES (99998,1,2);
INSERT INTO test2 VALUES (99999,1,2);
 

Now you know how SQLizer works you can convert your file to SQL!