0

I have a .csv data file which I have manipulated to look like this:

   -------------PATH AND FILE NAME--------,USER--,DATE-----,TIME---,STATUS
   /Proj/abc/app/app_pit/conf/HUB_TEST.apx,uJones,4/24/2018,5:55:58,OK
   /Proj/abc/app/app_pit/conf/test_acqit1.apx,uSmith,3/8/2018,10:16:41,OK
   /Proj/abc/app/app_pit/conf/test_bkfs.apx,uHarry,/26/2018,9:47:03,OK

Which was fantastic! Until, I got some feedback and a request to subdivide the "PATH AND FILE NAME" column as such:

    LB,-PROJ---,FILENAME----,USER--,DATE-----,TIME---,STATUS
    app,app_pit,HUB_TEST.apx,uJones,4/24/2018,5:55:58,OK
    app,app_pit,test_acqit1.apx,uSmith,3/8/2018,10:16:41,OK
    app,app_pit,test_bkfs.apx,uHarry,/26/2018,9:47:03,OK

Omitting "/Proj/abc" from all records and removing "conf" from the path

I tried many different awk, sed, tr, etc apps but cant seem to get this right. Perhaps there is some looping logic to employ here?

2
  • Are /Proj/abc/ and /conf/ literal constant strings here? or do they vary from record to record? Jun 14, 2018 at 15:12
  • where does LB,-PROJ--- come from? Jun 14, 2018 at 15:14

5 Answers 5

1

Ignoring the header, and assuming that the text in the file has no spaces at the start of the line (it has in your question):

$ sed -e 's@/Proj/abc/\([^/]*\)/\([^/]*\)/conf/@\1,\2,@' file
-------------PATH AND FILE NAME--------,USER--,DATE-----,TIME---,STATUS
app,app_pit,HUB_TEST.apx,uJones,4/24/2018,5:55:58,OK
app,app_pit,test_acqit1.apx,uSmith,3/8/2018,10:16:41,OK
app,app_pit,test_bkfs.apx,uHarry,/26/2018,9:47:03,OK

The sed command captures the app and app_pit strings (which I assume can vary), and replaces the path in the original data with these with commas in-between while leaving the last bit of the path as its own field.

Redirect this to a new file and then fix the header (if needed).

2
  • @steeldriver Ah, the conf has to go too. I will avoid hardcoding app there though. Thanks!
    – Kusalananda
    Jun 14, 2018 at 15:23
  • 1
    Yes good idea - it's not clear how variable the OP's records are Jun 14, 2018 at 15:27
1
sed -e 's|-------------PATH AND FILE NAME--------,USER--,DATE-----,TIME---,STATUS|LB,-PROJ---,FILENAME----,USER--,DATE-----,TIME---,STATUS|' -e 's|/Proj/abc/||g' -e 's|/conf||g' -e 's|/|,|2' -e 's|/|,|1' file

Just an extended set of stream replacements.

The first changes the first line to starting with the string beginning with LB that you want.

The second removes /Proj/abc/.

The third removes /conf.

The fourth and fifth replace the 2nd and 1st forward slashes with commas.

Output:

LB,-PROJ---,FILENAME----,USER--,DATE-----,TIME---,STATUS
app,app_pit,HUB_TEST.apx,uJones,4/24/2018,5:55:58,OK
app,app_pit,test_acqit1.apx,uSmith,3/8/2018,10:16:41,OK
app,app_pit,test_bkfs.apx,uHarry,/26/2018,9:47:03,OK
2
  • Hello Nasir and thanks for your reply. I notice your solution has still has "PATH and FILENAME" which needs to be divided into LB, PROJ, FILENAME but neither way is printing the header. The data looks great! Any ideas?
    – SSDdude
    Jun 19, 2018 at 14:08
  • @SSDdude The sed -e only sends the changes to std output and it changes "PATH and FILENAME" to LB, PROJ, FILENAME when I do it on my machine with the output as what's above in my answer. If you want the changes to the file, add -i after sed. Are you using gnu sed? Jun 19, 2018 at 14:33
0

Note that this will catch fire if any of the filenames have a , or / in them:

$ awk -F "[/,]" -v OFS="," 'BEGIN { print "LB", "-PROJ--", "FILENAME----", "USER--", "DATA-----", "TIME---", "STATUS" }NR!=1{print $4,$5,$7,$8,$9"/"$10"/"$11,$12,$13}' input
LB,-PROJ--,FILENAME----,USER--,DATA-----,TIME---,STATUS
app,app_pit,HUB_TEST.apx,uJones,4/24/2018,5:55:58,OK
app,app_pit,test_acqit1.apx,uSmith,3/8/2018,10:16:41,OK
app,app_pit,test_bkfs.apx,uHarry,/26/2018,9:47:03,OK
0

Awk solution:

awk 'BEGIN{ FS = OFS = "," }
     NR == 1{ $1 = "LB,-PROJ---,FILENAME----" }
     NR > 1{
         gsub("/(Proj/abc/|conf)", "", $1);
         gsub("/", ",", $1)
    }1' file

The output:

   LB,-PROJ---,FILENAME----,USER--,DATE-----,TIME---,STATUS
   app,app_pit,HUB_TEST.apx,uJones,4/24/2018,5:55:58,OK
   app,app_pit,test_acqit1.apx,uSmith,3/8/2018,10:16:41,OK
   app,app_pit,test_bkfs.apx,uHarry,/26/2018,9:47:03,OK
0

SOLVED!

    sed -e 's|-------------PATH AND FILE NAME--------,USER--,DATE-----,TIME---,STATUS|LB,-PROJ---,FILENAME----,USER--,DATE-----,TIME---,STATUS|' -e 's|/Proj/abc/||g' -e 's|/conf||g' -e 's|/|,|2' -e 's|/|,|1' file

Thanks Nasir Riley, your sed solution worked flawlessly!!

1

You must log in to answer this question.

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