SQLizer Logo

Easily convert files into SQL databases

Sqlizer API

Getting started...

The Sqlizer API is a simple REST interface which gives you programmatic access to our file conversion algorithm.

The processes of converting a file with the API follows four steps:

  1. Initiate the file conversion
  2. Upload your file
  3. Monitor the conversion progress
  4. Download your converted file

Authentication

All requests made to the API need include a HTTP Authorization header with your API key so we can identify who you are.

Authorization: Bearer {API-KEY}

This key can be regenerated on the account page.

Step 1: Initiate the file conversion

A file conversion can be initiated with a POST request to http://sqlizer.io/api/files/ and later accessed and updated at the endpoint http://sqlizer.io/api/files/{ID}/.

Below are some sample POST requests to initiate the different types of file conversion using curl.

curl http://sqlizer.io/api/files/  \
    -H "Authorization: Bearer {API-KEY}" \
    -d DatabaseType="MySQL" \
    -d FileType=xlsx \
    -d FileName="file.xlsx" \
    -d TableName="table_name" \
    -d FileHasHeaders=true \
    -d SheetName="Sheet1" \
    -d CellRange="A1:F100"
curl http://sqlizer.io/api/files/  \
    -H "Authorization: Bearer {API-KEY}" \
    -d DatabaseType="SQLServer" \
    -d FileType=csv \
    -d FileName="file.csv" \
    -d TableName="table_name" \
    -d FileHasHeaders=true
curl http://sqlizer.io/api/files/  \
    -H "Authorization: Bearer {API-KEY}" \
    -d DatabaseType="MySQL" \
    -d FileType=tsv \
    -d FileName="file.tsv" \
    -d TableName="table_name" \
    -d FileHasHeaders=true
curl http://sqlizer.io/api/files/  \
    -H "Authorization: Bearer {API-KEY}" \
    -d DatabaseType="SQLServer" \
    -d FileType=xml \
    -d FileName="file.xml" \
    -d TableName="table_name"
curl http://sqlizer.io/api/files/  \
    -H "Authorization: Bearer {API-KEY}" \
    -d DatabaseType="MySQL" \
    -d FileType=json \
    -d FileName="file.json" \
    -d TableName="table_name"

If the request is successful the server will respond with a HTTP 200 code and a JSON representation of the file conversion. The ID field is especially important as it is used in the url of all future requests for this file conversion.

Below is an example of the JSON that might be returned by the API.

{
    "ID": "7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==",
    "DatabaseType": "MySQL",
    "Status": "New",
    "FileType": "xlsx",
    "FileName": "file.xlsx",
    "TableName": "table_name",
    "FileHasHeaders": true,
    "SheetName": "Sheet1",
    "CellRange": "A1:F100",
}

Step 2: Upload your file

Once you have a file ID you can begin uploading your file data by sending POST requests to http://sqlizer.io/api/files/{ID}/data/.

The Sqlizer API allows you to upload your data in a single request or multiple requests. The latter is achieved by splitting the file into parts and sending each individually. Most files can be uploaded in a single request but if your file is larger than 100Mb or you have an unreliable connection an upload in multiple parts is recommended.

Upload in a single request

To submit your file in a single request the file should be encoded in a multipart form with the parameter name 'file' and sent to http://sqlizer.io/api/files/{ID}/data/ as a POST request.

curl http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/data/ \
    -H "Authorization: Bearer {API-KEY}" \
    -F "file=@/path/to/the/file.xlsx"

Once you have finished uploading your file you will need to finalise the upload.

Upload in multiple parts

To submit your file in a multiple parts you will first need to split the file into parts. Each part with exception of the last part must be at least 5Mb (5,242,880 bytes) but must be no more than 10,000,000 bytes. The final part must be no more than 10,000,000 bytes.

A simple way to partition your file is to use the unix split command. The following unix command will take your file and convert it to 5Mb parts named: Part_01, Part_02,...

split --numeric-suffixes=1 -b 5M FileName.xlsx Part_

Once you have partitioned your file into parts each part can be separately sent as a POST request to http://sqlizer.io/api/files/{ID}/data/ encoded as a multipart form with parameter name 'file'. The multipart form also needs a second parameter 'PartNumber' to indicate the part's order within the file. The first part should be given PartNumber = 1.

curl http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/data/ \
    -H "Authorization: Bearer {API-KEY}" \
    -F PartNumber=1 \
    -F "file=@/path/to/files/Part_01"

You can check whether an individual part has been uploading with a GET request to http://sqlizer.io/api/files/{ID}/data/?PartNumber={PartNumber}. The API will respond with a HTTP 200 code if the part was successfully uploaded and a HTTP 404 code if the part has not yet been successfully uploaded.

curl http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/data/?PartNumber=1 \
    -H "Authorization: Bearer {API-KEY}"

Once you have finished uploading all the file parts you will need to finalise the upload.

Finalising the upload

Once you have finished uploading your file the upload process needs to be finalised with a PUT request to http://sqlizer.io/api/files/{ID}/ to set the Status to 'Updated'.

curl -X PUT http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/ \
    -H "Authorization: Bearer {API-KEY}" \
    -d Status=Uploaded

Step 3: Monitor the conversion progress

Once your file upload is finalised it will be placed in a queue for processing. You can check on its progress with a GET request to http://sqlizer.io/api/files/{ID}/.

curl http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/ \
    -H "Authorization: Bearer {API-KEY}"

Below you can find some sample responses for the different statuses the file conversion process can be in.

// File is currently queued.
{
    "ID": "7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==",
    "DatabaseType": "MySQL",
    "Status": "Uploaded",
    "FileType": "xlsx",
    "FileName": "file.xlsx",
    "TableName": "table_name",
    "FileHasHeaders": true,
    "SheetName": "Sheet1",
    "CellRange": "A1:F100",
}
// File currently being analysed.
{
    "ID": "7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==",
    "DatabaseType": "MySQL",
    "Status": "Analysing",
    "PercentComplete": 32, // This property may not exist for all file conversion types
    "FileType": "xlsx",
    "FileName": "file.xlsx",
    "TableName": "table_name",
    "FileHasHeaders": true,
    "SheetName": "Sheet1",
    "CellRange": "A1:F100",
}
// File currently being converted into a database script.
{
    "ID": "7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==",
    "DatabaseType": "MySQL",
    "Status": "Processing",
    "PercentComplete": 57, // This property may no exist for all file conversion types
    "FileType": "xlsx",
    "FileName": "file.xlsx",
    "TableName": "table_name",
    "FileHasHeaders": true,
    "SheetName": "Sheet1",
    "CellRange": "A1:F100",
}
// File has been successfully converted into a database script.
{
    "ID": "7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==",
    "DatabaseType": "MySQL",
    "Status": "Complete",
    "ResultUrl": "https://example.com/result.sql", // This property links to the completed database script
    "ResultRows": 4500, // This lists the number of results in the completed database scripts
    "FileType": "xlsx",
    "FileName": "file.xlsx",
    "TableName": "table_name",
    "FileHasHeaders": true,
    "SheetName": "Sheet1",
    "CellRange": "A1:F100",
}
// The file conversion has failed for some reason.
{
    "ID": "7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==",
    "DatabaseType": "MySQL",
    "Status": "Failed",
    "Message": "We couldn't find a worksheet named 'Sheet1'.", // Reason for failure
    "FileType": "xlsx",
    "FileName": "file.xlsx",
    "TableName": "table_name",
    "FileHasHeaders": true,
    "SheetName": "Sheet1",
    "CellRange": "A1:F100",
}

Step 4: Download your converted file

Once the file conversion process is complete you can download the converted file from the url specified in the returned ResultUrl parameter.