Awk split one column into multiple columns

Sometimes you may need to split one column into multiple columns. You can easily do this using awk command. It allows you to specify the character based on which you want to split a single column. In this article, we will learn how to split one column into multiple columns.

Awk split one column into multiple columns

Let us say you have the following data with single column of values separated by comma.

$ cat data.txt
1,John,43
2,Jane,50

Let us say you want to split this into 3 columns in a tab delimited manner.

1 John 43
2 Jane 50

Here is a simple awk command to split it into 3 columns.

$ awk -F "," 'OFS="\t" {print $1,$2,$3 > ("output.txt")}' data.txt
$ cat output.txt
1 John 43
2 Jane 50

In the above awk command, we use -F option to specify input delimiter as comma, -OFS option to specify the output field delimiter, that is the delimiter to be used for output file. We issue a print statement within curly braces {} to print the columns 1, 2, 3. We also use redirection operator to redirect the output to file output.txt. Finally, we mention the input file data.txt.

If you don’t want to write the output to file, but only display it on standard output, modify the command as shown below.

$ awk -F "," 'OFS="\t" {print $1,$2,$3}' data.txt
1 John 43
2 Jane 50

You can also use the above command to split a pipe delimited file as shown below. In this case, replace comma “,” in input field delimiter with “|” as shown.

$ awk -F "|" 'OFS="\t" {print $1,$2,$3 > ("output.txt")}' data.txt

In the above example, we know our input file has 3 columns and we explicitly print each column using $1, $2 and $3 identifiers. If you don’t know the number of columns in your file, you can replace the print $1, $1, $2 part with a for loop as shown below.

for(i=2;i<=NF;i++){printf "%s ", $i}; printf "\n"

The above for loop loops through each column and prints it one after the other. It also adds a new line at the end of each line of input file. So now your awk command will become

$ awk -F "," 'OFS="\t" {for(i=1;i<=NF;i++){printf "%s ", $i}; printf "\n"}' data.txt
1 John 43
2 Jane 50

In this article, we have learnt how to split column into multiple columns in awk command. You can customize this command as per your requirement, and even use it in shell scripts.

Also read:

How to Read Variable from File in Shell Script
Shell Script to Print Output in Table Format
How to Change PHP Version in Ubuntu
MySQL Clustered Index
How to Execute Stored Procedure in Python