Merge Data from Multiple Sources in Airtable (Different Formats)

Imagine you’re handling the finances for your organization. Every month, you receive transaction data from multiple sources—your bank, PayPal, Stripe and credit card statements. Each source provides data in a different format.

Your bank’s CSV file has columns like “Transaction Date” and “Amount.” PayPal, on the other hand, labels them as “Date” and “Total.” Stripe might include special characters in the description field, while your credit card statement could have extra details like “Merchant Category.”

And this issue isn’t limited to finances. Data from almost every domain arrives in different formats:

• E-commerce: Sales data from Shopify, Amazon, and Etsy, each structured differently.

• Marketing: Performance data from Google Ads, Facebook Ads, and email campaigns.

• Surveys: Responses from Google Forms, Typeform, and SurveyMonkey.

• CRM: Customer records from HubSpot, Salesforce, and Zoho CRM.

So, how do you bring all this scattered data into one clean, consistent format?

Step 1: Define Your Target Schema

Before you do anything, decide on a standard structure for your final dataset. This is your target schema—a consistent format that ensures all data is aligned.

For example, if you're consolidating financial transactions, your target schema might look like this:

• Date: The transaction or event date.

• Amount: The monetary value (income as positive, expenses as negative).

• Description: A brief summary of the transaction.

• Category: The type of transaction (e.g., food, travel, utilities).

• Source: Where the data came from (e.g., Bank, PayPal, Stripe).

Your schema should include every field you need for analysis, regardless of how different the original data looks.

Step 2: Import Data into Separate Tables

Start by bringing each data source into its own table in Airtable. This helps keep raw data untouched while giving you room to clean and standardize it.

For example, if you're handling financial records, you might create separate tables for:

• Bank Transactions

• PayPal Transactions

• Stripe Transactions

• Credit Card Transactions

Step 3: Normalize the Data

Now, it’s time to transform your data so everything fits your target schema.

Start by mapping fields to ensure consistency. In financial data, “Transaction Date” might need to be standardized as “Date,” and “Amount Paid” could be renamed to “Amount.”

If a source is missing a necessary field, create one. For example, Stripe transactions may not include a “Category” column, so you can add one to classify expenses like “Groceries,” “Dining,” or “Entertainment.”

Leverage formulas to clean and standardize data. For example:

• Merge payee names: If your data has separate "First Name" and "Last Name" fields, use {First Name} & " " & {Last Name} to create a full name field.

• Convert transaction dates: If dates come in different formats (e.g., MM/DD/YYYY vs. YYYY-MM-DD), use DATETIME_PARSE({Transaction Date}, "MM/DD/YYYY") to standardize them.

• Extract merchant names: If descriptions contain extra details (e.g., "Amazon Purchase - Order #1234"), use LEFT({Description}, FIND(" - ", {Description}) - 1) to extract just "Amazon Purchase".

• Classify transactions: If categories are missing, use conditional formulas like IF(FIND("Uber", {Description}), "Transportation", "Other") to auto-assign categories.

Step 4: Consolidate into a Single Table

Once all your data is cleaned up, you can either do it manually or automate the process to bring everything into one master table.

• Manual Import: Export each cleaned table as a CSV and import it into the master table.

• Automation: Use Airtable’s Automations to move records from the source tables to the master table automatically.

Now that all your data is in one place, you can create filters, views, and dashboards to spot trends and make decisions.