Velo Tutorial: Importing and Exporting Collection Data with Code

In this article we will demonstrate how to import data into a collection and export data from a collection using code. We create a simple form and then write some code that imports and exports JSON data from your collections using the the wix-data API.

Prerequisites

Since the wix-data API requires that your data be in JSON format, you'll need to be familiar with converting your data to and from JSON.

To import data into a collection:

1. Use a spreadsheet or database application to export your data in the CSV format. 
2. Use an online tool, such as convertcsv.com, to convert your CSV data to the JSON format.
3. Use the form as described below to import the JSON data into your collection.

To export data from a collection:

1. Use the form as described below to export the JSON data from your collection.
2. Use an online tool, such as convertcsv.com, to convert your JSON data to the CSV format.
3. Use a spreadsheet or database application to view or import your data from the CSV format. 

Data Setup

Since we are going to use the wix-data API to import data, your data will need to use the Field ID, and not the Field Name, to identify which fields you are using in your data source. Meaning, the first row of your CSV file should contain your collection's field IDs.

To find the Field ID for your collection's fields, click the vertical ellipses that appears when you hover over a field in the Editor and select Manage Properties.

If you want to provide your own IDs for the items you are adding into your collection, add a field with the key _id that contains the ID values. If you don't provide your own ID values, each item will get a unique, auto-generated ID value.

Form

Start with a new page in your site and create a form similar to the one shown here:

Code

In the Page Code panel (Wix Editor) or the Code editor (Wix Studio), start by importing the wix-data API as the very first line of code.

1
import wixData from 'wix-data';

Import Code

Select the Import button we created above and use the Properties & Events panel to add an onClick event handler.

In the Page Code panel (Wix Editor) or the Code editor (Wix Studio), add the code below to the event handler:

1
export function importButton_onClick(event) {
2
  const items = JSON.parse($w("#textBox").value);
3
  const collection = $w("#collectionInput").value;
4
 
5
  items.forEach( (item) => {
6
    wixData.insert(collection, item)
7
      .then( (results) => {
8
        console.log(`Added item: ${JSON.stringify(results)}`);
9
      } )
10
      .catch( (err) => {
11
        console.log(err);
12
      } );
13
  } );
14
 
15
  $w("#textBox").value = "";
16
}

This code pulls the name of the collection and the JSON items from the form elements. It then loops through each JSON item and adds it to the collection using the insert() function.

Export Code

Select the Export button we created above and use the Properties & Events panel to add an onClick event handler.

In the Page Code panel (Wix Editor) or the Code editor (Wix Studio), add the code below to the event handler:

1
export function exportButton_onClick(event) {
2
  let collection = $w("#collectionInput").value;
3
 
4
  wixData.query(collection)
5
    .find()
6
    .then( (results) => {
7
      $w("#textBox").value = JSON.stringify(results.items);
8
    } )
9
    .catch( (err) => {
10
      console.log(err);
11
    } );
12
}

This code pulls the name of the collection from the form element and uses it to query the collection using the query() and find() functions.

The above code will export up to 50 items from your collection. If your collection has more than 50 items, you need to add a limit that specifies the maximum number of items that the query will return:

1
export function exportButton_onClick(event) {
2
  let collection = $w("#collectionInput").value;
3
 
4
  wixData.query(collection)
5
    .limit(1000)
6
    .find()
7
    .then( (results) => {
8
      $w("#textBox").value = JSON.stringify(results.items);
9
    } )
10
    .catch( (err) => {
11
      console.log(err);
12
    } );
13
}

The maximum allowable limit is 1000. So if you want to export more than 1000 items from your collection, you will need to perform multiple queries.

Using the Form

To import data using the form:

1. Enter the name of the collection you want to import the data into.
2. Paste the data in JSON format into the text box.
3. Click the Import button.
4. As each item is imported into the collection, a success or error message is logged to the console.

To export data using the form:

1. Enter the name of the collection you want to export the data from.
2. Click the Export button.
3. The exported data is displayed in the text box or an error is logged to the console.

Additional Considerations

When you enable Sandbox for your collections importing or exporting can be performed on your Sandbox or Live collection. If you import or export while you're previewing your site, the operation will be performed using your Sandbox collection. If you import or export on your published site, the operation will be performed using your Live collection. 

You may need to use the form on your site only once to import or export data from the Live collection.

In that case you should consider if you want to publish your site to work with the Live collection or work with the Live collection through the Sandbox collection to avoid the potential security risk described above.

Using the form with the Live Collection without publishing your site

To export the data from your Live collection through the Sandbox:

1. Create the form and add the code.
2. Copy the items from your Live collection to your Sandbox collection.
3. Preview your site and use the form to export your data.
4. Delete the page that contains the form before your next site publish.

To import data to your Live collection through the Sandbox:

1. Create the form and add the code.
2. Preview your site and use the form to import your data.
3. Copy the imported items from your Sandbox to your Live collection.
4. Delete the page that contains the form before your next site publish.

Using the form on your published site

If you are going to import or export more than once and need the form for future use:

1. Create the form and add the code.
2. Hide the form's page so it doesn't appear in your site's menu.
3. Password protect the form's page so visitors can't access the page even if they have its URL.
4. Add a Member Login button if necessary (see note below).
5. Publish your site.
6. Use the form when previewing your site to import and export using your Sandbox collection.
7. Use the form on your published site to import and export using your Live collection.

API List

The following APIs are used in the code in this article. To learn more, see the API Reference.

$w.TextBox

• $w.TextBox.value - Sets or gets an element's value.

$w.TextInput

• $w.TextInput.value - Sets or gets an element's value.

wix-data

• wix-data.insert( ) - Adds an item to a collection.
• wix-data.query( ) - Creates a query.
• wix-data.WixDataQuery.find( ) - Returns the items that match a query.
• wix-data.WixDataQuery.limit( ) - Limits the number of items a query returns.