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.

3. 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.