Introducing RainBow Query Language for CSV, a must-have extension for VSCode!

Hello,
I'm Mandai, the Wild Team member of the development team.

CSV and TSV are convenient for transferring data because they can be easily edited in a text editor, but as the data size increases, their inconveniences become more apparent.
In such cases, we would like to introduce you to RainBow Query Language (RBQL), a great extension for VSCode.

What kind of extension is it?

RBQL is generally introduced as a tool that makes CSV and TSV easier to view in VSCode. That
's what first got me started with it.

First of all, the ease of viewing CSV files when you open them has changed dramatically, and as the name suggests, the color of the data changes for each column

The feeling of despair when you open this data with different data lengths for each line in a text editor...!
It seems like it would be worth introducing it just to be able to avoid that.

But this is just the beginning

 

A wave of useful features 1: Select all specified columns in all rows

Now, let's move on to the editing function, which uses multi-cursors to select all data in the same column position across all records.
Is this a bit too much?

I think this is a function that should have a shortcut assigned to it, but unfortunately it is not assigned by default, so you will need to assign a shortcut that suits you best

As you would expect, even if the data length is different and the starting position of the column is off, or even if it is off by a lot, it will still be selected quickly without any problems

If you want to use this function, just type "edit select" in the command palette and select "Rainbow CSV: Column edit select"

Other similar features include

• A feature that sets the cursor to the beginning of each column instead of the selected state (Rainbow CSV: Column edit before)
• A feature that sets the cursor to the end of each column instead of the selected state (Rainbow CSV: Column edit after)

There are also similar functions such as the following.
You can now choose the most suitable function depending on the situation, and it seems like you can edit data more intelligently than ever before.

Please note that this feature does not work for files with more than 1000 lines

 

A wave of useful features 2: When you place the cursor over the column, the number of columns and headers are displayed in a tooltip

"Which data is this...? 1...2...3..."
"Are you okay now?"
"Yes, I'm okay (I've lost track)."

Has this ever happened to you?

With RBQL, you no longer need to count columns!
Simply hover your mouse cursor over the data in the editor to instantly see the number of columns and header information.

Regarding header information, the information on the first line is treated as the header, so for CSV files where the data section starts from the first line, only the data on the first line will be visible

 

A wave of useful features 3: Adjust column width to align vertical starting positions

A convenient function that adjusts the starting position of the data for each row, aligning the vertical starting position and making it easier to read

This may be a useful feature when you want to check data in full screen.
However, since this function makes changes to the data itself, it is best to check whether you want to save the changes before closing.

If you want to use this function, search for "align" in the command palette and select "Rainbow CSV: Align CSV Columns."

 

Truly useful feature RBQL

We have introduced many useful features, but the truly useful feature is the SQL-like query engine called RBQL, which is also the name of the extension

If you type "RBQL" in the command palette, the command "Rainbow CSV: RBQL" will appear, so select it

This will launch another editor with a table and some input forms, like the one below

The table at the top is a table of the CSV file that the RBQL command was executed on.
You can scroll the table with the left arrow, but unfortunately you cannot scroll with the mouse wheel.

The input form below the table is the core of this screen, the form for entering queries

The CSV used in this sample has 15 columns, so they are named a1 to a15, which are what SQL calls column names.
NR is the row number automatically generated by RBQL.

SELECT a1

 

This will open a new editor with only the first line of information extracted

SELECT a1, a2

 

This will display the CSV with the first and second lines extracted

If you're familiar with SQL, you should be fine at this point.
You can even swap the order and concatenate strings using "+", so I think it will be useful in a wide range of situations.

Although we won't go into detail here, there is also a JOIN function that uses a separate CSV file, allowing for complex data combinations

The select box below the query input form is an option setting

• Select the format (csv/tsv) of the output information
• Character encoding in the query (select UTF-8 if you are using Japanese in your search)
• A programming language that executes RBQL

You will be able to select

The output format is particularly useful when combined with the "select *" query, as it makes it easy to convert between csv and tsv

Furthermore, since it supports not only SELECT but also UPDATE, you can edit data for rows narrowed down by the WHERE clause

 

summary

This time, we introduced RBQL, a great extension that makes CSV/TSV dramatically easier to use

I think this is a great extension that has a wide range of useful and convenient functions that can be used for a variety of purposes, whether you are just making small edits in the editor, checking data, or completely rewriting data

We have introduced several features, but there are actually many more that we have not covered, so we encourage you to install RBQL alongside VSCode

That's all