1
filename~contenturl~uuid~nodeid~contentid

000224.pdf~store://2018/7/20/11/35/3f176f4b-41a0-4ac4-a795-a2240ffbb7b9.bin~d6203724-1100-4057-9ed5-4ca6a94f5512~1324625~1363256
000238.pdf~store://2018/7/20/11/35/4302b390-1134-424d-a92f-ad27b233e8c1.bin~96b7343d-349d-4316-8bc6-def5bd924032~1324641~1363292
000262.pdf~store://2018/7/20/11/35/5ff59679-b3ec-46d2-aa7d-5ec28eff6fe9.bin~11827eee-67bb-43b7-a743-966514f26457~1324661~1363375

The above is my .csv file is there with separator as "~" , I want to do substring operation on the second column which is starting from store:// and want to add checksum result of that row to a new column in the same CSV if possible.

e.g.

filename~contenturl~checksum
000224.pdf  /opt/xyz/2018/7/20/11/35/3f176f4b-41a0-4ac4-a795-a2240ffbb7b9.bin  23423423425

So if you see the end result, I substring and process path in store:// and added a new column of that file called checksum.

I want this via shell script using bin/bash

======================= As far as I am newbie, I just tried with AWK and able to get only first and second column values using

awk -F "~" '{print $1, $2}'  $csv_file

Now the next thing is complex for me, Second column values requires text processing and checksum you can get via

cksum /opt/xyz/2018/7/20/11/35/3f176f4b-41a0-4ac4-a795-a2240ffbb7b9.bin

Yes you heard right, final result would looks like

filename~contenturl~checksum
000224.pdf  /opt/xyz/2018/7/20/11/35/3f176f4b-41a0-4ac4-a795-a2240ffbb7b9.bin  23423423425

rest of other column would be better if we would have, or else above three column is also fine. Note: If it is easy to retain existing column and to add more column named "checksum" that is also fine.

3
  • 1
    What have you done so far, where are you stuck? You need to show some initiative on your own.
    – Panki
    Commented Sep 19, 2019 at 10:16
  • 1
    How would you get or calculate the checksum? Your sample output uses ~ as separator in the heading and ` ` in the data line. You state you "want to add checksum result of that row to new column in same csv if possible", but in your sample output you replaced columns uuid~nodeid~contentid with checksum instead of adding a column. Please edit your question to clarify this.
    – Bodo
    Commented Sep 19, 2019 at 10:22
  • Ok as of now, I am able to just get second column using awk (awk -F "~" '{print $1, $2}' $csv_file) for getting checksum there is command cksum <my second column value which is full path to .bin file> Commented Sep 19, 2019 at 10:40

2 Answers 2

0

To make the solution a bit easier and because your header lines are not consistent (newline in input file and wrong separator ~ in output file), let's assume your input file doesn't contain a header line.

You can solve the task in two steps:

  1. Create a checksums.csv file, we only need the path the the file:

    awk -F'~' '{ system("cksum /opt/xyz"substr($2,8)) }' file.csv > checksums.csv
    

    The substr($2,8) removes prefix store:/ and /opt/xyz is prepended to complete the path. The system(…) command executes the cksum command on given path and the output is redirected to the output file.

    An example output would look like this:

    $ cat checksums.csv
    128361894 41 /opt/xyz/2018/7/20/11/35/3f176f4b-41a0-4ac4-a795-a2240ffbb7b9.bin
    820470222 41 /opt/xyz/2018/7/20/11/35/4302b390-1134-424d-a92f-ad27b233e8c1.bin
    1593587341 41 /opt/xyz/2018/7/20/11/35/5ff59679-b3ec-46d2-aa7d-5ec28eff6fe9.bin
    

    Since the output contains three columns checksum, byte count and path, we need to extract columns three and one in the next step.

  2. Print the header line and merge column1 (filename) of file.csv with column3 (path) and column1 (checksum) of checksums.csv.

    { echo "filename contenturl checksum";\
      paste -d ' ' <(awk -F'~' '{ print $1 }' file.csv) <(awk '{ print $3,$1 }' checksums.csv);\
    } > newfile.csv
    

    Example output:

    $ cat newfile.csv
    filename contenturl checksum
    000224.pdf /opt/xyz/2018/7/20/11/35/3f176f4b-41a0-4ac4-a795-a2240ffbb7b9.bin 128361894
    000238.pdf /opt/xyz/2018/7/20/11/35/4302b390-1134-424d-a92f-ad27b233e8c1.bin 820470222
    000262.pdf /opt/xyz/2018/7/20/11/35/5ff59679-b3ec-46d2-aa7d-5ec28eff6fe9.bin 1593587341
    

    Afterwards, clean up:

    rm checksums.csv
    
8
  • Thanks @Freddy, It is really awesome and faster solution, seems awk do operation in RAM itself. Thanks Again Commented Sep 24, 2019 at 12:26
  • Hi @Freddy,This solution works well but we have one more issue in it, filename contenturl checksum filename /opt/alfkofaxprd/alfresco-kofax-source/alfresco-kofax/alf_data/contentstore/2018/12/4/8/48/a922a5ee-7f93-4c2a-887b-97c29c916cb7.bin 1938377259 if you see filename after header print also filename instead real file name.. and thus at last I am also getting row like below 187242.pdf /opt/alfkofaxprd/alfresco-kofax-source/alfresco-kofax/alf_data/contentstore/2019/8/26/10/50/d74b942f-16fe-44ff-9fc7-434658f846ed.bin 931825045 187244.pdf what changes could be made here in awk? Commented Sep 27, 2019 at 10:06
  • Thanks a lot, issue here is I am not able to do proper formatting like asking a question. My Point here is whole row is shifted down in result generated, and header is printed in second row. if you see my above output I pasted in formatting it include header in first row and in second row first cell include "filename" instead actual filename. Commented Oct 3, 2019 at 12:50
  • check screenshot at link you will get idea what is issue...... Commented Oct 16, 2019 at 4:29
  • Ah, now I think I understand what you mean. I tested my solution with your original file and got strange output. The thing is that you have to remove the original header from your CSV file and any empty lines. When both files file.csv and checksums.csv are pasted together, they must have the same amount of lines, i.e. the columns of the first line are merged, then the columns of the second line and so on.
    – Freddy
    Commented Oct 16, 2019 at 5:14
0

GNU sed has the e flag for the s command allowing you to integrate shell processing like cksum in your script:

sed -E 'h;s_.*~store:/([^~]*).*_cksum /opt/xyz\1_e;x;G;s_~[^~]*(.*)\n(.*) .* (.*)_~\2~\3\1_;s/~/  /g'
  • option -E switches to extended regular expressions for better readability
  • h saves the current line in the hold space, so we are free to mess it up
  • s_.*~store:/([^~]*).*_cksum /opt/xyz\1_e matches everything including store:/ (to be removed), the filename inside the () to be reused as \1 and the rest of the line (to be removed) and replaces it by cksum + path. Now the e flag comes to action to execute this command and replace the pattern space with what is returned
  • x;G exchanges hold and pattern space and then appends hold to pattern space, so the buffer contains
  • s_~[^~]*(.*)\n(.*) .* (.*)_~\2~\3\1_ may be had to read, but easy to write: ~[^~]* is the second field, (.*)\n the rest of the original line and (.*) .* (.*) the three fields that are returned by cksum, so the second field gets replaced by the file name and the checksum
  • You don't write it in your question, but in the example you don't have ~ as delimiter, but two spaces: s/~/ /g

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .