Getting Started: Converting files to SQL with the SQLizer API
Getting started with the SQLizer API
The SQLizer API is a REST interface which gives you programmatic access to our file conversion algorithm.
The processes of converting a file with the API follows four steps:
- Create a ‘file’ via the SQLizer API
- Upload your file’s data
- Monitor the conversion progress
- Download the results
Authentication
To use the API, you will need to sign up for an account to get an API key.
All requests made to the API need include a HTTP Authorization header with your API key so we can identify who you are. Once you are signed in, this key can be found on the account page. See the User Guide on Managing your SQLizer API Keys for more information.
You can set the Authentication Header using cURL like this:
curl https://sqlizer.io/api/files/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ"
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.
XLS / XLSX to SQL
curl https://sqlizer.io/api/files/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ" \
-d DatabaseType="MySQL" \
-d FileType=xlsx \
-d FileName="file.xlsx" \
-d TableName="table_name" \
-d FileHasHeaders=true
CSV to SQL
curl https://sqlizer.io/api/files/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ" \
-d DatabaseType="SQLServer" \
-d FileType=csv \
-d FileName="file.csv" \
-d TableName="table_name" \
-d FileHasHeaders=true
XML to SQL
curl https://sqlizer.io/api/files/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ" \
-d DatabaseType="SQLServer" \
-d FileType=xml \
-d FileName="file.xml" \
-d TableName="table_name"
JSON to SQL
curl https://sqlizer.io/api/files/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ" \
-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.
{
"Status": "New",
"CheckTableExists": true,
"InsertSpacing": 250,
"FileType": "xlsx",
"DatabaseType": "MySQL",
"TableName": "table_name",
"ID": "gKKIyEhQFH5we4dvvpRYABixt2RelK6UFDpG0wa4zc2VEcnBuwwEgN4nKX8PKavXEXnXNrOYr1zAgSGFGUg",
"FileName": "file.xlsx",
"FileHasHeaders": true
}
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.
This guide uses the single request method, for more information on the multiple requests approach, see the page on multipart-upload.
To upload 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/gKKIyEhQFH5we4dvvpRYABixt2RelK6UFDpG0wa4zc2VEcnBuwwEgN4nKX8PKavXEXnXNrOYr1zAgSGFGUg/data/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ" \
-F "file=@/path/to/the/file.xlsx"
Finalising the upload
Once you have finished uploading your file you can tell SQLizer to begin conversion by sending a PUT request to https://sqlizer.io/api/files/{ID}/
to set the Status to ‘Uploaded’.
curl -X PUT https://sqlizer.io/api/files/gKKIyEhQFH5we4dvvpRYABixt2RelK6UFDpG0wa4zc2VEcnBuwwEgN4nKX8PKavXEXnXNrOYr1zAgSGFGUg/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ" \
-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/gKKIyEhQFH5we4dvvpRYABixt2RelK6UFDpG0wa4zc2VEcnBuwwEgN4nKX8PKavXEXnXNrOYr1zAgSGFGUg/ \
-H "Authorization: Bearer pPSM2zM0xEowKmuRIheEWNpmAv7lY0KIRf8Kk1bWCfK9c1peHgXYYZDau7tFNR9JdiHjpQmxkGPu0u8a1FYQ"
Here’s an example of the kind of response you might get:
{
"Status": "Processing",
"CheckTableExists": true,
"InsertSpacing": 250,
"FileType": "xlsx",
"DatabaseType": "MySQL",
"TableName": "table_name",
"ID": "gKKIyEhQFH5we4dvvpRYABixt2RelK6UFDpG0wa4zc2VEcnBuwwEgN4nKX8PKavXEXnXNrOYr1zAgSGFGUg",
"FileName": "file.xlsx",
"FileHasHeaders": true,
"PercentComplete": "32",
}
You will see the Status
property update through a number of statuses and hopefully react a status of Complete
after a few seconds. For information on what each of these statuses mean, check the HTTP Reference page.
Step 4: Download your converted file
Once the file’s “Status” property reaches Complete
the JSON returned from a GET request to the file’s URL will contain a ResultUrl
property.
{
"Status": "Complete",
"CheckTableExists": true,
"InsertSpacing": 250,
"FileType": "xlsx",
"DatabaseType": "MySQL",
"TableName": "table_name",
"ID": "gKKIyEhQFH5we4dvvpRYABixt2RelK6UFDpG0wa4zc2VEcnBuwwEgN4nKX8PKavXEXnXNrOYr1zAgSGFGUg",
"FileName": "file.xlsx",
"FileHasHeaders": true,
"ResultUrl": "https://example.com/file-download-link",
"ResultRows": 5499,
}
You can download the converted file from that URL.
curl https://example.com/file-download-link
Note: You do not need to put the SQLizer API key on the download request as an Authorization header, as this URL will be an Amazon S3 signed URL, not a SQLizer API request.
🖤 SQLizer team.
About: SQLizer converts for free - less than 5,000 rows of data for personal use - however if you have a >5000 row CSV file you need to convert, use code CONVERTME10
for 10% off your Pro Monthly plan for the first 12 months, or a one-off 10% discount on your Pro Annual plan.
More from The Official SQLizer blog...
- All new: A JavaScript Client for SQLizer on npm Rejoice, JS developers! A JavaScript client library for SQLizer.io, easily converting CSV, JSON, XML and Spreadsheet files into SQL INSERT or UPDATE statements - is...
- [Update 2024] Convert JSON to SQL: Free and Fast If you want to convert JSON to SQL there’s no concrete or straightforward way of doing things. Conversion is usually tricky because JSON and SQL...
- [Update 2024] Convert XML to SQL Easy and Free To convert XML to SQL, a touch of wizardry is needed. Unlike CSV files and database tables, XML files aren’t naturally organized into rows and...
- [Update 2024] Converting a Word Doc to SQL with SQLizer Wait, what? A Word document? Have you ever found yourself faced with a Word document containing a wealth of data, all neatly structured in a...