convert table into comma separated in text file using bash

Question

I have a text file like this:

+------------------+------------+----------+
|     col_name     | data_type  | comment  |
+------------------+------------+----------+
| _id              | bigint     |          |
| starttime        | string     |          |
+------------------+------------+----------+

how can i get a result like this using bash

(_id bigint, starttime string   )

so just the column names and type

#remove first 3 lines 
sed -e '1,3d' < columnnames.txt >clean.txt

#remove first character from each line
sed 's/^.//'  < clean.txt >clean.txt

#remove last character from each line
sed 's/.$//' < clean.txt >clean.txt


# remove certain characters 
sed 's/[+-|]//g' < clean.txt >clean.txt 

# remove last line 
sed  '$ d' < clean.txt >clean.txt

so this is what i have so far, if there is a better implementation let me know!

Just a remark: You can use sed -i … clean.txt to modify the file in place instead of using < clean.txt > clean.txt. — Joe
– Joe, Commented Jan 31, 2020 at 19:08
I'm pretty sure that the tool which outputs this table, I suppose it's a database client, supports machine readable output like cvs as well. Check the manual of that program — hek2mgl
– hek2mgl, Commented Jan 31, 2020 at 19:42

Diego Torres Milano · Accepted Answer · 2020-01-31 19:37:24Z

2

Something similar, using only awk:

awk -F ' *[|]' 'BEGIN {printf("(")} NR>3 && NF>1 {printf("%s%s%s", NR>4 ? "," : "", $2, $3)} END {printf(" )\n")}' columnnames.txt

answered Jan 31, 2020 at 19:37

Diego Torres Milano

69.9k9 gold badges116 silver badges145 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jas · Accepted Answer · 2020-01-31 19:31:32Z

1

# Set the field separator to vertical bar surrounded by any number of spaces.
# BEGIN and END blocks print the opening and closing parens
# The line between skips the header lines and any line starting with '+'

$ awk -F"[[:space:]]*[|][[[:space:]]*" '
    BEGIN { printf "%s", "( "}  
    NR > 3 && $0 !~ /^[+]/ { printf("%s%s %s", c, $2, $3); c = ", " } 
    END { print " )" }' file

( _id bigint, starttime string )

edited Jan 31, 2020 at 19:31

answered Jan 31, 2020 at 19:26

jas

10.9k2 gold badges33 silver badges45 bronze badges

Comments

Ed Morton · Accepted Answer · 2020-02-01 01:17:51Z

1

$ awk -F'[| ]+' 'NR>3 && NF>1{v=v s $2" "$3; s=", "} END{print "("v")"}' file
(_id bigint, starttime string)

answered Feb 1, 2020 at 1:17

Ed Morton

209k18 gold badges90 silver badges212 bronze badges

Comments

Matias Barrios · Accepted Answer · 2020-01-31 19:13:15Z

0

I would do this :

cat input.txt \
| tail -n +4 \
| awk -F'[^a-zA-Z_]+' '{ for(i=1;i<=NF;i++) { printf $i" " }}'

Its a little bit shorter.

answered Jan 31, 2020 at 19:13

Matias Barrios

5,0864 gold badges31 silver badges71 bronze badges

Comments

Léa Gris · Accepted Answer · 2020-01-31 22:57:24Z

0

Another way to implement Diego Torres Milano's solution as a stand-alone awk program:

tableconvert

#!/usr/bin/env -S awk -f

BEGIN {
  FS="[[:space:]]*[|][[[:space:]]*"
  printf "%s", "( "
}

{
  if (FNR <= 3 || match($0, /^[+]/))
    next
  else {
    printf("%s%s %s", c, $2, $3)
    c = ", "
  }
} 

END {
  print " )" 
}

Make tableconvert an executable:

chmod +x tableconvert

Run tableconvert on intablefile.txt

./tableconvert intablefile.txt 
( _id bigint, starttime string )

With added bonus that using FNR instead of NR allow the awk program to process multiple input files as arguments:

./tableconvert infille1.txt infile2.txt infile3.txt ...

edited Jan 31, 2020 at 22:57

answered Jan 31, 2020 at 22:35

Léa Gris

20.2k4 gold badges39 silver badges52 bronze badges

Comments

David C. Rankin · Accepted Answer · 2020-01-31 23:43:36Z

A variation on the other answers using awk with the field-separator being the '|' with optional spaces on either side as GNU awk allows, then taking fields 2 and 3 as the fields wanted in each record, and formatting the output as described in the question with the closing " )" provided in the END rule:

$ awk -F' *\\| *' '
    NR>3 && $1~/^[+]/{exit}                 # exit condition first line w/^+
    NR==4{$1=$1; printf "(%s %s", $2,$3}    # 1st data record is 4
    NR>4{$1=$1; printf ", %s %s", $2,$3}    # process all remainng records
    END{print "  )"}                        # output closing "  )"
' table
(_id bigint, starttime string  )

(note: if you don't want the two-spaces before the closing ")", just remove them from the print in the END rule)

Rather than using a BEGIN the first record of interest (4) is used to provide the opening "(". Look things over and let me know if you have questions.

Collectives™ on Stack Overflow

convert table into comma separated in text file using bash

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related