Text Grid help

What is Text Grid & why?

Text Grid is a tiny and practical grid editor for delimited text files. It runs on any Windows computer (requires .NET framework). It can also edit standard plain text files.

It can also open plain text file with each line as row in a single column interface.

Text Grid does not replace spreadsheet (e.g. Excel) or text editor (e.g. Notepad++) but augments them.

Excel has a limit of 1 million rows. This app has no limit (performance depends on available hardware).

Excel cannot run SQL like filtering and update commands. You can do complex SQL filtering/updating using this app!

Excel saves files in binary format (xlsx). This app saves in plain text ASCII format, so you can use any other text editor to manipulate the file however you may like it.

Other text editors do not offer you to execute GROUP BY SUM or COUNT queries or bulk update of column values with conditions. This app offers you all these! The SQL commands in Text Grid are damn simple.

Feature	Spreadsheet	Text editor	Text Grid
File format	binary	text	text
Cell formulas	yes	no	no
Cell formatting	yes	no	yes
SQL select	no	no	yes
SQL update	no	no	yes
Group by sum	yes	no	yes
Max row	1 million	no limit	no limit

This application is primarily aimed for editing delimited text files. However, a secondary capability of basic edits in plain text files has also been provided.

Features

· Handle any delimited file

· With or without header

· Cell value update in place

· No row, column limit

· SQL query

· SQL update

· Group by sum

· Find & highlight globally

· Add columns and rows

· Sort rows, rearrange columns

· Copy-paste to other applications

Example tutorial file

Open the supplied "World for Text Edit.pdv" file. This file is | (pipe) delimited and has a header record.

To prevent accidental edits, you can enable read only mode via View - Read Only. To return to read write mode, use View - Read Write menu.

Query data using SQL

After opening the sample file, run the following queries to understand how you can run SQL on text file using View - Filter menu.

[currency code] = 'EUR'

[currency code] IN ('EUR','USD','GBP')

[area sq km] > 1000000 and [area sq km] < 2000000

If any row has [area sq km] = null or blank, above query will fail because NULL or blank is treated as non-numeric value!

But you can overcome this by excluding blank values as shown in query below.

[GDP PPP USD] <> '' AND [GDP PPP USD] > 20000

Some more examples:

country like '%united%'

population > 100000000

[area sq km] > 1000000 AND population > 100000000

[area sq km] > 1000000 OR population > 100000000

iso2 = 'GB'

[currency code] = ''

[GDP PPP USD] <> '' AND [GDP PPP USD] > 10000

You can even filter for date values like this (using YYYY/MM/DD or MM/DD/YYYY format):

birthday > #1990/12/31#

This will work even if date string is shown in file as MM/DD/YYYY.

To search for a string globally i.e. in all rows and columns, use View - Global Filter menu.

If you want to frequently perform search, you can open the Live Search window using View - Live Filter menu. This window stays on top and you can type something and then press [Enter] to perform a global search.

To clear filter, use View - Show All menu.

For Group by Sum or Count type queries, use Column - Group By and Column - Sum menus.

When you use Group By option, a separate dialog box will show you the result. You can select the entire result grid and copy result to clipboard by pressing Ctrl + C or Ctrl + Insert. Then you can paste the result in spreadsheet or other applications.

You can calculate sum of all cells on the left (for current row) or above (current column) by using menu Row - Sum left or Column - Sum above. However, unlike a spreadsheet, the sum value will not auto update if source values are changed and you need to manually execute the sum command again to reflect correct values. If sum cannot be calculated, then the 0 will be displayed in selected cell. When you calculate sum left or above values, the existing content in selected cell will be replaced with sum value.

Update data using SQL

You can update column values for multiple rows using SQL. Open the Column - Bulk Update menu.

Assume you want to update all blank currency code values with XZD.

To achieve this, supply these values.

Update column	currency code
Set Value	XZD
Where (condition)	[currency code] = ''

Then click the Run button. A message box will tell you that values have been updated.

If you want to update multiple column values, then use this form for each column.

Note that if column name has space, then you need to use [ ] in where condition.

Command line parameters

You can open a file from command prompt using following example syntax.

"Text Grid.exe" "C:\MyData\World for Text Edit.pdv" "|" "1"

Where,

First parameter is the name of the file to load (with full path, if file exists on a folder different from Text Grid.exe file.

Second parameter is the delimiter.

Third parameter is the header indicator (1 = header exists, 0 = no header).

If you supply only first parameter, i.e. the file name - then application will open assuming second and third parameters are "|" "1". This is the mode when you right click on a PDV file and select to open it with Text Grid.

If you do not supply all parameters in correct order, the application will start with an error message and you need to manually open a file using File - Open menu.

Cell formatting

There is limited support for cell formatting like making cell content bold and changing of cell background colors.

To keep the text grid data file as pure text file, the cell formatting, when applied, is saved in a separate file with .gfm extension. For example, the format file for filename.pdv will be filename.pdv.gfm.

To save the file with formatting, you must manually save formatting via File - Format Save menu. Similarly, to load formatting (from pre-saved .gfm file), you must manually select File - Format Load menu. If you add/remove rows/columns the format file will become out of sync - so you need to manually save format file again after such operations.

Plotting

By using Column - Plot menu, you can plot the distribution of current column values.

Convert CSV to PDV

If CSV file has texts surrounded by double quotes, then this application cannot open those files.

However, you can convert those files to | delimited with double quotes removed using File - Convert - CSV to PDV menu.

You can then open the PDV file normally.

CSV files, where values are not surrounded by double quotes, can be opened without any conversion.

Tips & Tricks

Add ID column

Adding an ID column (Column - Insert ID column) is useful when you want another way of providing where conditions for selecting or updating data. You can drop the ID column or keep it if you wish. You can have only one auto generated ID column and it always starts with 1 and increment by 1 until end of row is reached. If you add more rows, the ID values will not auto increment. You need to drop and re-create ID column if you wish to persist it.

Select non-adjacent cells

Perform first selection by clicking on a cell and then dragging mouse button. Alternatively, you can select first cell and click on last cell on the range while pressing Shift button.

Then press Control key and drag mouse or click on last cell of the range while clicking on Shift button.

Once desired cells are selected, you can press Ctrl + C or Ctrl + Insert to copy the cells values in clipboard. You can then paste by Ctrl + V or Shift + Insert in target application.

Get Distinct values of a column

If you press F3 on a cell, you can see all distinct values of that column. Double clicking on List of Values dialog form will copy the clicked value to current cell.

Alternatively, perform a Group By query (via Column - Group by) and select the column (in which you want distinct values) and then for group by column choose any numeric column name. If no such column exists, simply add a new column and populate with value 1. Then check the result where you will find all distinct column values.

Open file from URL

In the File Open dialog box, you can even open a file directly using web URL like:

http://enselsoftware.com/myfolder/sample.txt

As long as the file has valid structure and data, you should be able to open the file as if a local file. In the background the file is downloaded to your computer's temporary folder and opened from there.

However, you cannot edit file opened via URL.

Use PDV over CSV or TSV

We strongly recommend that you use (pipe |) as your delimiter instead of comma (CSV ,) or tab (TSV \t). This is because comma is a very common character in normal sentences. The change of messing up data because of comma is quite common. Problem with tab is that it is unprintable and you may not easily realize whether tab has been replaced by spaces!

Whatever delimiter you use, please ensure that your data does not contain same character inside as a cell value, else it would corrupt the file.

Keyboard shortcuts

These shortcuts are applicable depending on whether it is grid editor or text editor interface.

Shortcut	Command Action
F1	Displays this help
F2	Edits current cell
F3	Displays unique List of Values of current column
F4	Replicate current row
F5	Reload current file
Ctrl + F5	Read only mode
Ctrl + F6	Read Write mode (default)
F7	Column width change
F8	Auto size displayed columns
Ctrl + F8	Sum above
Ctrl + F9	Sum left
F9	Copy value from cell above
F10	Save grid data to file
Ctrl + B	Toggle current cell format to bold
Ctrl + C Ctrl + Insert	Copy selected content to clipboard
Ctrl + F	Find globally in grid
Ctrl + H	Find and highlight
Ctrl + I	SQL filter
Ctrl + J	Show all rows (including those hidden by previous filter)
Ctrl + K	Sum column
Ctrl + L	Sum Group By
Ctrl + M	Show row number
Ctrl + O	Open file
Ctrl + P	Plot current column values
Ctrl + Q	Quit application
Ctrl + R	Find and Replace
Ctrl + U	Bulk update column values
Ctrl + V Shift + Insert	Paste selected content to clipboard
Ctrl + W	Save As
Ctrl + X	Cut selected content to clipboard
Ctrl + 1	Set back color to green
Ctrl + 2	Set back color to orange
Ctrl + 3	Set back color to red
Ctrl + 4	Set back color to blue
Esc	Undo current cell edit

Glossary

ASCII = American Standard Code for Information Interchange

CSV = Comma Separated Values

PDV = Pipe Delimited Values

SQL = for Structured Query Language

Contact