Mastering CSV with VS Code

Working with delimited data sets can be kind of tricky. Whether you've got commas and maybe the comma could exist within a string value, or you've got, you know, worried about the number of columns on the row. There's just so many things that can go wrong and it's not always the easiest to work with them. Today's extension aims to solve that. So let's get to it.

Hello world, my name is Michael Jolley and I'm the bald bearded builder. I love talking about Versus Code extensions and today's is called Rainbow CSV. I kind of feel like the name of it needs a disclaimer because it's not just CSVs. This extension can help with any pretty much all delimited files, whether it's tabs or types or semicolons. And then in our case, we're gonna play with commas, but you know, there's so many out there and this can do it without any of them.

It really aims to make the life dealing with those easier in Versus Code. Before we get into the weeds with it though, let me remind you that three days a week on Tuesday, Wednesday, and Thursday, you can find us live on Twitch at 2PM Eastern. We'd love to have you hang out with us. It's twitch. Tvbaldbeardedbuilder.

Okay, how do we get started with it? First, I've left a link in the description if you wanna read more about it and see some of the customization options. I'm not gonna touch on nearly any of them. There's a ton. You can go read that if you wanted to know more.

But the easiest way to install any extension is to click that extensions icon in Versus Code, search for Rainbow CSV in this case and press the install button. So as soon as it's installed and you open up a CSV file, you're gonna notice why it's called Rainbow CSV.

Columns are automatically color coded so that you can quickly see your data and where things line up and what field this is in versus other fields, which is kind of hard when you have a really long CSV file. You'll also notice that there's a great hover component. When you mouse over any type of piece of data, it tells you not only like the value of that data, but it tells you what column it's in, which is fantastic. And then finally, I love this. It links all of that CSV for us.

So it makes sure that we're using apostrophes or quotation marks correctly, which can be a real gotcha in CSV. And also make sure that all of those rows have the appropriate number of columns. If one's missing, it kind of lets you know so that you can go see what happened here. Did I miss something up or whatever.

It handles all that for us. If you're gonna be playing with a CSV file in this very much, you're gonna wanna specify the headings on your file, if they exist.

The easiest way to do that is to put your cursor on the line that contains your headings. It's usually the first few lines and then use the command palette. You'll search for rainbow CSV set header line And then that will record that and just kind of keep it in memory for you. It doesn't change your file at all. But then when you do the hover commands, you'll see not only like the column name or the column like value column zero or column one, you'll now see the column name that you had in those header columns.

Anytime you're working with a delimited file CSV in our case, it can be hard to read that data, especially if the columns contain texts, because then the data is just kind of out of alignment, right? You can't really tell where one starts and one ends, you know, good luck if it has commas in some of the values, it gets really tricky. That's where this next feature comes in really handy. If you use the command palette and search for rainbow CSV, align CSV columns, that's what it's called. That will align, it'll add white space to align every column that makes it really easy to read more like a spreadsheet.

You gotta be careful though, because if you save the file at this point, it will save that white space in the file, which maybe you like that, maybe you don't. If you don't be careful, but don't worry if you accidentally do save it, you can actually use another command palette called, I think it's rainbow CSV, shrink CSV table. That's it. So do that. It'll remove all that white space and bring it back to normal for you.

If you've ever opened a CSV and tried to search through it for some value, boy, that hits me right in the fields. I feel for you. It can be really hard and difficult, especially if it's a large file where the value could show up multiple times. That's where this next feature makes this extension invaluable if you're using delimited files.

And they use this feature called RBQL which is basically like a SQL like syntax for searching this delimited file. It's amazing. So to get to it, use the command palette and search for rainbow CSV RBQL. That's gonna open a new little window and you'll see kind of a sample of like maybe the first 10 lines of your data. And it'll have a field for query you wanna run.

And under that will be some options like exclude the header row. There's some other options there you can check out yourself. But that query, can search for, you know, show me all where, you know, column two equals, you know, blah or whatever. And it will allow you to run that query and it'll actually open a new window with a similarly delimited file with just the results of that query, which makes it a lot easier to search these data sets that are really, you know, get a data set with 50,000 records and you're searching for Smith, oh man, you could be jumping all over the place. So this really like brings it all into view so you can peruse them all and get it just right.

One thing that's really nice about that feature in that RBQL is it allows you to specify what columns. So you don't have to get the whole row back out for that record that matches. You could say, just give me column one and column five. And it'll put that out there, pretty slick. You have to work with delimited files, comma, tabs, any of that very often.

I used to a lot more than I do today, but when I do, I am so glad this extension is here to help me work with it faster. If you use, let me know how you're using it and what you're using it for in the comments below. And if you liked this video, be sure to hit that like button. If you enjoy content like this and learning more about Versus Code extensions, be sure to subscribe and hit that bell to know when we release a new video. I'll catch you next time.