Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: awk equivilent of Excel WEEKNUM()
Top Forums Shell Programming and Scripting awk equivilent of Excel WEEKNUM() Post 302908929 by Michael Stora on Friday 11th of July 2014 07:34:55 PM
Old 07-11-2014
Quote:
Originally Posted by Corona688
And what do you want to do for those?
Treat them as I defined in my first post.

I can always translate between Excel's method and mine because the first Saturday of a year is always either the 1st or after the 1st. Since none of the AWK available definitions of week number are day of the month based, that does not work.

Now if I were to advance to the next Saturday (which I know how to do in AWK--basically add 6 days of seconds and subtract one day of seconds times the weekday number from the %w format ) and test the week number with either %g or %G, it would work for most years but break for years in which the 1st falls on a Friday or Saturday. I could test for that and handle the exception but even that exception is not always easy as the correct week might be one more than the return value or 51 or 52 (and possibly even 53--some methods can split 54 week years, I don't know without looking if ISO 8601 can ) less than the return value.

Mike

---------- Post updated at 04:34 PM ---------- Previous update was at 11:59 AM ----------

Anyway, here is my working kludge which will break in a decade . . .

The parts in BOLD deal with the week number calculation.
Code:
    awk -F, 'BEGIN { getline < "'"$indexFile"'" #Discard Headers
                     # Build an associative array for left outer join
                     while (getline < "'"$indexFile"'" ) { split($0,locIndex,","); indexArr[locIndex[1]]=locIndex[2] }
                     close("'"$indexFile"'")
                 # These are the start dates (at midnight) for each Work Week "year".  The script will break after 2025.
                 yearStart[2010] = mktime("2009 12 27 00 00 00"); yearStart[2011] = mktime("2010 12 26 00 00 00");
                 yearStart[2012] = mktime("2012 01 01 00 00 00"); yearStart[2013] = mktime("2013 12 29 00 00 00");
                 yearStart[2014] = mktime("2013 12 29 00 00 00"); yearStart[2015] = mktime("2014 12 28 00 00 00");
                 yearStart[2016] = mktime("2015 12 27 00 00 00"); yearStart[2017] = mktime("2017 01 01 00 00 00");
                 yearStart[2018] = mktime("2017 12 31 00 00 00"); yearStart[2019] = mktime("2018 12 30 00 00 00");
                 yearStart[2020] = mktime("2019 12 29 00 00 00"); yearStart[2021] = mktime("2020 12 27 00 00 00");
                 yearStart[2022] = mktime("2021 12 26 00 00 00"); yearStart[2023] = mktime("2023 01 01 00 00 00");
                 yearStart[2024] = mktime("2023 12 31 00 00 00"); yearStart[2025] = mktime("2024 12 29 00 00 00");
                 yearStart[2026] = mktime("2025 12 28 00 00 00")
                 yearStart[2027] = mktime("9999 12 31 00 00 00") # to prevent array overflow in loop below
                    }
             NR==1 { print "headers" } # add the correct headers later
             NR>=2 { split($7,a," "); date = a[1]; time = a[2];
                     split(date,d,"/"); month = sprintf("%02d",d[1]); day = sprintf("%02d",d[2]); year = 2000 + d[3] % 100
                 dateStamp = mktime(year" "month" "day" 00 00 00")
                     $19 == "Y"? valid = "0" : valid = "N"
                     locID=indexArr[$6"-"$21]
                 for (i = 2010; dateStamp > yearStart[i]; i++); YYYY = i-1 # Last year that passed condition
                 WW = int(( dateStamp - yearStart[YYYY] ) / (7*24*60*60)) + 1 # now that we know the "year" how many weeks are we into it?
                 (YYYY == 2009| YYYY == 2026)? YYYYWW = "ERROR" : YYYYWW = YYYY * 100 + WW
                     print date","time","$23","$9","$10","$11","$12","YYYYWW","valid","locID","$6","$22}'

I'd love to find a more elegant solution. This just bothers me on a gut level. I have no data older than 2010 and I won't be doing the same job in >10 years but it is still a kludge.

Mike
Last edited by Michael Stora; 07-11-2014 at 09:33 PM..
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

use awk to read randomly located columns in an excel file

Hi, I have an excel file that have a random count of columns/fields and what im trying to do is to only retrieve all the rows under 2 specific field headers. I can use the usually command for awk which is awk 'print{ $1 $2}' > output.txt, but the location of the 2 specific field headers is... (9 Replies)
Discussion started by: mdap
9 Replies

2. Shell Programming and Scripting

PERL: Split Excel Workbook to Indiv Excel files

Hi, I am trying to find a way to read an excel work book with multiple worksheets. And write each worksheet into a new excel file using perl. My environment is Unix. For example: I have an excel workbook TEST.xls and it has Sheet1, Sheet2, Sheet3 worksheets. I would like to create... (2 Replies)
Discussion started by: sandeep78
2 Replies

3. Shell Programming and Scripting

printing two lines in awk as two columns in excel

hi guys, i would like to print two lines from a file as two adjacent columns using excel using awk.. i have this so far: awk '{for(i=1; i<=NF; i++) {printf("%s\n",$i)}}' "$count".ttt > "$count".csv #this to print the first line from the .ttt file as rows of the first column in the .csv... (9 Replies)
Discussion started by: npatwardhan
9 Replies

4. Shell Programming and Scripting

Comparison of Cells in EXCEL using awk

Hi I have 2 csv files which looks like the following and i have to compare the 2 CSVs and create a third file such that if the value of the 1st cell in A.CSV and the value of the first cell in the B.CSV are same, it should print "SAME" in the third file or else print NOT SAME. Likewise i need... (19 Replies)
Discussion started by: meva
19 Replies

5. UNIX for Dummies Questions & Answers

AWK to excel

I've looked at a few different posts and none have entirely answered my question. Wondering how i'd go about using AWK to export to an excel file, which i gather would be a .csv file from what I've read. Atm my AWK command will read 2 files and export 2 files. Each of those files have 2... (3 Replies)
Discussion started by: Aussiemick
3 Replies

6. Shell Programming and Scripting

Help with selecting column with awk for a txt file generated by excel

I am new to scripting/programming, so I apologize for any novice questions. I have a tab delimited text file that was saved from excel xls file. I am trying to select only the third column using awk command. My command line is as below: cat test.txt | awk '{print $3}' However, above... (8 Replies)
Discussion started by: SangLad
8 Replies

7. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

8. UNIX for Advanced & Expert Users

awk - If then else rule like Excel cell

Hi, I need extract / transpose where "Y" in the input file My input file is Item,EB,SB,SD,TP,GR LP,Y,N,N,N,Y GC,Y,N,N,N,N CO,N,Y,Y,Y,Y PS,Y,N,N,Y,Y Expecting output is EB-/'LP/',/'GC/',/'PS/' SB-/'CO/' SD-/'CO/' TP-/'CO/',/'PS/' GR-LP,CO,PS I appreciate for your help Regards (4 Replies)
Discussion started by: myrole
4 Replies

9. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

10. Shell Programming and Scripting

Excel vlookup function like value mapping with awk

I have two files File1 175552 st_497858.1 rs86052.1 rs92185.1 st_001022416.1 174841 175552_174841 179912 st_001122967.2 rs90435.1 rs89122.1 st_001022583.1 175545 179912_175545 179912 st_001122967.2 rs90435.1 rs89122.1 st_001022584.1 175545 179912_175545 179967 st_001256606.1 rs93516.2... (1 Reply)
Discussion started by: sammy777888
1 Replies
All times are GMT -4. The time now is 05:37 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy