VS Code is the ignored Excel companion I did not know I wanted

For years, I’ve considered a CSV as one thing I open in Excel. Rows, columns, filters, formulation—that is the purpose, proper? Except a CSV is not actually a spreadsheet in any respect. It’s a textual content file that Excel occurs to be superb at turning into one.

That distinction all of the sudden turned helpful once I found Visual Studio Code (VS Code). It’s a free code editor from Microsoft, however I do not use it to program. I take advantage of it alongside Excel once I must work with the CSV itself, doing issues that make a lot much less sense as soon as Excel has turned it right into a spreadsheet.

Work with CSVs larger than an Excel worksheet

Sometimes Excel is not invited

I not too long ago had a CSV file with greater than 2.2 million rows that I needed to examine earlier than doing something with it in Excel. That’s an issue as a result of an Excel worksheet tops out at 1,048,576 rows, so if I open the file as a worksheet, some knowledge is truncated. But as a result of VS Code treats the CSV as a textual content file, I may open all the file, search via it, and examine data with out hitting Excel’s worksheet restrict.

Once I’ve checked the supply file and made any mandatory adjustments, I’d use Power Query to load it into the Data Model for evaluation. VS Code lets me see and work on the entire file first; Excel can then get on with the spreadsheet work.

Change values in a number of CSVs directly

One change, each file

I used to be shocked by how helpful VS Code’s search and exchange turned once I had a number of CSVs containing the identical worth. Instead of opening every file in Excel, I may open the folder in VS Code (File > Open Folder) and press Ctrl+Shift+F to go looking throughout all of the recordsdata on the identical time. The outcomes present the filename and matching textual content, and clicking one takes me straight to that incidence within the CSV, the place I can overview or edit it.

Ctrl+Shift+H takes this a step additional, opening search and exchange throughout the folder. I can restrict the search to *.csv, test the matches within the preview, after which exchange each incidence directly. Because VS Code is looking out the textual content relatively than understanding my CSV’s columns, a alternative may have an effect on partial matches or values in different fields. If I need to be extra selective, I can open the person CSVs from the search outcomes and take care of explicit matches individually.

There’s an essential caveat: Replace All adjustments all the pieces inside the search scope, so the preview and warning are price taking critically. It additionally makes good file organization extra essential. I like preserving associated CSVs collectively whereas placing unrelated recordsdata, or completed variations, elsewhere. That method, the recordsdata I need VS Code to work on are naturally grouped inside the identical scope.

Compare two CSVs with out constructing a comparability in Excel

Find the variations in seconds

I’ve used conditional formatting and Power Query to compare datasets in Excel, and each have their place. If I’m auditing knowledge or want a repeatable comparability, I’d nonetheless go to Excel. But generally I’ve two equally named CSVs and easily need to know what modified.

VS Code has a splendidly direct reply. With one CSV open, I can open the Command Palette (Ctrl+Shift+P), select File: Compare Active File With, then choose the opposite CSV. VS Code opens them facet by facet and highlights modified, added, and deleted traces. Clicking a highlighted space takes me straight to the related a part of the file.

Excel compares datasets. VS Code compares the recordsdata themselves.

Edit a CSV with out Excel deciphering it

Make one change and depart all the pieces else alone

Changing one worth is not purpose to desert Excel. I’ve been doing that for many years. The distinction is that Excel sees a CSV as knowledge and will interpret what it finds as numbers, dates, or different data types. Sometimes that is precisely what you need, however different instances you want the supply textual content left alone.

That’s the place VS Code has a bonus. It opens the CSV as plain textual content, so 00123 stays 00123, an extended identifier equivalent to 12345678901234567890 retains all its digits, and a date-like worth stays precisely because it seems within the file. I can discover the textual content I need, change it, save the file, and depart all the pieces round it untouched.

This is especially helpful when I’m making a small correction to a CSV that one other software will learn later. I needn’t import the information, fear about how Excel has interpreted it, make my change, after which export it once more. I can edit the supply file instantly and hand it again in the identical textual content format.

Fix a CSV when the encoding is fallacious

When José all of the sudden turns into Jos�

VS Code is a helpful escape hatch when characters in a CSV look damaged. Different packages and older techniques do not at all times use the identical text encoding, so a file containing names equivalent to José or Montréal can show garbled characters when it is opened utilizing the fallacious encoding.

VS Code reveals the file’s present encoding within the bottom-right of the standing bar. Click it and select Reopen with Encoding, then choose a unique encoding to see whether or not the unique characters reappear. For instance, once I opened a Windows-1252 CSV as UTF-8, José appeared as Jos�. Reopening the file with Western (Windows 1252) restored the unique characters.

The essential factor is to not save the file whilst you’re viewing it with the fallacious encoding. If you reserve it in that state, you may write the alternative characters into the file itself, and the unique characters might now not be recoverable. Reopen it with the proper encoding first, test that the textual content seems to be proper, after which reserve it.

Excel additionally allows you to specify a file’s encoding via Data > From Text/CSV, however VS Code lets me examine and restore the supply file with out importing it first.

Excel nonetheless does the spreadsheet work

I got here to VS Code for a handful of CSV jobs, and I’ve discovered it helpful as a result of it lets me work with the file itself. Excel nonetheless does the spreadsheet work, whereas VS Code provides me one other approach to take care of the recordsdata round it. If you are new to it, it is price seeing what else VS Code can do outside of CSVs, as a result of chances are you’ll discover different methods to make it a helpful Excel companion.