Editing CSV and Other Delimiter-Separated Files as Tables
Most of the functions in the table editor are accessed as context menu commands. Many of the commands have dedicated keyboard shortcuts.
Note that the context menus for the header row and the rest of the table are different.
For the table editor and associated features to be available:
- The Database Tools and SQL plugin must be enabled. (This plugin is bundled with the IDE and enabled by default.)
- The file name extension must be associated with the text file type. See e.g. File Types.
Opening the table editor
You can open the table editor for a whole file or for its fragment.
- Open the file of interest in the editor.
- If you want to open the table editor for a fragment, select that fragment.
- Select Edit as Table from the context menu.
- In the dialog that opens, specify conversion setting and click OK.
You can sort table data by any of the columns by clicking the cells in the header row.
Each cell in this row has a sorting marker in the right-hand part and, initially, a cell may look something like this: . The sorting marker in this case indicates that the data is not sorted by this column.
If you click the cell once, the data is sorted by the corresponding column in the ascending order. This is indicated by the sorting marker appearance: . The number to the right of the marker (1 on the picture) is the sorting level. (You can sort by more than one column. In such cases, different columns will have different sorting levels.)
When you click the cell for the second time, the data is sorted in the descending order. Here is how the sorting marker indicates this order: .
Finally, when you click the cell for the third time, the initial state is resorted. That is, sorting by the corresponding column is canceled: .
Hiding and showing columns
To hide a column, right-click the corresponding header cell and select Hide column.
To show a hidden column:
- Do one of the following:
- Right-click any of the cells in the header row and select Column List.
- Press Ctrl+F12.
In the list that appears, the names of hidden columns are shown struck through.
- Select (highlight) the column name of interest and press Space.
- Press Enter or Escape to close the list.
Transposing the table
The transposed table view is available. In this view, the rows and columns are interchanged.
To turn this view on or off, click on the toolbar and select Transpose. Alternatively, use the Transpose context menu command.
Enabling coding assistance for a column
You can assign a column one of the supported languages (e.g. SQL, HTML or XML): right-click the corresponding header cell, select Edit As and select the language. As a result, you get coding assistance for the selected language in all the cells of the corresponding column.
You can also assign a language to an individual cell.
Modifying cell contents
- To start editing a value, do one of the following:
- Double-click the corresponding table cell.
- Right-click the cell and select Edit or Edit Maximized from the context menu.
- Select the cell and press F2 or Shift+Enter. In the latter case, the cell will be maximized.
- Select the cell and start typing. Note that in this case the initial cell contents are deleted right away and is replaced with the typed value.
- When in the editing mode, you can:
- Modify the value right in the cell. To start a new line, use Ctrl+Enter. To enter the value, press Enter. To restore an initial value and quit the editing mode, press Escape.
- Use value completion. Press Ctrl+Space to open the suggestion list. The list contains the values from the current column that match your input.
- Maximize the cell if you need more room for editing. To do that, press Ctrl+Shift+Alt+M, or right-click the cell and select Maximize.
When working in a maximized cell, use Enter to start a new line and Ctrl+Enter to enter the value. To restore an initial value and quit the editing mode, press Escape.
- Insert the contents of a text file into the cell. To do that, right-click the cell and select Load File. Then select the necessary file in the dialog that opens.
- Edit a value in the cell as a fragment in one of the supported languages (e.g. SQL, HTML or XML). To do that, right-click the cell, select Edit As and select the language. As a result, you get coding assistance for the language you have selected.
Adding and deleting rows and columns
Use the following context menu commands and shortcuts:
- Add New Row (Alt+Insert).
- Delete Row (Ctrl+Y). To delete more than one row at once, first, select the corresponding rows or cells in the corresponding rows.
- Clone Row (Ctrl+D). This command creates a copy of the current row.
- Add New Column (Shift+Alt+Insert).
- Delete Column (Shift+Alt+Delete). To delete more than one column at once, first, select the cells in the corresponding columns.
- Clone Column (Ctrl+Shift+Alt+D). This command creates a copy of the current column.
Copying data to the clipboard or saving them in a file
- Use one of the following context menu commands:
- Copy (Ctrl+C). This command copies the data for the selected cells to the clipboard.
- Dump Data | To Clipboard. This command copies the data for the whole table to the clipboard.
- Dump Data | To File. This command saves the data for the whole table in a file. Before actually saving the data, the dialog is shown which lets you select the output format and see how your data will look in a file.
- If you are saving the data in a file, specify the file name and location.
See also, Specifying data output format and options.
Specifying data output format and options
To specify the output format and options for the Copy and Dump Data commands (see Copying data to the clipboard or saving them in a file), right-click the table and point to Data Extractor: <current_format>.
In the menu that opens, the output formats are in the upper part: SQL Inserts, SQL Updates, etc. (The options that look like file names are also the output formats or, to be more exact, the scripts that implement corresponding data converters.)
The output option are:
- Allow Transposition. This option affects only delimiter-separated values formats (TSV, CSV). If the table is shown transposed and you are copying selected cells or rows to the clipboard (e.g. Ctrl+C), the selection is copied transposed (as shown) if the option is on and non-transposed (as in the original table) otherwise.
- Skip Generated Columns (SQL). This is the option for SQL INSERTs and UPDATEs. When on, auto-increment fields are not included.
- Add Table Definition (SQL). This is also the option for SQL INSERTs and UPDATEs. When on, the table definition (CREATE TABLE) is added.
- Configure CSV Formats. This command opens the CSV Formats dialog that lets you manage your delimiter-separated values formats (e.g. CSV, TSV).
- Go to Scripts Directory. This command lets you switch to the directory where the scripts that convert table data into various output formats are stored.
Exporting the data to a database
You can export the data to a database (your database must be defined as a data source):
- Select Export to Database from the context menu.
- Select the target schema (a new table will be created) or table (the data will be added to the selected table).
- In the dialog that opens, specify the data mapping info and the settings for the target table.
- Importing delimiter-separated values into a database