Hi ,I got one data file from external source and I have to load it in database through sql loader. I want to add 2 columns in database,one is file name and one is time of the file received on server.
-rwxr-x--- 1 user user 238 Jun 03 16:32 90936264971326030616.ctr
From above case, I want one field as 'Jun 03 16:32' and one as "90936264971326030616" (file name without extension).
Thanks in advance
Not sure if your specifications requires the quotations or not but what about:
awk '{print "\""$6,$7,$8"\"","\""substr($9,1,length($9)-4)"\""}' filename
Will produce:
"Jun 03 16:32" "90936264971326030616"
Quite sparse a request, no? What tools would be preferred? What would the data be used for, and how? How or where to store the data? In a file? In stdout (to be piped into sth.)? In shell variables?
Try
awk '{sub (/\..*$/, _, $NF); print $NF; print $(NF-3) " " $(NF-2) " " $(NF-1)}' file
90936264971326030616
Jun 03 16:32
Hi All,
Thanks for your response
The actual requirement I have is to take the timestamp (file name here can be ignored), append it to all rows in the file so that it can loaded in database through sqlldr.
Once I extract the timestamp of the file in UNIX and pass it in one variable like $1 then I can further use the below command to make a new data file which I will load.
sed -e 's/$/ May 04 09:02/' summary_file.sql > Data_file.sql
I hope I am clear now.
I tried your suggested command :
File content is:-
***************
-rw-r----- 1 owner owner 976 May 04 09:02 summary_file.sql
cat summary_file.sql
REPORT_ID REPORT_NAME FILE_NAME FILE_DESCR RPT_GEN_DT PARTNER_NAME HQ_ID SOLD_TO SOLD_TO_NAME RO_SEGMENT REPORT_CATEGORY SUB_CATEGORY LOB SUB_LOB FISCAL REGION SALES_ORG EXT_PUB_IND DATA_LOAD_TS REPORT_GEN_IND REPORT_GEN_TS CREATE_TS SUB_REGION_CD summary_file1
Approach 1:-
***********
awk '{print "\""$6,$7,$8"\"","\""substr($9,1,length($9)-4)"\""}' summary_file.sql
Output
*****
"PARTNER_NAME HQ_ID SOLD_TO" "SOLD_TO_"
It gives me 6,7 and 8th field.
Approach 2:-
***********
awk '{sub (/\..*$/, _, $NF); print $NF; print $(NF-3) " " $(NF-2) " " $(NF-1)}' summary_file.sql
It gives me output as :-
summary_file1
REPORT_GEN_TS CREATE_TS SUB_REGION_CD
It is not giving me time. It just picks up the last field in one line and other last 3 columns in one line.
Please help.
An excellent example how incomplete and/or inaccurate specifications DO waste peoples' and requestor's time and effort!
To start from the beginning: What OS and shell version do you use? Do you have the stat or equivalent command at your disposal?
Sorry for wasting your time ..its my first time to ask for a help like this..not sure what all info is required. Shell version is
/usr/bin/ksh
. It is an AIX server
What be the stat equivalent on your system that can give the file's time stamp explicitly? (OK, if need be we can extract that from ls -l )
Not sure what do you mean by
stat
equivalent. We use
ls-l
or
ls-ltr
to check timestamp. Hope it helps.
stat -c"%y" file
2016-03-14 11:58:07.955460988 +0100
can yield single details from the file system directly.
stat command does not work for me in ksh shell.
Tried using this instead :-
ls -l Data_file.sql | awk '{print $6,$7,$8}'
Jun 08 05:56
Any idea if I can convert this out put to following format
08/06/2016 05:56:50
Any help would be appreciated
The locale definition might help...
Hello Ruchika,
If you have GNU version of ls command then following may help you in same.
ls -l Input_file --time-style="+%d/%m/%Y %H:%M:%S" | awk '{print $(NF-2) OFS $(NF-1)}'
Thanks,
R. Singh
GNU date available? Try
DT=$(date -d "$(LC_ALL=C ls -l summary_file.sql | awk '{print $6,$7,$8}')" +"%y/%m/%d %H:%M:%S")
sed -e "s/$/ $DT/" summary_file.sql > Data_file.sql
GNU date can be used more simply than that with its -r reference file option:
DT=$(date -r summary_file.sql +"%y/%m/%d %H:%M:%S")
GNU date might be available on some Solaris as gdate or /usr/bin/gnu/date.
If you don't have GNU date, but do have Perl, you could try my GNU date stand-in which also supports -r.
That never fell into place...
Hi All,
I want some help once again. I tried many commands for picking the system timestamp for a particular file.
by
ls -l
, i am only getting date and time but not year, so this command is not fulfilling my requirement
ls -l 79396246719284800001_WBR_REPORT_PROCESS_STATUS_1467192848.mdata | awk '{print $6,$7,$8}'
Jun 29 09:43
I want the date with current year also and final out put is required in following format
29/06/2016 09:43:00
.
Stat is not working in unix .I tried istat ,it gives me very elaborated output , through which I grep only last modified line and found below output :-
istat file.ksh |grep "Last modified:"
Last modified: Fri Jul 1 06:31:43 GMT 2016
Please help in converting to dd/mm/yyyy hh24:mi:ss
Regards
Ruchika
Did you realise Corona688's post#14?
Yes RudiC , i went through Corona688's post#14 but
date -r
and
date -d
doesn't work for me.
date -r WBR_REPORT_PROCESS.log +"%y/%m/%d %H:%M:%S"
date: Not a recognized flag: r
Usage: date [-u] [+"Field Descriptors"]
I have never used Perl. But can try if you can provide me any specific command.
Regards
Ruchika
There's a link in post#14...?
Thanks for all your help..I am done with everything else..one last help required is with sed command. When i am using this command in shell script
I used a variable vfile2 to define some value
v_File2=`echo ${NPI_DIRECTORY}/$file_name | cut -d'_' -f1`
when I try to print in my logs, i am getting the value of this variable correctly.
But when I try to use it in sed command then it appends ${v_File2} in the file,instead of its value.
`sed -e 's/$/ ${v_File2}/' ${NPI_DIRECTORY}/$file_name > ${NPI_DIRECTORY}/${file_name}_temp`
I tried it in many styles ( ${v_File2}, "${v_File2}", $v_File2,%${v_File2}) but nothing worked .
File after sed command :-
FILE_NAME REPORT_ID REPORT_NAME REPORT_NAME1 ID1 ID2 REPORT_CATEGORY CATEGORY REGION CREATION_TIMESTAMP TIMESTAMP ${v_File2}
Regards
Ruchika