Using AI to Transform Unstructured Data Into Structured Data

Learn How to Use AI to Transform Unstructured Data and Store It In a Relational Database

As developers, we often encounter unstructured data that needs to be stored in a structured format, such as in a relational database like MySQL. But how do we transform messy, unstructured data into organized, searchable records? Structuring data requires defining patterns and relationships, a process now simplified with AI and large language models (LLMs). These tools can interpret unstructured inputs and return neatly formatted data.

In this tutorial, we’ll walk you through a straightforward 5-step process to convert raw unstructured data, such as text into structured data, and store it in a relational database.

Transform Unstructured Data to Structured Data: Sample Application

Before we start, check out our sample application for analyzing doctor’s notes. The application extracts prescriptions, dosage, and reasons for the prescription automatically from an unstructured note (a text input).

Here’s how it works exactly:

The sample application accepts unstructured text input, such as this doctor’s note:

 /* 
To whom it may concern,

John Doe was seen in my office today for complaints of mild seasonal allergies. He has been prescribed Loratadine 10 mg to be taken once daily for a period of 14 days.

The patient is advised to drink plenty of fluids and avoid allergens where possible. If symptoms persist beyond two weeks, a follow-up visit is recommended.

Thank you for your attention.
*/ 

It then transforms this text input into structured data stored in a MySQL database.

You can give this a go yourself:

1. Open the sample application.
2. Click on Doctor’s Notes > Yellow Plus Button and fill in the form. Paste the doctor’s note from above into the large text box, as shown here:

3. Next, click Generate. This sends the doctor’s note to OpenAI for analysis.
4. OpenAI returns a response, which is stored in a SQL database table which has five fields:

1. Date: This is automatically set to today’s date.
2. Doctor: This field stores the doctor’s name.
3. Reason: This field is populated by the AI’s response.
4. Prescription: This field is populated by the AI’s response.
5. Dosage: This field is populated by the AI’s response.

5. To view the database record that now stores structured data, click Medical Info and select your record from the list. Here’s what the record for our sample doctor’s note now looks like:

In short, through the use of AI, we went from a lengthy text input that didn’t have any structure to neat, structured data that we can now use for further analysis or reporting. For example, we could now create an email template that is automatically sent out to patients with just the reason, prescription, and dosage. Or we could write an SQL query to view the most prescribed medicine in the last 3 months.

Once we’re dealing with structured data, we have much greater control over our data.

Build Your Own AI Application
Rapidly build and deploy your application today

How to Transform Unstructured Data to Structured Data: Tutorial

To build an application like this, follow the steps below. The tool we are using is Five, a rapid application development environment that allows us to:

1. Model a SQL database;
2. Auto-generate a user interface; and
3. Write JavaScript functions to invoke the AI agent.

1. Sign up for a free trial of Five’s development environment.

2. Model your database.
If you’re not sure how to model a database in Five, follow our series of blog posts on database modeling. Remember, the database is what structures your data. So if you plan to extract three, five, or seven pieces of information from an unstructured input, you should have three, five, or seven database fields.

3. Create your user interface.
All we need is a form. Use Five’s Form Wizard to do so.

4. Add the JavaScript functions to prompt your LLM.
We need two functions: one that runs on the client and one that runs on the server.

The client-side function holds the fields sent to AI as variables. Here’s what our client-side function for the sample app above looks like:

function OpenaiClient(five, context, result)  {
    const entry = five.field.Entry;
    const date = five.field.Date;
    const doctorName = five.field.DoctorName;

    const variables = {};
    variables["Entry"] = entry;
    variables["Date"] = date;
    variables["DoctorName"] = doctorName;

    five.executeFunction("OpenaiServer", variables, null, null, null, function (result) {

    })


    return five.success(result);
}

Attach the client-side function to the Generate button by triggering an On-Click event.

Our server-side function does the heavy lifting and contains our AI prompt. Don’t forget to provide your API key in line 3.

function OpenaiServer(five, context, result)  {
    five.log("Hello from backend");
    const apiKey = 'Your API Key'
    const client = five.httpClient();
    client.addHeader("Authorization", `Bearer ${apiKey}`);
    client.addHeader('Content-Type', 'application/json');
    client.setContentType("application/json");

    const messages = [
        {
            role: "developer",
            content: `You are a text scraper. Give me three JSON items: the medicine, the dosage, and the reason. Return the result as JSON.`,
        },
        {
            role: "user",
            content: `${context.Entry}`,
        },
    ];

    const parseData = {
        model: "gpt-4o",
        messages: messages
    }

    client.setContent(JSON.stringify(parseData));

    let results = client.post("https://api.openai.com/v1/chat/completions")
    const content = results.response.choices[0].message.content;
    const jsonString = content.replace(/```json\n|\n```/g, '');
    const parsedResponse = JSON.parse(jsonString);
    const { medicine, dosage, reason } = parsedResponse;
    const sql = "INSERT INTO MedicalInfo (MedicalInfoKey, Prescription, Dosage, Reason, Date, Doctor) VALUES (?,?,?,?,?,?)";
    five.executeQuery(sql, 0, five.uuid(), medicine, dosage, reason, five.now(), context.DoctorName);


    return five.success(result);
}

5. Test the application.
Once you’re done developing, remember to test the application so that it works as intended.

Accelerate Your Development: If you get stuck at any of these steps, contact our team of developers by posting on our user community.

Recap: Transform Unstructured Data to Structured Data

Required Tools

To transform unstructured text data like a doctor’s note into structured data suitable for a database, follow these steps:

1. Data Ingestion: Import the doctor’s note into a processing tool or application.
2. Text Parsing and Analysis: Use AI or natural language processing (NLP) to extract key fields such as prescription name, dosage, and duration.
3. Data Mapping: Match parsed data to predefined database columns.
4. Data Storage: Insert structured data into a relational database for querying and analysis.

Tools like Five simplify this process for rapid application development. With Five, you can build a simple AI-driven application that transforms unstructured data into structured data for any use case, industry, or field. Just follow the steps above.

Structuring Your Data For Better Data Management

In conclusion, transforming unstructured data into structured data enables better organization, searchability, and analytical insights. Approaching this challenge with AI and low-code tools provides significant efficiencies in data processing, particularly for complex text like doctor’s notes or business documents.

Key takeaways include leveraging natural language processing for data extraction, mapping information to predefined schemas, and ensuring data integrity within a relational database. Adopting structured storage solutions enhances decision-making, reporting, and automation. Ultimately, this approach empowers developers and organizations to unlock value from previously untapped data.