convert text to excel

Convert Text File to Excel Using Shell Script

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

Leave a Reply

Your email address will not be published.