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.
Table of Contents
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,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?
In order to search or replace text in Google Sheets, click Edit -> Find and Replace.
The find and replace dialog will open. You can specify the text to search for and replace with here.
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.
In Microsoft Excel go to Home -> Find & Select an action you want to do: Find or Replace.
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.
In the Replace dialog, you can perform a simple search. In addition, you can also specify the text to replace the found one.
UltraEdit offers powerful options for searching and replacing tabular text. Go to the Home section and select the desired action: Find or Replace.
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.
The search and replace dialog looks like this.
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?
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.
In the menu that opens, choose the sort order (A to Z or Z to A).
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.
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.
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.
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).
To sort by several columns, select the required range and click the 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.
Finally, click OK and the table will be sorted.
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.
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.
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.
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.
- Database Editors vs Text Editors - March 3, 2023
- G2 Enterprise Awards: UltraEdit 2022 - February 22, 2023
- UltraEdit: Keyboard Shortcuts Or Mouse? - February 16, 2023