Getting started with the SQLizer API

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

See also: SQLizer API Reference.

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

Note that the API is available to SQLizer Pro users only. You can sign up on our plans and pricing page.

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 https://sqlizer.io/api/files/ and later accessed and updated at the endpoint https://sqlizer.io/api/files/{ID}/ .

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

curl https://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" \ -d CheckTableExists=true \ -d InsertSpacing=250 curl https://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 \ -d CheckTableExists=true \ -d InsertSpacing=250 curl https://sqlizer.io/api/files/ \ -H "Authorization: Bearer {API-KEY}" \ -d DatabaseType="PostgreSQL" \ -d FileType=tsv \ -d FileName="file.tsv" \ -d TableName="table_name" \ -d FileHasHeaders=true \ -d CheckTableExists=true \ -d InsertSpacing=250 curl https://sqlizer.io/api/files/ \ -H "Authorization: Bearer {API-KEY}" \ -d DatabaseType="SQLServer" \ -d FileType=xml \ -d FileName="file.xml" \ -d TableName="table_name" \ -d CheckTableExists=true \ -d InsertSpacing=250 curl https://sqlizer.io/api/files/ \ -H "Authorization: Bearer {API-KEY}" \ -d DatabaseType="MySQL" \ -d FileType=json \ -d FileName="file.json" \ -d TableName="table_name" \ -d CheckTableExists=true \ -d InsertSpacing=250

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", "CheckTableExists": true, "InsertSpacing": 250 }

Step 2: Upload your file

Once you have a file ID you can begin uploading your file data by sending POST requests to https://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 https://sqlizer.io/api/files/{ID}/data/ as a POST request.

curl https://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 https://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 https://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 https://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 https://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 https://sqlizer.io/api/files/{ID}/ to set the Status to 'Updated'.

curl -X PUT https://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 https://sqlizer.io/api/files/{ID}/ .

curl https://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", "CheckTableExists": true, "InsertSpacing": 250 } // 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", "CheckTableExists": true, "InsertSpacing": 250 } // 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", "CheckTableExists": true, "InsertSpacing": 250 } // 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", "CheckTableExists": true, "InsertSpacing": 250 } // 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", "CheckTableExists": true, "InsertSpacing": 250 }

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. You do not need to put the SQLizer API key on the download request as an Authorization header, as the resulting file will be downloaded from an Amazon S3 bucket using a signed URL.

(Optional) Step 5: Remove the files from our server

All uploads and the generated result files are removed from SQLizer's servers after 7 days. However, if you would like to remove the files yourself, you can do so by sending a DELETE request to the https://sqlizer.io/api/files/{ID}/ endpoint.

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

SQLizer will respond with a status message, such as:

{ "Status": "Ok" }