0

I have a flat file containing columns of data representing fields from a table. I get the file from an outside source and want to insert the data into a table in my database. Unfortunately, the file I receive is missing a field in a specific column (new field that was added). Since I have no control of what is being sent to me, I would like to edit the file and just add the field. Can sed be used to add text in a specific column on every row?

For example, let's say I have this file:

Alan Bradford                 555-2012
Cathy Davies                  555-7823
Edward Farris                 555-9162
Gary Hobbs                    555-5151
Irene Jacobs                  555-1285

The file is missing the area codes, so I want to add 213 in front. I know the phone numbers always start in column (pure character count) 31. So I want it to show

Alan Bradford                 213 555-2012
Cathy Davies                  213 555-7823
Edward Farris                 213 555-9162
Gary Hobbs                    213 555-5151
Irene Jacobs                  213 555-1285

I know I can do this in three passes. I can use cut -c1-30 and get Part1, and cut -c31- to get Part2. Then I can paste it all together with echo "$Part1 $NEWDATA $Part2" >> filename

I just wonder if there is a much easier way using sed. I should be able to use something like

sed -e "30l,i213 " InFile > OutFile

I just can't seem to get the syntax right to say move over 30 characters on the line, then insert 213 .

Anyone know what might work, or work better than my cut and paste options?

UPDATE

I'm told that my example was not accurate enough and I should edit the question to stop wasting people's time. The example I gave was pretty accurate for the question: How do I insert a string "XXX" ALWAYS in position Y no matter what is before or after it?

But no problem...Here's my real world example. I have a text file with rows of 928 characters each. I want to insert a string starting at position 878. The values before and after the string cannot be counted on to be the same each time, since the next field after where I want to insert is a remarks field, which is usually but not always blank.

The answer from @DonHolgo had the most promise and is a great answer. But on my flavor of UNIX (AIX 7.1) it seems that it only allows you to keep track of up to 255 characters before you get an error.

Here I am inserting "XXX " at column 255:

# sed 's/.\{255\}/&XXX /' OrigTextFile
1  030680001001YNPO    14          H502  000595000000000000       1  0000680M00000100000004799000000000000479900000004799000000004799000000000000479900000       SDI42028820                                                         20P561292      00000000000XXX 000000000000000000000000000000000000000000000000000000000000000                                                  T              0000655000000000Y              0000516000000000E              0000280000000000               0000000000000000               0000000000000000               0000000000000000               0000000000000000               0000000000000000               0000000000000000               0000000000000000                                                                                                                                                                                                        20200814

But I try the same thing for inserting at column 256 or higher:

# sed 's/.\{256\}/&XXX /' OrigTextFile
sed: 0602-404 Function s/.\{256\}/&XXX / cannot be parsed.

Looks like a limitation on my version of sed. So I may have to just do my original plan of cutting the textfile up. File1 = First 878 characters of each line from the textfile File2 = New string I want to add, one line for each line in original file File3 = Remaining characters from original textfile.

Then just join them all:

# paste File1 File2 File3 > NewTextFile
8
  • Is the file space- or tab-separated? – AdminBee Aug 18 '20 at 14:52
  • It is space delimted. Thx – S. Nixon Aug 18 '20 at 15:06
  • Perhaps a better example would be if I always have a city name (Tulsa), a state (OK), followed by a zip code. And now I need to add the country abbreviation (USA) between the state and zip. It will not always be the same letters or numbers before or after the columns I want to add. And the zip code might be another country postal code, such as in Canada where they use letters. So all I can count on is always inserting at that same position in the line. – S. Nixon Aug 18 '20 at 15:29
  • Are the phone numbers always NNN-NNNN? – terdon Aug 18 '20 at 15:52
  • 1
    To deal with not being able to have numbers greater than 255, just use multiple chunks. e.g. to add in the 878 position use s/.\{200\}.\{200\}.\{200\}.\{200\}.\{78\}/&213/ – icarus Aug 18 '20 at 22:50
2

You can use

sed 's/.\{30\}/&213 /' InFile > OutFile

to replace the first 30 characters ("any character" times 30) by themselves (&) plus "213 ".

5
  • This works as long as I only need to replace something in the first 255 columns (and with my example). Unfortunately, in my real-life case, the column I need to insert is in position 878 of a 928 character line. Although what I could do is reverse the whole line, do the sed with {50}, and then rev it back again. So it's a possibility. – S. Nixon Aug 18 '20 at 15:15
  • 2
    @S.Nixon then please edit your question and give us an example file they accurately represents your data. Otherwise, we will just waste your time, and ours, giving solutions that work for your example but not for your real data. – terdon Aug 18 '20 at 15:55
  • @S.Nixon Or simply use 878 instead of 30? – DonHolgo Aug 18 '20 at 17:32
  • Thanks for everyone's suggestions and advice. It turns out that I had to make a few other changes to the data (such as adding a unique key as the first field) so it ends up being easier to do my original logic. File 1 contains columns 1-878. File 2 contains columns 879 and higher. I can then paste the files together adding the unique key as a prefix, and inserting the new field between file 1 and 2. Once all combined, it has all the fields in the right spots to import into my PostgreSQL table. – S. Nixon Aug 19 '20 at 19:06
  • DonHolgo gets best answer as he answered the question as asked. Even if my sed version did not work as expected. Using sed in specific column. But I liked the awk and perl suggestions too. Thanks all – S. Nixon Aug 19 '20 at 19:07
2

You can use a counted RE. For example, x{12} will match 12 x characters, and y{1,3} would match 1, 2, or 3 y characters. Here we're going to use .{30} to match 30 character wildcards (i.e. 30 of any character). The \1 in the result string matches the bracketed reference in the pattern match

sed -r 's#^(.{30})#\1213 #' file

In your updated question you now say there are 878 characters before the insert. So just amend the example's 30 to reality's 878 and insert XXX

sed -r 's#^(.{878})#\1XXX#' file

The same process can apply for any fixed-width modification.

You can use perl too, which doesn't have the line length limitations that plague some implementations of sed,

perl -pe 's#^(.{878})#$1XXX#' file
4
  • No point in having the second capturing group. – icarus Aug 18 '20 at 14:58
  • Indeed probably not. It came from the "Split the item in two, and add a bit" thinking :-) – roaima Aug 18 '20 at 15:51
  • The AIX version of sed appears to be incompatible. sed: Not a recognized flag: r – S. Nixon Aug 18 '20 at 18:11
  • @S.Nixon there you go, a perl alternative – roaima Aug 18 '20 at 19:34
1

You can also try it with awk

awk '{sub(/^.{30}/,"&213 ")}1' file

This will append 213 to the pattern consisting of the first 30 characters of the line, no matter what they are.

The syntax is as follows:

  • The sub() function is used to substitute the first occurence of the specified regular expression on the current line (default target if no string-to-be-manipulated is explicitly stated).
  • The regular expression is ^.{30}, meaning "30 times any character", but starting at the beginning of line (meaning of the "anchor" ^).
  • The replacement is "the found pattern (meaning of the &), followed by 213 and a space.
  • The remainder of the line will be untouched, leading effectively to an insertion of the 213 after these first 30 characters.

This action is performed on any line (action block { ... } without condition). awk will then print the modified line (the 1 at the end of the awk program).

3
  • My example was perhaps not the best as compared to my real life problem. In my case, I have a 928 character row with many columns. It may just have spaces in front of it, and it is followed by a remarks field of 50 characters. So it really could be anything and not a specific set of numbers and dashes. Ideally, I need to ALWAYS add "XXX" in column 878 without looking at what came before or after it. – S. Nixon Aug 18 '20 at 15:26
  • @S.Nixon I see. I have updated the command; it basically now does what the other answers do, too, only with a different tool ... – AdminBee Aug 18 '20 at 15:34
  • thx, this seems to work even when I exceed 256 characters into each text line. At the very least, these answers are helping me understand some of the things I can do with sed and awk that have always been very confusing for me. So thanks to all that answered so far. – S. Nixon Aug 18 '20 at 18:03
0

using Raku (née Perl6)

$ raku -pe 's/ (^^. ** 30) /{$0}213-/ ;'

OR

$ raku -pe 's/ (^^. ** 30) /{$0}{"213-"}/ ;'

Above code using Raku's s///operator. The "-pe" command line flag(s) instruct Raku to processes input line-by-line and autoprint the return value.

Note: Raku is normally very tolerant of spaces within regexes, but the replacement half of the s/// operator is one of those places where it pays to be extra careful. Just using $0 213- will add an extra space before the four-character 213- string that you would like inserted immediately after the first 30-character field.

The solution is to either:

  1. wrap the $0 capture in curly braces giving {$0}213- as the replacement, or
  2. wrap both the $0 capture and the double-quoted 213- in curly braces making sure to leave no spaces in between, like so: {$0}{"213-"} .

[Note for all examples above I used "213-" (with a trailing hyphen) as an example insertion string; I recognize the OP really wants ""213 " (with a trailing space) as the correct insertion string].

https://raku.org/

0

If you are running into the outer limits of the tool, you can use the alternative of breaking up the 877 number into 3 units of 255 and the remaining 112.

skip=877
cmax=255
mult=`expr "$skip" / "$cmax"`
rem=`expr "$skip" % "$cmax"`
lim=".\\{$cmax\\}"
re="\\($lim\\)\\{$mult\\}.\\{$rem\\}"

sed -e "s/$re/&XXX/"   your_file_nam

A much easier way is using Perl:

perl -lpe 'substr($_, 877) =~ s/^/XXX/'  your_file_name

python:

python3 -c '
import sys
f, p, r = sys.argv[1:]
p = int(p)
with open(f) as fh:
  print(*[l[:p]+r+l[p:] for l in fh],sep="",end="") 
' file_name 877 "XXX"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.