Extracting columns from a text file with no delimiters

Question

I have a large text file which is basically a stream of data all pretty much compressed together for each row. I've been asked to look into the failure of certain data in some columns. The data is not delimited in any way. I do however have a list of "column" lengths and comments on whether there's relevant data in each "column".

I'd use Excel, but the limit of Excel to delimit by columns is restricted to 1000 characters per row, and each row goes well beyond this. A number of these fields have strings of 30 spaces that act as filler and there's at least a good 15 or so of these... I'm hoping to parse these designated "empty" fields out.

What I need is a way that I can feed my file in and with an array that I can provide which has the column lengths and maybe a marker like "X" to ignore the respective columns I want to ignore, have it spit out a new file with delimiters, which I can then feed back into Excel for analysis.

For example if I had a file with a row like aaaaaabbbbbccccdddddeeeffffff and I feed this file in with an array of [6 5 4X 5 3X 6] it would spit out a file with aaaaaa^bbbbb^ddddd^ffffff in that row.

Is there a way this can be done with grep, awk or sed?

Thanks in advance.

Answer 1

Short cut command approach:

Sample input.txt contents:

aaaaaabbbbbccccdddddeeeffffff
wwwwwwddddd111133333xxxaaaaaa
ffffff00000sssszzzzz000rrrrrr

---

The job:

cut -c 1-6,7-11,16-20,24-29 --output-delimiter=^ input.txt

• -c - to select only characters

• 1-6,7-11,16-20,24-29 - consecutive ranges of character positions, flexibly adjustable

• --output-delimiter=^ - output field delimiter, you can adjust it to whatever you want

---

The output:

aaaaaa^bbbbb^ddddd^ffffff
wwwwww^ddddd^33333^aaaaaa
ffffff^00000^zzzzz^rrrrrr

Answer 2

If you have GNU awk, you can specify explicit fieldwidths e.g.

$ printf 'aaaaaabbbbbccccdddddeeeffffff\n' | 
    gawk -v FIELDWIDTHS="6 5 4 5 3 6" -v OFS="^" '{print $1, $2, $4, $6}'
aaaaaa^bbbbb^ddddd^ffffff

Starting with version 4.2, you can skip characters using a n:m syntax e.g.

printf 'aaaaaabbbbbccccdddddeeeffffff\n' |
   gawk -v FIELDWIDTHS="6 5 4:5 3:6" -v OFS="^" '{$1=$1} 1'
aaaaaa^bbbbb^ddddd^ffffff

(the $1=$ just forces re-evaluation of $0 with the specified fieldwidths).

See for example The GNU Awk User's Guide: 4.6 Reading Fixed-Width Data

Answer 3

Hard to say without seeing your exact input and desired output, but...

sed -e "$(
  printf '%d\n' 6 5 4 5 3 6 |
    awk '
      {
        f[NR] = f[NR-1] + $1
      }
      END {
        for (i=NR; i>0; i--) {
          printf "s/./&^/%d\n", f[i]
        }
      }
    '
)" infile.txt | cut -d^ -f1,2,4,6

Untested. No bugs, I promise. ;)

---

Okay, I tested. I was missing the end brace for END. No other bugs. Works perfectly on example input. Output is:

aaaaaa^bbbbb^ddddd^ffffff

Answer 4

With sed, one could write (using _ as delimiter):

sed "$(echo s/./\&_/{29,23,20,15,11,6}\;)"

But this means to sum up the absolute positions from the column widths. TO directly use the widths, we need some ugly escaping for the command substitution:

sed -E "s/./&_/6;$(echo s/.\*_\(.\)\{{5,4,5,3,6}\}/\&_/\;)"

Answer 5

Improved version of RomanPerekhrest's cut answer, with column array parser, including X suffixes to show how many columns to skip.

Load array $n, and make a function to parse array into numbers for cut -c:

n=(6 5 4X 5 3X 6)
col_array() { j=$(h=0; 
                  for f in $@; do 
                      g=${f/[Xx]};
                      i=$((h+1));
                      h=$((h+g));
                      [ $g = $f ] && echo -n $i-$h,
                  done;) ; 
              echo ${j%,}; }

The file input.txt contains:

aaaaaabbbbbccccdddddeeeffffff
wwwwwwddddd111133333xxxaaaaaa
ffffff00000sssszzzzz000rrrrrr

Use col_array() with cut:

cut -c $(col_array  ${n[@]}) --output-delimiter=^ input.txt

Output:

aaaaaa^bbbbb^ddddd^ffffff
wwwwww^ddddd^33333^aaaaaa
ffffff^00000^zzzzz^rrrrrr

There's no strict need for an array, since col_array() parses its parameters:

cut -c $(col_array 3 5X 7) --output-delimiter=^ input.txt

Output:

aaa^bbbcccc
www^ddd1111
fff^000ssss