Common Data Transformations After Importing Data

Hey there! In this guide, we'll go through the process of importing data from external sources into your Xano database and performing common data transformations to ensure your data is structured correctly. Whether you're importing data via CSV or API, these steps will help you get your database up and running smoothly.

Importing Data via CSV

Let's start by importing a CSV file containing information about cities in the United States.

1. Create your table: First, create a new table in Xano. It's recommended to match the field names in your table to the column headers in the CSV file for easier mapping.
2. Import the CSV: Use the `CSVStream` function to import your CSV file into the table you just created. This function is designed to handle large CSV files efficiently.

• Add a `File Resource` input to provide the CSV file.
• Add the `CSVStream` function and connect the file input as its value.
• Loop through the items in the CSV using the `add record` function and reference the `item` variable to map the CSV data to your table fields.

1. Conditional Import: If you only want to import a specific number of records (e.g., the first 1,000), you can add a conditional statement to limit the import.

After importing, you should see your data populating the table in Xano.

Creating Unique Value Tables

Often, you'll want to create separate tables for unique values within a column, such as state names. Here's how to do it:

1. Create a new table: Create a new table (e.g., `states`) with a single field (e.g., `state_name`).
2. Query unique values: Query all records from your original table (e.g., `cities`) and apply the `unique` filter to retrieve only the unique values from the desired column (e.g., `state_name`).
3. Loop and add records: Loop through the unique values and add a new record to the `states` table for each unique value.

Now you have a separate table with only the unique state names, which you can use for creating table references.

Creating Table References

To establish relationships between your tables, you'll want to create table references. Let's create a reference from the `cities` table to the `states` table:

1. Add a table reference field: In the `cities` table, add a new field as a table reference to the `states` table (e.g., `state_id`).
2. Query tables: Query all records from both the `cities` and `states` tables.
3. Loop and find matches: Loop through the `cities` records and use the `find first element` function to find the matching state record based on the `state_name` field.
4. Add record with reference: Add the record to the `cities` table, populating the `state_id` field with the ID of the matching state record.

After running this process, your `cities` table should have the correct state references populated.

Creating Table Reference Lists

In some cases, you may want to store a list of table references instead of a single reference. For example, you might want to store all cities for each corresponding state. Here's how to do it:

1. Create a list field: In the `states` table, create a new field as a list of table references to the `cities` table (e.g., `cities_ids`).
2. Query tables: Query all records from both the `states` and `cities` tables.
3. Loop and find matches: Loop through the `states` records and use the `find all elements` function to find all matching city records based on the `state_name` field.
4. Add record with reference list: Add the record to the `states` table, populating the `cities_ids` field with the list of IDs for the matching city records.

Now, each state record will have a list of city IDs associated with it.

Transforming Data Types

Xano supports various data types, including geography points, arrays, and objects. Let's look at how to transform your data into these formats:

1. Geography Points: If you have latitude and longitude values stored as text, you can convert them into a geography point type using the `geography point` function. Create a JSON object with the required structure (`{ "type": "Point", "coordinates": [longitude, latitude] }`) and populate it with your data.
2. Arrays from Text: If you have arrays stored as text (e.g., a list of zip codes separated by spaces), you can transform them into actual arrays using the `split` filter. Simply apply the `split` filter to the text field, providing the appropriate separator (e.g., a space).
3. Objects from Text: If you have key-value pairs stored as text (e.g., `"county_name": "New York", "population": 8804190, "time_zone": "America/New_York"`), you can convert them into objects using the `json decode` filter. This filter will transform the text into a valid JSON object.
4. Object Arrays from Text: If you have a list of objects stored as text, you can convert them into an array of objects using a combination of the `json decode` filter and looping through the records. Apply the `json decode` filter to the text field, then loop through the records and store the resulting objects in an object array field.

By following these steps, you can import data from external sources, create relationships between tables, and transform your data into the desired formats within your Xano database.