# Preparing dataset examples


You can prepare data in any dataset to make it more suitable for analysis, for example changing a field name or adding a calculated field. For database datasets, you can also determine the data used by specifying a SQL query or joining two or more tables. 

Use the following topics to learn how to prepare datasets.

**Topics**
+ [

# Preparing a dataset based on file data
](prepare-file-data.md)
+ [

# Preparing a dataset based on Salesforce data
](prepare-salesforce-data.md)
+ [

# Preparing a dataset based on database data
](prepare-database-data.md)

# Preparing a dataset based on file data


Use the following procedure to prepare a dataset based on text or Microsoft Excel files from either your local network or Amazon S3.

**To prepare a dataset based on text or Microsoft Excel files from a local network or S3**

1. Open a file dataset for data preparation by choosing one of the following options:
   + Create a new local file dataset, and then choose **Edit/Preview data**. For more information about creating a new dataset from a local text file, see [Creating a dataset using a local text file](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-file.html). For more information about creating a new dataset from a Microsoft Excel file, see [Creating a dataset using a Microsoft Excel file](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-excel.html).
   + Create a new Amazon S3 dataset, and then choose **Edit/Preview data**. For more information about creating a new Amazon S3 dataset using a new Amazon S3 data source, see [Creating a dataset using Amazon S3 files](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-s3.html). For more information about creating a new Amazon S3 dataset using an existing Amazon S3 data source, see [Creating a dataset using an existing Amazon S3 data source](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-existing-s3.html).
   + Open an existing Amazon S3, text file, or Microsoft Excel dataset for editing, from either the analysis page or the **Your Datasets** page. For more information about opening an existing dataset for data preparation, see [Editing datasets](https://docs.amazonaws.cn/quicksight/latest/user/edit-a-data-set.html).

1. (Optional) On the data preparation page, enter a new name into the dataset name box on the application bar. 

   This name defaults to the file name for local files. For example, it defaults to **Group 1** for Amazon S3 files.

1. Review the file upload settings and correct them if necessary. For more information about file upload settings, see [Choosing file upload settings](https://docs.amazonaws.cn/quicksight/latest/user/choosing-file-upload-settings.html).
**Important**  
If you want to change upload settings, make this change before you make any other changes to the dataset. New upload settings cause Amazon Quick Sight to reimport the file. This process overwrites all of your other changes.

1. Prepare the data by doing one or more of the following:
   + [Selecting fields](https://docs.amazonaws.cn/quicksight/latest/user/selecting-fields.html)
   + [Editing field names and descriptions](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-name.html)
   + [Changing a field data type](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-data-type.html)
   + [Adding calculated fields](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-calculated-field-analysis.html)
   + [Filtering data in Amazon Quick Sight](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-filter.html)

1. Check the [SPICE](spice.md) indicator to see if you have enough capacity to import the dataset. File datasets automatically load into SPICE. The import happens when you choose either **Save & visualize** or **Save**. 

   If you don't have access to enough SPICE capacity, you can make the dataset smaller by using one of the following options: 
   + Apply a filter to limit the number of rows.
   + Select fields to remove from the dataset.
**Note**  
The SPICE indicator doesn't update to how much space you save by removing fields or filtering the data. It continues to reflect the SPICE usage from the last import.

1. Choose **Save** to save your work, or **Cancel** to cancel it. 

   You might also see **Save & visualize**. This option appears based on the screen that you started from. If this option isn't there, you can create a new visualization by starting from the dataset screen. 

## Preparing a dataset based on a Microsoft Excel file


Use the following procedure to prepare a Microsoft Excel dataset.

**To prepare a Microsoft Excel dataset**

1. Open a text file dataset for preparation by choosing one of the following options:
   + Create a new Microsoft Excel dataset, and then choose **Edit/Preview data**. For more information about creating a new Excel dataset, see [Creating a dataset using a Microsoft Excel file](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-excel.html).
   + Open an existing Excel dataset for editing. You can do this from the analysis page or the **Your Datasets** page. For more information about opening an existing dataset for data preparation, see [Editing datasets](https://docs.amazonaws.cn/quicksight/latest/user/edit-a-data-set.html).

1. (Optional) On the data preparation page, enter a name into the dataset name box in the application bar. If you don't rename the dataset, its name defaults to the Excel file name.

1. Review the file upload settings and correct them if necessary. For more information about file upload settings, see [Choosing file upload settings](https://docs.amazonaws.cn/quicksight/latest/user/choosing-file-upload-settings.html). 
**Important**  
If it's necessary to change upload settings, make this change before you make any other changes to the dataset. Changing upload settings causes Amazon Quick Sight to reimport the file. This process overwrites any changes you have made so far.

1. (Optional) Change the worksheet selection. 

1. (Optional) Change the range selection. To do this, open **Upload Settings** from the on-dataset menu beneath the login name at upper right.

1. Prepare the data by doing one or more of the following:
   + [Selecting fields](https://docs.amazonaws.cn/quicksight/latest/user/selecting-fields.html)
   + [Editing field names and descriptions](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-name.html)
   + [Changing a field data type](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-data-type.html)
   + [Adding calculated fields](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-calculated-field-analysis.html)
   + [Filtering data in Quick Sight](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-filter.html)

1. Check the [SPICE](spice.md) indicator to see if you have enough space to import the dataset. Amazon Quick Sight must import Excel datasets into SPICE. This import happens when you choose either **Save & visualize** or **Save**.

   If you don't have enough SPICE capacity, you can choose to make the dataset smaller using one of the following methods:
   + Apply a filter to limit the number of rows.
   + Select fields to remove from the dataset.
   + Define a smaller range of data to import.
**Note**  
The SPICE indicator doesn't update to reflect your changes until after your load them. It shows the SPICE usage from the last import.

1. Choose **Save** to save your work, or **Cancel** to cancel it. 

   You might also see **Save & visualize**. This option appears based on the screen that you started from. If this option isn't there, you can create a new visualization by starting from the dataset screen. 

# Preparing a dataset based on Salesforce data


Use the following procedure to prepare a Salesforce dataset.

**To prepare a Salesforce dataset**

1. Open a Salesforce dataset for preparation by choosing one of the following options:
   + Create a new Salesforce dataset and choose **Edit/Preview data**. For more information about creating a new Salesforce dataset using a new Salesforce data source, see [Creating a dataset from Salesforce](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-salesforce.html). For more information about creating a new Salesforce dataset using an existing Salesforce data source, see [Create a dataset using an existing Salesforce data source](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-existing-salesforce.html).
   + Open an existing Salesforce dataset for editing from either the analysis page or the **Your Datasets** page. For more information about opening an existing dataset for data preparation, see [Editing datasets](https://docs.amazonaws.cn/quicksight/latest/user/edit-a-data-set.html).

1. (Optional) On the data preparation page, enter a name into the dataset name box in the application bar if you want to change the dataset name. This name defaults to the report or object name.

1. (Optional) Change the data element selection to see either reports or objects.

1. (Optional) Change the data selection to choose a different report or object.

   If you have a long list in the **Data** pane, you can search to locate a specific item by entering a search term into the **Search tables** box. Any item whose name contains the search term is shown. Search is case-insensitive and wildcards are not supported. Choose the cancel icon (**X**) to the right of the search box to return to viewing all items.

1. Prepare the data by doing one or more of the following:
   + [Selecting fields](https://docs.amazonaws.cn/quicksight/latest/user/selecting-fields.html)
   + [Editing field names and descriptions](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-name.html)
   + [Changing a field data type](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-data-type.html)
   + [Adding calculated fields](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-calculated-field-analysis.html)
   + [Filtering data in Quick Sight](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-filter.html)

1. Check the [SPICE](spice.md) indicator to see if you have enough space to import the dataset. Importing data into SPICE is required for Salesforce datasets. Importing occurs when you choose either **Save & visualize** or **Save**.

   If you don't have enough SPICE capacity, you can remove fields from the dataset or apply a filter to decrease its size. For more information about adding and removing fields from a dataset, see [Selecting fields](https://docs.amazonaws.cn/quicksight/latest/user/selecting-fields.html).
**Note**  
The SPICE indicator doesn't update to reflect the potential savings of removing fields or filtering the data. It continues to reflect the size of the dataset as retrieved from the data source.

1. Choose **Save** to save your work, or **Cancel** to cancel it. 

   You might also see **Save & visualize**. This option appears based on the screen you started from. If this option isn't there, you can create a new visualization by starting from the dataset screen. 

# Preparing a dataset based on database data


Use the following procedure to prepare a dataset based on a query to a database. The data for this dataset can be from an Amazon database data source like Amazon Athena, Amazon RDS, or Amazon Redshift, or from an external database instance. You can choose whether to import a copy of your data into [SPICE](spice.md), or to query the data directly.

**To prepare a dataset based on a query to a database**

1. Open a database dataset for preparation by choosing one of the following options:
   + Create a new database dataset and choose **Edit/Preview data**. For more information about creating a new dataset using a new database data source, see [Creating a dataset from a database](https://docs.amazonaws.cn/quicksight/latest/user/create-a-database-data-set.html). For more information about creating a new dataset using an existing database data source, see [Creating a dataset using an existing database data source](https://docs.amazonaws.cn/quicksight/latest/user/create-a-data-set-existing-database.html).
   + Open an existing database dataset for editing from either the analysis page or the **Your Datasets** page. For more information about opening an existing dataset for data preparation, see [Editing datasets](https://docs.amazonaws.cn/quicksight/latest/user/edit-a-data-set.html).

1. (Optional) On the data preparation page, enter a name into the dataset name box on the application bar.

   This name defaults to the table name if you selected one before data preparation. Otherwise, it's **Untitled data source**.

1. Decide how your data is selected by choosing one of the following:
   + To use a single table to provide data, choose a table or change the table selection.

     If you have a long table list in the **Tables** pane, you can search for a specific table by typing a search term for **Search tables**. 

     Any table whose name contains the search term is shown. Search is case-insensitive and wildcards are not supported. Choose the cancel icon (**X**) to the right of the search box to return to viewing all tables.
   + To use two or more joined tables to provide data, choose two tables and join them using the join pane. You must import data into Quick Sight if you choose to use joined tables. For more information about joining data using the Amazon Quick Sight interface, see [Joining data](https://docs.amazonaws.cn/quicksight/latest/user/joining-data.html).
   + To use a custom SQL query to provide data in a new dataset, choose **Switch to Custom SQL** tool on the **Tables** pane. For more information, see [Using SQL to customize data](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-SQL-query.html).

     To change the SQL query in an existing dataset, choose **Edit SQL** on the **Fields** pane to open the SQL pane and edit the query.

1. Prepare the data by doing one or more of the following:
   + [Selecting fields](https://docs.amazonaws.cn/quicksight/latest/user/selecting-fields.html)
   + [Editing field names and descriptions](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-name.html)
   + [Changing a field data type](https://docs.amazonaws.cn/quicksight/latest/user/changing-a-field-data-type.html)
   + [Adding calculated fields](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-calculated-field-analysis.html)
   + [Filtering data in Quick Sight](https://docs.amazonaws.cn/quicksight/latest/user/adding-a-filter.html)

1. If you aren't joining tables, choose whether to query the database directly or to import the data into SPICE by selecting either the **Query** or **SPICE** radio button. We recommend using SPICE for enhanced performance. 

   If you want to use SPICE, check the SPICE indicator to see if you have enough space to import the dataset. Importing occurs when you choose either **Save & visualize** or **Save**.

   If you don't have enough space, you can remove fields from the dataset or apply a filter to decrease its size.
**Note**  
The SPICE indicator doesn't update to reflect the potential savings of removing fields or filtering the data. It continues to reflect the size of the dataset as retrieved from the data source.

1. Choose **Save** to save your work, or **Cancel** to cancel it. 

   You might also see an option to **Save & visualize**. This option appears based on the screen you started from. If this option isn't there, you can create a new visualization by starting from the dataset screen.