Sponsored Content
Full Discussion: awk on csv files
Top Forums UNIX for Beginners Questions & Answers awk on csv files Post 303034110 by zozoo on Thursday 18th of April 2019 10:22:08 AM
Old 04-18-2019
awk on csv files

wk on csv files
Hi I have two csv files
1 ) keys.csv with data as below
Code:
XX1,offsite
XX2,offsite
XX8,none
XX3,offsite
XX4,onsite
XX7,none
XX5,onsite
XX6,onsite
XX15,none

2) source.csv file with data like

Code:
1,0123,ppp,XX1
2,0122,sss,XX2
3,1239,yyy,XX8
4,567,kkk,XX5
5,999,qqq,XX6
6,1234,sss,XX15
7,2456,ggg,XX3
8,635,ttt,XX4
9,987,www,XX7
10,154,sqt,XX7
11,1000,sss,XX15
12,1231,sss,XX15
13,1230,sss,XX15

output needed is after comparing files the 2nd field from first file should get appended to where ever the 1st field from file 1 matches with field 3 from second file
i.e sample output

Code:
1,0123,ppp,XX1,offsite
2,0122,sss,XX2,offsite
3,1239,yyy,XX8,none
4,567,kkk,XX5,onsite
5,999,qqq,XX6,onsite
6,1234,sss,XX15,none
7,2456,ggg,XX3,offsite
8,635,ttt,XX4,onsite
9,987,www,XX7,none
10,154,sqt,XX7,none
11,1000,sss,XX15,none
12,1231,sss,XX15,none
13,1230,sss,XX15,none

tried below it does not work any pointers on what could be wrong ?

Code:
awk -F ',' 'NR == FNR {f[$1] = $2;next} $3 in f { print $0","f[$1]}' keys.csv source.csv

 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

csv files (with quoted commas) and awk

I have a file as follows: 1,"This is field 2",3,4,5 2,"This is field 2 it can contain one , comma",3,4,5 3,"This is field 2 it also, can, contain, more",3,4,5 4,"This is field 2 without extra commas",3,4,5 and i pass this through to awk: awk -F, ' { if (... (3 Replies)
Discussion started by: Cranie
3 Replies

2. Shell Programming and Scripting

validation of data using filter (awk or other that works...) in csv files

Hello People I have the following file.csv: date,string,float,number,boolean 20080303,abc,1.5,123,Y 20080304,abc,1.2,345,N 20080229,nvh,1.4,098,Y 20080319,ugy,1.9,586,N 20080315,gyh,2.4,345,Y 20080316,erf,3.1,932,N I need to filter the date field where I have a data bigger than I... (1 Reply)
Discussion started by: Rafael.Buria
1 Replies

3. UNIX for Dummies Questions & Answers

Writing awk script to read csv files and split them

Hi Here is my script that calls my awk script #!/bin/bash set -x dir="/var/local/dsx/csv" testfile="$testfile" while getopts " f: " option do case $option in f ) testfile="$OPTARG";; esac; done ./scriptFile --testfile=$testfile >> $dir/$testfile.csv It calls my awk... (1 Reply)
Discussion started by: ladyAnne
1 Replies

4. Shell Programming and Scripting

how to give multiple csv files as input in awk

Hi All, I am new to shell scripting..My problem is i want to give multiple csv files as input to awk script and process the data into one file.. My input file is File1 File2 File3 Product Location Period SalesPrice A x 8/11/2010 ... (7 Replies)
Discussion started by: kvth
7 Replies

5. Shell Programming and Scripting

Merge 2 csv files with awk

I have 2 files pipe delimted and want to merge them based on a key e.g file 1 123$aaa$yyy$zzz 345$xab$yzy$zyz 456$sss$ttt$foo 799$aaa$ggg$dee file 2 123$hhh 345$ddd 456$xxx 888$zzz so if the key is the first field, and the result should be the common key between file 1 and 2 (6 Replies)
Discussion started by: loloAix
6 Replies

6. Shell Programming and Scripting

Using AWK to match CSV files with duplicate patterns

Dear awk users, I am trying to use awk to match records across two moderately large CSV files. File1 is a pattern file with 173,200 lines, many of which are repeated. The order in which these lines are displayed is important, and I would like to preserve it. File2 is a data file with 456,000... (3 Replies)
Discussion started by: isuewing
3 Replies

7. Shell Programming and Scripting

awk script to perform an action similar to vlookup between two csv files in UNIX

Hi, I am new to awk/unix and am trying to put together an awk script to perform an action similar to vlookup between the two csv files. Here are the contents of the two files: File 1: Date,ParentID,Number,Area,Volume,Dimensions 2014-01-01,ABC,247,83430.33,857.84,8110.76... (9 Replies)
Discussion started by: Prit Siv
9 Replies

8. Shell Programming and Scripting

Filter using awk in CSV files

Hello Gentlemen, Finding difficulties to play with my Input files:confused: . Your guidance will certainly help as always. After converting to csv file from XLSM file, I am getting some extra ""(double quote) characters which I want to terminate inside shell script and process it further. ... (6 Replies)
Discussion started by: pradyumnajpn10
6 Replies

9. UNIX for Beginners Questions & Answers

awk assistance - Comparing 2 csv files

Hello all, I have searched high and low for a solution to this, many have come really close but not quite what I'm after. I have 2 files. One contains GUID's, for example: 8121E002-96FE-4C9C-BC5A-6AFF20DACECD 84468F30-F3B7-418B-81F0-0908E80792BF A second file, contains a path to the... (8 Replies)
Discussion started by: tirmUK
8 Replies

10. Shell Programming and Scripting

Modify csv-files with awk

Hello everyone! I have thousands of csv files I have to import into a Database table. As usually the files aren't perfect. For example they have a different number of columns and some weird columns. The second problem is, that I have to add 3 parts of the filename into 3 rows in the... (6 Replies)
Discussion started by: elRonaldo
6 Replies
date(1) 							   User Commands							   date(1)

NAME
date - write the date and time SYNOPSIS
/usr/bin/date [-u] [ +format] /usr/bin/date [ -a [-]sss.fff] /usr/bin/date [-u] [ [mmdd] HHMM | mmddHHMM [cc] yy] [.SS] /usr/xpg4/bin/date [-u] [ +format] /usr/xpg4/bin/date [ -a [-]sss.fff] /usr/xpg4/bin/date [-u] [ [mmdd] HHMM | mmddHHMM [cc] yy] [.SS] DESCRIPTION
The date utility writes the date and time to standard output or attempts to set the system date and time. By default, the current date and time is written. Specifications of native language translations of month and weekday names are supported. The month and weekday names used for a language are based on the locale specified by the environment variable LC_TIME. See environ(5). The following is the default form for the "C" locale: %a %b %e %T %Z %Y For example, Fri Dec 23 10:10:42 EST 1988 OPTIONS
The following options are supported: -a [-]sss.fff Slowly adjust the time by sss.fff seconds (fff represents fractions of a second). This adjustment can be positive or nega- tive. The system's clock is sped up or slowed down until it has drifted by the number of seconds specified. Only the super- user may adjust the time. -u Display (or set) the date in Greenwich Mean Time (GMT--universal time), bypassing the normal conversion to (or from) local time. OPERANDS
The following operands are supported: +format If the argument begins with +, the output of date is the result of passing format and the current time to strftime(). date uses the conversion specifications listed on the strftime(3C) manual page, with the conversion specification for %C deter- mined by whether /usr/bin/date or /usr/xpg4/bin/date is used: /usr/bin/date Locale's date and time representation. This is the default output for date. /usr/xpg4/bin/date Century (a year divided by 100 and truncated to an integer) as a decimal number [00-99]. The string is always terminated with a NEWLINE. An argument containing blanks must be quoted; see the EXAMPLES section. mm Month number dd Day number in the month HH Hour number (24 hour system) MM Minute number SS Second number cc Century (a year divided by 100 and truncated to an integer) as a decimal number [00-99]. For example, cc is 19 for the year 1988 and 20 for the year 2007. yy Last two digits of the year number. If century (cc) is not specified, then values in the range 69-99 shall refer to years 1969 to 1999 inclusive, and values in the range 00-68 shall refer to years 2000 to 2068, inclusive. The month, day, year number, and century may be omitted; the current values are applied as defaults. For example, the following entry: example% date 10080045 sets the date to Oct 8, 12:45 a.m. The current year is the default because no year is supplied. The system operates in GMT. date takes care of the conversion to and from local standard and daylight time. Only the super-user may change the date. After successfully setting the date and time, date displays the new date according to the default format. The date command uses TZ to determine the correct time zone information; see environ(5). EXAMPLES
Example 1: Generating Output The following command: example% date '+DATE: %m/%d/%y%nTIME:%H:%M:%S' generates as output DATE: 08/01/76 TIME: 14:45:05 Example 2: Setting the Current Time The following command sets the current time to 12:34:56: example# date 1234.56 Example 3: Setting Another Time and Date in Greenwich Mean Time The following command sets the date to January 1st, 12:30 am, 2000: example# date -u 010100302000 This is displayed as: Thu Jan 01 00:30:00 GMT 2000 ENVIRONMENT VARIABLES
See environ(5) for descriptions of the following environment variables that affect the execution of date: LANG, LC_ALL, LC_CTYPE, LC_TIME, LC_MESSAGES, and NLSPATH. TZ Determine the timezone in which the time and date are written, unless the -u option is specified. If the TZ variable is not set and the -u is not specified, the system default timezone is used. EXIT STATUS
The following exit values are returned: 0 Successful completion. >0 An error occurred. ATTRIBUTES
See attributes(5) for descriptions of the following attributes: /usr/bin/date +-----------------------------+-----------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +-----------------------------+-----------------------------+ |Availability |SUNWcsu | +-----------------------------+-----------------------------+ |CSI |enabled | +-----------------------------+-----------------------------+ /usr/xpg4/bin/date +-----------------------------+-----------------------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +-----------------------------+-----------------------------+ |Availability |SUNWxcu4 | +-----------------------------+-----------------------------+ |CSI |enabled | +-----------------------------+-----------------------------+ |Interface Stability |Standard | +-----------------------------+-----------------------------+ SEE ALSO
strftime(3C), attributes(5), environ(5), standards(5) DIAGNOSTICS
no permission You are not the super-user and you tried to change the date. bad conversion The date set is syntactically incorrect. NOTES
If you attempt to set the current date to one of the dates that the standard and alternate time zones change (for example, the date that daylight time is starting or ending), and you attempt to set the time to a time in the interval between the end of standard time and the beginning of the alternate time (or the end of the alternate time and the beginning of standard time), the results are unpredictable. Using the date command from within windowing environments to change the date can lead to unpredictable results and is unsafe. It can also be unsafe in the multi-user mode, that is, outside of a windowing system, if the date is changed rapidly back and forth. The recommended method of changing the date is 'date -a'. Setting the system time or allowing the system time to progress beyond 03:14:07 UTC Jan 19, 2038 is not supported on Solaris. SunOS 5.10 11 May 2004 date(1)
All times are GMT -4. The time now is 11:17 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy