Skip to content

Importing spreadsheets

The simplest way to import data is to upload a spreadsheet. To do this, click Data Tables on the menu bar, and then Import from CSV/Excel.

You can upload spreadsheets in the following formats:

  • CSV
  • Excel 2003 and newer (XLSX extension)

In the case of CSV uploads, the format of the CSVs will be automatically detected. This includes the type of delimiter in use (comma or tab), the line endings, whether fields are enclosed in quotes, and whether column headings are included. You may adjust these settings before starting your import.

Your field names and data types will also be automatically detected during upload. You can optionally change the field names and types during the import process.

You may also compress your spreadsheet before import. This may significantly reduce the time taken to upload data on slower internet connections. Simply place your spreadsheet inside a zip file and then upload the zip file.

If you are uploading a very large spreadsheet (more than 10MB), then it will be imported in the background. You can see its progress on the Data Tables screen.

Uploading new data

Once you’ve created your data table and your first API, you will likely want to update the data from time to time. This is very straightforward to do. Sheetlabs allows you to do this via the Sheetlabs web interface, a Google Sheets add-on, and via the Sheetlabs Administrative API.

To update the data in your data table, click on Data Tables on the menu at the top of the screen, then click Update Data next to the relevant data table.

Finally, click Import to begin the data table import process. This may take a few seconds or a few minutes depending on the size of the your spreadsheet.

Please note that if your spreadsheet has changed format (e.g. you have added or removed columns, or you have changed the type of data stored in the spreadsheet) then you will receive an error informing you that your new spreadsheet is incompatible with your data table. To rectify this, either modiffy your new spreadsheet to match the format of the data table, or create a new data table and bind your API to this.

Uploading CSV/XLSX data via an API call

If you wish to automate the process of importing spreadsheets into Sheetlabs, then you might want to take advantage of the APIs that allow you to import CSV/XLSX files.

Importing spreadsheets into a new data table

To create an entirely new data table and import a CSV/XLSX file, send a POST request to https://sheetlabs.com/datatables/upload/{organisation}. The request must have the content encoding of multipart/form-data and must include a parameter named files that references the uploaded file. Optionally, you may include a metadata parameter to configure the name, fields and other attributes for the data table. If this is omitted, then the sensible defaults will be used.

The below shows how to upload a CSV file into a data table name coupons using the popular tool curl:

curl -X POST -u "user@example.com:password" -F files=@coupons.csv -F metadata='{"name":"coupons"}' https://sheetlabs.com/datatables/upload/ACME

Importing spreadsheets into an existing data table

If you already have an existing data table and want to reload data into it, then you can use the PUT method to upload a new spreadsheet. The request should be made to the endpoint https://sheetlabs.com/datatables/upload/{organisation}/{datatable_id}. The request must have the content encoding of multipart/form-data and must include a parameter named files that references the uploaded file.

The below shows how to upload a CSV file into an existing data table:

curl -X PUT -u "user@example.com:password" -v -F files=@coupons.csv https://sheetlabs.com/datatables/upload/ACME/15335

Importing spreadsheets into a new data table and creating an API in a single step

You can also create a new data table and API in a single step. To achieve this, send a POST request to https://sheetlabs.com/datatables/upload/{organisation}. The request must have the content encoding of multipart/form-data and must include a parameter named files that references the uploaded file. To signal that you want an API created, you must include a metadata parameter with the name populated and auto_managed_service: true included. You may also include other configuration in the metadata parameter for setting fields and other attributes for the data table.

The command below will upload example.csv, import it into a data table called examplez and create a matching API called example.

curl -X POST -u "user@domain.com:password" -F files=@example.csv -F metadata='{"name":"example","auto_manage_service":true}' https://sheetlabs.com/datatables/upload/ACME

Importing spreadsheets into an existing data table and amending an existing API in a single step

If you've used the single step approach above, but now want to upload a new spreadsheet (with different columns and field types) and have the API be automatically adjusted to match, then this section is for you. You will need the ID of your existing data table that you want to replace.

The command below will upload example.csv, import it into the existing data table with ID 1234, and amend the API, all in a single step. ``` curl -X PUT -u "user@domain.com:password" -F files=@test2.csv -F metadata='{"auto_manage_service":true}' https://sheetlabs.com/datatables/upload/ACME/1234