Working with CSV files in UltraEdit

Many of us work with character separated value files which need to be manipulated in some form or fashion. These CSV files can be somtimes difficult to deal with when each field has a different width. UltraEdit can be particularly useful where column editing is required, because of its Convert to fixed width and Convert to CSV features. Using these powerful tools, you can convert a file from character delimited fields to fixed-width columns and back.

Step 1: Convert to fixed-width

Open the CSV file you're going to be working with.

Screenshot of open CSV file in UltraEdit

With the CSV file active, click on the Edit tab. Look in the "Column / block" group towards the middle of the ribbon and click on the CSV Convert drop down, then select Convert to fixed-width.

Screenshot of Convert to fixed-width command

This will open the "Convert to Fixed Columns" dialog where you can set options for the conversion. A brief explanation of each option follows:

Scan
Click this to have UltraEdit scan the file (or first line) and determine the minimum widths required for each field. After calculating these values, you'll see them appear as comma separated numbers in the "Field widths" entry box near the bottom of the dialog.

Scan first line only (vs. complete file)
This instructs the editor to search just the first line of the file for the field widths. This can save time if you're working with a huge CSV file.

Keep delimiter with fixed columns
If this isn't checked, UltraEdit will remove the delimiter between each field. If it is checked, the delimiter character for the fields won't be removed.

Ignore separator in quotes (')
If checked, any separator character that exists between single quotes will be ignored as a field separator and treated as a regular character.

Ignore separator in double quotes (")
If checked, any separator character that exists between double quotes will be ignored as a field separator and treated as a regular character.

Separator character
This specifies the delimiter character between fields, i.e., the field boundaries. Typically this is a comma (,), pipe (|), or tab. For tab characters, enter ^t into this field. In all other cases, a single character should be entered.

Field widths
This is a list of comma separated field widths used for the conversion. Click the Scan button to have UltraEdit automatically scan the file to determine the field widths and set them here. If there are more fields than entries, the last entry will be used as the field width for the remaining fields. Of course, you can manually enter or modify field widths if you need to.

Convert
This button performs the conversion from character delimited text to fixed column according to the settings described above.

For this example, we are going to use the following configuration, which we get after clicking Scan to determine the field widths for each field. We've also manually added 1 to each field width so that each column is separated by a space:

Screenshot of Convert to Fixed Columns dialog

Finally, we'll click Convert, and the file is converted to fixed-width format.

Screenshot of CSV file converted to fixed-width columns

Now that the data is aligned in columns, you can edit multiple fields at once using column mode:

Screenshot of editing a CSV file in column mode

Step 2: Convert to CSV

If you need to convert the file back from fixed columns, use the Convert fixed-width to CSV option in the CSV Convert drop down.

Screenshot of Convert to CSV command

You'll see the following options in the "Convert to Character Delimited" dialog:

Screenshot of convert to character delimited dialog in UltraEdit

Separator character
As with the same field in the "Convert to Fixed Column" dialog, this entry allows you to input the separator character between fields. Enter ^t for tab.

Field widths
This is a list of comma separated field widths used for the conversion. If there are more fields than entries, the last entry will be used as the field width for the remaining fields. If you had previously converted the file to fixed columns, this field will be already populated with the correct values.

Convert
Click this button to convert the data from fixed column to character delimited text according to the settings above. The complete file will be converted.

Screenshot of fixed-width file converted to CSV