Have you ever been in a situation to work with CSV files and produce output in structured tabular format? Recently I was working with data cleansing on a file that is not in a proper structure. It has so many whitespaces between each column and I have to convert it to CSV format to push to the database. After cleaning and creating the output in CSV format, my output is not visually appealing to verify data integrity in the CSV file. This is the time the “Column” command comes in handy to me.
According to manpage, the column command “columnate lists”. In simple words, the column is a simple utility that can format your output into a column format (rows and fields) based on the structure of your source file. The column command is part of the util-linux package.
An important point to note here is column command behaves differently in Debian-based and Rhel-based distros. The reason is Debian-based distro uses “column” from bsdmainutils instead of util-linux. The upstream version of the column command is newer than the bsdmainutils package. Take a look at the bug report to know more about this.
$ dpkg -S $(which column)
For demonstration purposes, I am using CentOS 7 and will show different options between Ubuntu and CentOS 7. To check the column version run the following command. This command will also show the util-linux package version.
$ column --version # will not work in Debian/ubuntu
You can also check the version of util-linux by running the below commands.
$ rpm -qa | grep -i util-linux # Redhat,Centos,Fedora,Amazon Linux $ dpkg -l | grep -i util-linux # Ubuntu
Before using the column command a good place to start will be the man page and explore its options.
$ man column
List File Content in Tabular Format
The column command can create a table by passing the filename as an argument along with the
-t flag. I am using /etc/passwd as the input file.
$ column -t /etc/passwd
Looking at the above image, you may think this is not what we expected and the output may look weird. Yes! You are right. Columns consider space as the default delimiter when creating a table. This behavior can be overridden by passing a custom delimiter.
Custom delimiters give you a wide range of options to work with. To create a custom delimiter use
-s flag followed by a delimiter. Now we will use
“:” as a delimiter to split /etc/passwd file.
$ column -s ":" -t /etc/passwd
Look at the above image where the table is nicely formatted and structured. From util-linux version 2.23 option
-s has been changed to not be greedy.
Now run the same command in Ubuntu and the result will be greedy. This is because column command (bsdmainutils) on Ubuntu will treat multiple adjacent words as a single word.
$ column -s ":" -t /etc/passwd
To overcome this behavior use
$ column -t -s ":" -n /etc/passwd # Only on Debian/Ubuntu
Ignore White Empty Lines in File Output
When you have blank lines in your input file, the column command by default ignores it. See my input file which is in CSV format and I added a blank line between every line. Now let’s create a table as we did before with this input file.
$ column -t -s ";" dummy.txt
From the above image you can see my input file dummy.txt has empty lines and when I try to create a table, empty lines are ignored.
Note: This is the default behavior for both the “bsdmainutils/util-linux” variant of the column command. But column (bsdmainutils) has the option to override this behavior by passing
$ column -e -t -s "," dummy.txt # Only on Debian/Ubuntu
From the above image, you can see the table is formatted properly and the empty lines are not ignored.
File Output Separator
By default, two white spaces will be used as output separators. This behavior can be overridden by passing
-o flag. You will not have an output separator option available in the column (bsdmainutils).
$ column -t -s "," -o "||" dummy.txt # Only on Rhel based distro
Convert File Rows into Columns
-x the flag you can convert rows into columns. This behavior is the same in both the rhel and ubuntu variants of the column command. This is a very useful feature when you have to grab a certain field through the awk or column command then convert it to the header for your CSV file.
$ column -x fillcols.txt
When you run the column command without using any flags the behavior will be the same as passing
Find Column Size
The column uses an environmental variable
($COLUMNS) to find out the size of your terminal and based on the size use the echo command, table size will be displayed in the terminal.
$ echo $COLUMNS
Take a look at the image below. Initially, I resized my terminal to have
$COLUMNS the size set to 60 and ran the column command. Again I resized my terminal to have
$COLUMNS the size set to 114 and ran the column command again. You can see the difference in how the column prints the table when we resize the terminal.
$ column -t -s ":" /etc/passwd | head 5
That’s it for this article. If you have any feedback please provide it in the comment section.