Shell Programming and Scripting

I have a shell script that is currently transferring a csv file from a server into a Teradata database table. One of the 30 or so columns is called "destination_url". In that URL there are parameters, and it is possible for those parameters to be repeated because of referring companies copying referred URLs from one another. So, for example one of these urls might look like this:

http://othercompany.com/media/redir.php?prof=403&camp=3&affcode=kw1266732&cid={creative}&networkType={ifContent:content}&url[]=http://www.mycompany.com/search?keywords=shelf&taxonomy=dep32&cid=128513&kid=169896850098703&TRACK=PSGGL

My task is to take the cid value and kid value and pull them out and place them in two columns. The two new columns will be called cid_value and kid_value. I should point out that the source destination_url will remain unchanged. I just need the two values from it.

The problem is that the urls SOMETIMES have more than one cid and/or kid. So usually I need to pull the first instance of the cid and kid. cid and kid will always be numeric. cid will always have 7 digits. kid can have anywhere from 6 to 14 digits.

I know this is possible to do in shell but I have NO idea how to do this. Can someone help? I'm new to shell scripting. Feeling totally lost and without help. I know it will be using sed and awk and I'm trying to read about them but nothing is clicking yet.

Please use code tags as required by forum rules!

This is quite incomplete a specification. Nothing is said about field separators, filed count per line, whatsoever. No header line is given to add the "cid_value" etc. strings to. The first cid entry has "{creative}" assigned; so what needs to be extracted - the first entry or the one with numbers or the first with numbers?
With quite some guesses & assumptions, I came up with

Code:

awk     'match($0,"cid=[0-9]*\&") {$(NF+1)=substr($0, RSTART+4, RLENGTH-5)}
         match($0,"kid=[0-9]*\&") {$(NF+1)=substr($0, RSTART+4, RLENGTH-5)}
         1
        ' file

Give it a shot and come back with results.

Thanks. My apologies. I'm trying to post as best I can. New guy! The field seperater is "

Code:

DLE

". There are 65 fields in the source file. The end will have 2 additional (cid_value and kid_value). Destination_url is the 34th column of the given 65 columns.

You are correct, I am looking for the first numerical instance of cid and kid. I posted the example I posted because it shows a little bit of the variability of the Destination_url column.

I am going to play with your example above. I'm not sure how to work that into my existing shell file but I will try my best.

---------- Post updated at 02:28 PM ---------- Previous update was at 02:03 PM ----------

Perhaps this will help. In my shell script there is a use of sed and awk that is taking another column called "adGroup" that is parsing out 4 columns. This has NOTHING to do with my project. But it also shows what I have come up with. Its not working yet, but it isn't erroring out either. I don't understand if I'm using the match command correctly. Nor do I understand what the end ' file does. Does that need to be replaced with my output table, which in this case is called edw_work.unfiltered_table? Either way here is the current code:

Code:

    # The data is the passed to an awk statement that breaks the ad grou:q!:p column (column 2) up into up to 10 columns. See below for more comments.
      sed -e1d $SRC_PATH$SRC_FILE_NAME$NEXTDATE$SRC_FILE_EXT | awk -F"" -v OFS="" '{ for (i=4;i<NF;i+=1) { gsub(",", "", $i) }; print }' | sed 's/\"//g' > $DEST_PATH$DEST_FILE_NAME$NEXTDATE$DEST_FILE_EXT 2>> $LOG_FULL

	  
	  #Craig's awk statement to take the destination_URL column and pull the correct cid and kid from it and insert it into new columns (cid_value and kid_value)that will be directly after the destination_url column
	  awk     
		'match($34,"cid=[0-9]*\&") {$(NF+35)=substr($0, RSTART+4, RLENGTH-6)}
         match($34,"kid=[0-9]*\&") {$(NF+36)=substr($0, RSTART+4, RLENGTH-14)}
         1
        ' file

Don't use NF+35 nor 36. NF+1 will add one field at the end as requested, and then again. There may be awk versions that do not allow this type of assignments. If the field count is known and constant, you can use constant values as well, i.e. NF+1 and NF+2