Sometimes you may need to write text file data to excel or convert text to excel in bash, or bulk convert text files to spreadsheets. In this article, we will learn how to convert text file to excel using shell script.
Convert Text File to Excel Using Shell Script
Let us say you have the following text file, data.txt.
drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x 2 apx aim 4096 Oct 29 18:40 drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x 2 apx aim 4096 Oct 29 18:40 drwxr-xr-x 5 root root 4096 Oct 2 12:26 drwxr-xr-x 2 apx aim 4096 Oct 29 18:40
Let us say you want to convert it into Excel sheets.
drwxr-xr-x|5|root|root|4096|Oct|2|12:26 drwxr-xr-x|2|apx|aim|4096|Oct|29|18:40 drwxr-xr-x|5|root|root|4096|Oct|2|12:26 drwxr-xr-x|2|apx|aim|4096|Oct|29|18:40 drwxr-xr-x|5|root|root|4096|Oct|2|12:26 drwxr-xr-x|2|apx|aim|4096|Oct|29|18:40
Of course, you can use any scripting language to convert text files into Excel documents. But here is a simple awk command to help you convert text document to pipe delimited file which you can import in Excel.
awk '{$1=$1}1' OFS="|" data.txt
If you have multiple text files in folder, then we can use a shell script to bulk convert text to excel. Run the following command to create an empty shell script.
$ vi convert.sh
Add the following lines to it to loop through all .txt files in /Data folder and convert them into pipe delimited Excel spreadsheets.
#!/bin/sh for filename in /Data/*.txt; do awk '{$1=$1}1' OFS="|" $filename done
Save and close the file.
Make it executable.
$ chmod +x convert.sh
Run the script to convert .txt file to Excel sheets.
$ ./convert.sh
In this article, we have learnt how to convert one or more text files into Excel sheets.
Also read:
How to Move Directory to Another Git Repository
How to Move Directory to Another Partition in Linux
How to Search PDF File in Linux
How to Schedule Task in Python
How to Import Python Modules by String Name