CODEWITHSUNDEEP
shellseduuid
sipsorcery
sipsorcery

Reputation: 30699

Add new uuid to each line of a file using sed or equivalent command

I've got a multi line text file that I want to use to create an SQL statement that requires a UUID. I'm attempting to come up with a way to generate the SQL using sed or some other shell command utility.

Example input:
A
B
C

Example Output:
insert into table values ('7CC92727-D743-45E0-BE57-9FEB2B73BD18','A');
insert into table values ('94E03071-F457-48DD-86E2-AF26309A192D','B');
insert into table values ('1F17525C-9215-4FC4-A608-4FA43C0B2B14','C');

I can use the uuidgen command to generate new UUID's but so far haven't found a way to use that command with sed.

Update: Thanks to the answers I was able to come up with a sed command that worked for me on cygwin.

Expressed without quoting the SQL values for didatic purposes:

sed 's/.*/echo "insert into table values (`uuidgen | tr -d \r`,&)"/e' file.txt

With the quotes:

sed 's/.*/echo "insert into table values '\''(`uuidgen | tr -d \r`'\'','\''&'\'')"/e' file.txt

Upvotes: 1

Views: 3564

Answers (3)

Todd A. Jacobs
Todd A. Jacobs

Reputation: 84343

A Very Readable Bash Solution Using a While-Loop

You can read the file into a Bash loop using the default REPLY variable. For example:

while read; do
    echo "insert into table values ('$(uuidgen)','$REPLY');"
done < /tmp/foo

A Less-Readable Sed Solution

sed -e 's/.*/echo "insert into table values (\\"$(uuidgen)\\",\\"&\\");"/e' \
    -e "s/\"/'/g" /tmp/foo

The while-loop is significantly more readable than the sed alternative, because of the necessity to escape quotes in your replacement string. The sed solution is also rather brittle because of the fact that it is evaluating the contents of your file inside a sed expression, which may cause errors when certain meta-characters are present. And finally, this particular sed solution relies on the /e flag, which is a GNU sed extension that may not be available on your platform.

The GNU sed manual describes the flag as follows:

This command allows one to pipe input from a shell command into pattern space. If a substitution was made, the command that is found in pattern space is executed and pattern space is replaced with its output. A trailing newline is suppressed; results are undefined if the command to be executed contains a nul character. This is a GNU sed extension.

Testing

Both scripts were tested against /tmp/foo, which contained the following fixture data:

A
B
C

Bash Sample Output:

insert into table values ('fe0ca930-456b-4265-810c-219eb93c4c73','A');
insert into table values ('34b088eb-3dc0-46fa-85ca-efaf3f0c0f4b','B');
insert into table values ('5d271207-99fe-4ca2-8420-3b8ca774e99b','C');

GNU sed Sample Output:

insert into table values ('4c924b78-dc70-441d-928e-638fec9f3ea1','A');
insert into table values ('29f424d4-6e33-4646-a773-cd0e96ebb874','B');
insert into table values ('39534c05-6853-4390-a6b6-4a19fad296b1','C');

Conclusion

The Bash solution seems clearer and more robust than the sed solution. However, both solutions clearly work on the fixture data provided in the original question, so you should pick whichever one works best for you on the real data.

Upvotes: 4

kev
kev

Reputation: 161644

sed 's/.*/echo "`uuidgen`,&"/e' input |
    sed -r 's/(.*),(.*)/insert into table values("\1","\2");/' |
        tr '"' "'"

insert into table values('c609f5ab-28ce-4853-bd67-7b6b4ca13ee3','A');
insert into table values('01ae6480-1b52-49a8-99a3-f2bba7ec3064','B');
insert into table values('a41122e8-5e4f-4acc-b62a-bc4ad629677e','C');

Upvotes: 2

potong
potong

Reputation: 58381

This might work for you:

echo -e 'a\nb\nc' | 
sed 's/.*/uuidgen;echo "&"/' | 
sh | 
sed 'N;s/^\(.*\)\n\(.*\)/insert into table values('\''\1'\'',\2);/'
insert into table values('c9939dfe-5a74-465a-b538-66aeba774b6b',a);
insert into table values('da6684f2-3426-4561-b41d-7b507d2d79ee',b);
insert into table values('23c72ef5-2a50-4a09-b964-83eea3c54e83',c);

or using GNU sed:

echo -e 'a\nb\nc' | 
sed 'h;s/.*/uuidgen/e;G;s/^\(.*\)\n\(.*\)/insert into table values('\''\1'\'',\2);/'
insert into table values('ac1a130c-50a3-43ce-8d41-987ca0d942b7',a);
insert into table values('59426b2f-cf03-4233-bcc2-3ce05c47bece',b);
insert into table values('fdec75d6-313e-48c4-adfb-335721a0f6e7',c);

Upvotes: 1

Related Questions