DEV Community

Judy
Judy

Posted on

2 1 1 1 1

Parse a csv file where field values are enclosed by quotation marks and contain carriage return #eg35

Here is a csv file. Its 3rd column contains strings of a special format – they are enclosed by double quotation marks and some occupy multiple rows (which means they contain carriage return).

Image description
Task: Use Java to reorganize the csv file as a standard two-dimensional table.

Image description
Write the following SPL statement:

=file(""data.csv"").import@tcoq().run(description=replace(description,""\n"",""""))

import() function parses the csv file as a two dimensional table; @t option enables importing the 1st row as column names, @c option enables using commas as the separator, @o option handles the unescaped carriage return in lines, and @q option enables removing quotation marks at both sides before parsing. run() function modifies the two dimensional table and return the modified table.

Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.

This is one of the problems on StackOverflow. You can click on it to see that the conventional solution is quite complicated, but the SPL approach is really simple and efficient.

SPL open source address

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more