Edit CSV Files with 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.
CSV File Editing Features
Step 1: Convert to fixed-width
Open the CSV file you’re going to be working with.
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.
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:
Finally, we’ll click Convert, and the file is converted to fixed-width format.
Now that the data is aligned in columns, you can edit multiple fields at once using 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.
You’ll see the following options in the “Convert to Character Delimited” dialog:
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.