How to use OpenAI's ChatGPT to clean up data

About Rows

Rows is the easiest way to import, transform, and share data in a spreadsheet. It combines a spreadsheet editor, +50 integrations with the tools you use every day, a powerful AI Analyst✨, and a sharing experience to instantly turn any spreadsheet into a web app, a form, or a dashboard.

Connect the OpenAI integration

To connect the integration, open a new spreadsheet, and select the OpenAI integration on the welcome side panel. Alternatively, you can search for the integration inside the Data actions panel.

Inside the Data Actions panel, search for "Apply task" and select the corresponding OpenAI action:

Finally, Connect the integration to get started.

The Free plan includes 20 free uses of the OpenAI integration. Users on the Plus or Pro plans have unlimited access to OpenAI and can use their API key to access any OpenAI model, including fined-tuned models. By default, the OpenAI integrations use the "gpt-4o" model.

Use the clean-up action

Once the integration is connected, you can start using OpenAI in Rows to clean up data.

Alternatively, you can use the Apply Task function directly in the spreadsheet. Type =APPLY_TASK_OPENAI to see the autocomplete.

All OpenAI functions need to be configured through mandatory and optional parameters, depending on their purpose. Let's go through them.

Task

The apply task function requires the task as the first parameter. This is simply the task you want to perform on your text.

You can write the text directly inside the text field in the action wizard, or reference any cell in the table by pointing to it on the editor.

Text

The second parameter is text and represents the text you want to apply your task on.

The remaining parameters are all optional and commonly used for advanced use cases. Learn more about them in the Sentiment analysis function documentation.

Examples of data cleansing

There is a near-infinite amount of ways you can use OpenAI to clean up existing data. For example: removing unnecessary text or punctuation, converting date type, or clear formatting.

Let's go through some of them.

Clean up Company names

Goal

Clean up a list of company names by removing legal abbreviations.

Example

1=APPLY_TASK_OPENAI("Remove legal entity abbreviations like GmbH, LLC, Inc., emojis, special characters and unnecessary text from ",A2)

Details Assumes that A2 contains the company name.

Trim whitespaces

Goal

Trim unnecessary whitespaces from a cell.

Examples

1=APPLY_TASK_OPENAI("Remove unncessary whitespaces",A2)

Details

Assumes that A2 contains the text to clean up.

Capitalize words

Goal

Correctly fix capitalization in a list of words.

Example

1 =APPLY_TASK_OPENAI("Capitalize all the words",A2)

Details

Assumes that A2 contains the original text to be capitalized.

Clear formatting

Goal

Clear formatting and remove punctuation from the text.

Example

1 =APPLY_TASK_OPENAI("remove any punctuation and capital letters, replace spaces with _",A2)

Details

Assumes that A2 contains the original text to be capitalized.

Convert date format

Goal

Convert dates to yyyy/mm/dd format.

Example

1 =APPLY_TASK_OPENAI("Convert to YYYY/MM/DD",A2)

Details

Assumes that A2 contains the original dates.