CSV to API: How to Ingest Clean CSV Files Without Writing Code

Cross-company data sharing is on the rise. Many enterprise companies are collecting data from over 100 SaaS solutions and services. Chances are your team is overwhelmed with the sheer volume of data from different sources and formalizing it into a standard, usable format. 

Much of that data is probably coming from CSVs as that’s one of the most common formats for sharing data. And for good reason. CSVs are accessible across industries, use cases, and technical abilities. With CSVs having been around for decades, everyone has come across CSV data at some point. 

APIs are something we interact with frequently and have become interwoven into almost every application we use. And your team is certainly no stranger to building and using APIs in the products you work so tirelessly to bring to market. 

At Osmos, we’ve made it easier to ingest clean external data without writing code. Connecting APIs to Osmos allow you to import clean data to any SaaS application. In this blog, we’ll walk through the steps of importing data from a CSV via API.

What is an API?

As we all know, data generates insights that help us make better business decisions. For the last decade, businesses have been compiling, storing, and organizing their data in order to gain useful insights. With the evolution of cloud technologies, businesses are no longer storing this data in-house. Instead, they're turning to the cloud to store and organize this data. With today's digital landscape, data is being ingested from various sources and channeled through various applications such as marketing platforms, sales tools, and CRM's. This is done primarily through APIs.

An Application Programming Interface (API) is a type of software interface containing rules that dictate how applications communicate with each other. An API is essentially a middleman between two applications to help exchange information.

Some examples of APIs:

• Food delivery apps using Google Maps to show where your food is on a map
• Google showing you restaurant hours without you having to visit the restaurant’s website
• Embedding tweets into news articles 
• Paying with PayPal while online shopping
• Your calendar automatically updating based on new emails with event details

Benefits of APIs:

• APIs add additional security preventing your devices from interfacing directly with the server
• APIs save time for technical teams, so they don’t have to reinvent the wheel for every aspect of their app
• APIs encourage innovation among developers and allow them to focus on bettering their key product
• APIs offer a better experience for your end users as the integrations make for a more cohesive UX 

APIs exist to make our lives–and the lives of your internal teams–much easier. And when processes are made easier and simplified, you save time and money to focus on what’s really important: building a better product. 

We all know moving data is slow, tedious, and expensive. Most businesses rely on a lot of data sources; however, extracting that data and synchronizing it with their internal systems requires a lot of manual work, which is a huge bottleneck in the process.

Using APIs to ingest data allows you to migrate data into your operational systems quickly and securely, all while providing a seamless and delightful experience.

Learn more about the Osmos HTTP API super connector.

Import and Cleanup CSV Data via API with Osmos Pipelines

Step 1: Determine where your data is coming from

Customers and partners all have their own methods of sharing their data. Some like to send CSVs through emails, others share FTPs of CSVs, and some may even share a Google Drive link. CSVs can live in any number of places. Osmos Source Connectors give your customers the flexibility to share CSVs how they’re comfortable.

Step 2: Select where your data is going

You've already decided to import your CSV file via API, but Osmos offers additional flexibility with two options for this.

1. Call an Osmos API: we create an endpoint for you

Calling an Osmos API allows you to send data to a destination that you can then pick up where it’ll arrive cleaned and ready for use. Your system will essentially knock on the door of the API to see when new data is available and ready to be ingested in your system.

Choose this option if you want an additional layer of security as calling an Osmos API makes it so you don’t have to open firewalls or worry about authentication methods.

• To set this up, select HTTP API when building a Destination Connector.

• Give your Destination Connector a name.
• Select “Call an Osmos API”.

• Build the schema for this endpoint by setting up the necessary parameters.
• Congrats! You’ve created an API endpoint to send clean data.

2. Osmos calls your API: you have an endpoint already created

Allowing Osmos to call your API lets us send you your cleaned data based on your designed schema. This option is configurable to your specifications. Check out how to build your schema below and share it with us below:

• To set this up, select HTTP API when building a Destination Connector

• Give your Destination Connector a name
• Select “Osmos Calls Your API”

• Include the URL of your API
• Select your HTTP Method and include any HTTP Headers that we should use when sending data
• Choose how data is sent to your API by writing options. We support batched records and one record at a time.
• Build the schema for this endpoint by setting up the necessary parameters
• Congrats! You’ve created an API endpoint that you can now send clean data to

Step 3: Map and transform your data

In step 1, we established the data source. In this step, we will validate, map, and transform CSVs into your golden shape so you ingest clean, usable data.

Traditionally, this step is one of the biggest headaches as it requires your team to be pulled from important projects to manual data cleansing using Python scripts or Excel. This process can take weeks, but with Osmos, you can quickly clean, validate, and format incoming data–no code required!

To map and transform data, you’ll start by mapping the columns of the imported data to your destination columns. The system will detect patterns to AutoMap column headers wherever possible. 

Next, you can choose the following transformations to cleanse the incoming data:

• QuickFixes: One-click, data cleanup for the most common formatting scenarios for the specified data type (i.e. changing date formats from MM-DD-YYYY to DD/MM/YYYY)
• AutoClean: Bulk edit data after providing the system a few examples of the desired output (i.e. extracting a First Name from a column that has Last Name, First Name)
• Formulas: Familiar spreadsheet-like formulas that can be used for complex data transformations based on one or more columns

Step 4: Automate importing and cleaning CSVs via API

Now that you’ve transformed and cleaned data faster than ever before, it’s time to schedule your Osmos Pipeline. This allows cleaned CSVs to flow from its external source to your operational systems automatically. Choose how often you want the Osmos Pipeline to run by scheduling to run down to the minute giving you complete control over your incoming data.

Don't Let CSV Ingestion Slow You Down

And just like that, you’re able to import CSVs into your operational systems via an API–all cleaned up and ready to go without writing code. Say goodbye to writing and maintaining Python scripts, manual data cleaning in spreadsheets, and hello to automated data cleanup with AI-powered data transformations.

One platform, multiple use cases

1. Low-Code Data Migration
2. Data Onboarding