How To Work Effectively With CSV Files In Tabular Text Editors

January 21, 2023
How To Work Effectively With CSV Files In Tabular Text Editors

CSV files are often used for various types of business, as they allow you to easily structure, store and process large volumes of data. However, for effective work with these files, it is important to choose a CSV text editor that allows you to easily and quickly process tabular data. In this article, we will talk about the most common actions that need to be performed on a CSV file, and how to perform them using popular tabular text editors.

What is tabular text data?

Tabular text data is structured data that is stored as rows and columns and can be displayed as a table. Columns can have headers that explain what data they contain.

Why are CSV files used?

A CSV (comma-separated values) file is a plain text file that has .csv extension. It contains tabular data and separates it with delimiters (usually commas, but you can use other delimiters). It is widely used due to its simple adaptability. This means that almost any spreadsheet program can easily open it and read data from CSV files.

Sample CSV file looks like this:

Barry French,12,457.81,Nunavut,Appliances,0.58
Barry French,12,2.99,Nunavut,Binders and Binder Accessories,0.39
Clay Rozendal,483,3.99,Nunavut,Telephones and Communication,0.58

Using CSV file format is a simple way to transfer data because it is easy to create and open with many programs. CSV format is often used to export data to a database and to exchange structured information between programs that cannot communicate with each other directly.

Let’s see how to search and replace text in columns and how to sort tabular data using popular text editors.

How to find and replace text in columns?

Google Sheets

In order to search or replace text in Google Sheets, click Edit -> Find and Replace.

find and replace in csv file

The find and replace dialog will open. You can specify the text to search for and replace with here.

find and replace in csv file

After you write the text to search for and press Find, the cell with the found text will be highlighted. If the text is not found, a corresponding message will appear. You can replace the text in the selected cell. To do this, fill in the Replace with field and click the Replace button. If you want to replace all the text found, click Replace all.

Microsoft Excel

In Microsoft Excel go to Home -> Find & Select an action you want to do: Find or Replace.

find and replace in csv file

After that, the corresponding dialog box will open. Enter the text to search for and click Find next so that the first cell with the found text is highlighted in the table. Click Find all to select all cells that contain the specified text.

find and replace in csv file

In the Replace dialog, you can perform a simple search. In addition, you can also specify the text to replace the found one.

find and replace in csv file

UltraEdit

UltraEdit offers powerful options for searching and replacing tabular text. Go to the Home section and select the desired action: Find or Replace.

find and replace in csv file

In the dialog box, specify the text you want to search for and move between the fields that contain matches using the Next and Previous buttons. You can also find out the number of all matches by clicking on Count all.

You can also use additional search settings and regular expressions.

find and replace in csv file

The search and replace dialog looks like this.

find and replace in csv file

Specify the text to replace with and use the Replace button to replace the currently found text and Replace all to replace all matching text.

How to sort tabular data by field?

Google Sheets

If you want to sort the data in the Google Sheet by one column, click on the button in the column name to call up the drop-down menu.

sort tabular data by field

In the menu that opens, choose the sort order (A to Z or Z to A).

sort tabular data by field

You can also sort data by multiple columns. To do this, first, select the range to sort. Then, in the menu select Data -> Sort range -> Advanced range sorting options.

sort tabular data by field

After that, a dialog box will open in which you can add columns to be sorted by and define their order. You can define the sorting method (A to Z or Z to A) separately for each column.

dialog box opened to add columns to be sorted by and define their order

If your table has headers, check the corresponding checkbox in the dialog box and specify the headers instead of column names for sorting. After you have configured all the sorting options, click the Sort button and the table will be sorted.

Microsoft Excel

With Microsoft Excel, you can sort tabular data by a single column or by an entire range, just like we did in Google Sheets. To sort by one column, select it and click the appropriate button in the menu bar (Sort Ascending or Sort Descending).

Microsoft Excel screen to sort tabular data by a single column or by an entire range

To sort by several columns, select the required range and click the Custom Sort menu button.

Microsoft Excel - Custom Sort menu button

In the dialog box that opens, add the columns by which the sorting will take place. If the table has headers, then check the My data has headers checkbox and select the headers of the required columns. If not, then leave the checkbox unchecked and select the columns by their name.

dialog box opened to add the columns by which the sorting will take place

Finally, click OK and the table will be sorted.

UltraEdit

In order to sort tabular data by the first column in ascending order in UltraEdit, just click the Sort button in the menu bar under the section Edit.

UltraEdit - Sort button in the menu bar under the section Edit.

If you want to sort in a different way, click on the drop-down menu button under the Sort button, and select the Advanced sort/option.

UltraEdit Sort button

After that, a dialog box will open in which you can make all the necessary settings:

  • Select the sort order – Ascending or Descending.
  • Remove duplicates when all or all keys match.
  • Configure case-insensitive sorting.
  • Set up a custom separator.
  • Select the column numbers by which the sorting will take place.

UltraEdit Advanced Sort-Options dialog box

If you need to sort by only part of the field and ignore the other part, fill in the values in the Start character (the number of the first character to be considered when sorting) and End character (the number of the last character to be considered when sorting) fields.

Looking for the best text editor for working with CSV files? Download a 30-day trial version of UltraEdit for free.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Recent Posts

Latest News

Subscribe to Our Newsletter