Overclock.net › Forums › Software, Programming and Coding › Operating Systems › Linux, Unix › Unix join command help
New Posts  All Forums:Forum Nav:

Unix join command help

post #1 of 8
Thread Starter 
I am trying to join two files together but cannot get the command to work.
Code:
File1 is Last Name:First Name:DOB:SSN
File2 SSN:Date:GrossPay:NetPay

I need the join command to output in this order
SSN:LastName:Gross

My command I thought would work, but cannot get it to work is: join -t: -1 3 -1 1 -2 3 -o 1.4 1.1 2.3

Any guidance is appreciated!
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
post #2 of 8
Code:
join -t: [B]-1 3 -1 1 -2 3[/B] -o 1.4 1.1 2.3
Shouldn't you have only two of those? You are trying to match column one and three from first file to column 3 from the second file if i get this command correctly.
So i think it should be:
Code:
join -t: -1 4 -2 1 -o 1.4 1.1 2.3 file1 file2
    
CPUMotherboardGraphicsRAM
AMD Phenom II X2 550 M4N78 SE NVIDIA GeForce GTS 250 G. Skill 
Hard DriveCoolingMonitorKeyboard
WD Caviar Black 750GB Scythe Mugen 3 Samsung SyncMaster P2370 Razer Lycosa 
PowerCaseMouse
Seasonic M12II Bronze 650W Generic beige server case, on wheels. Mad Catz R.A.T. 5 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
AMD Phenom II X2 550 M4N78 SE NVIDIA GeForce GTS 250 G. Skill 
Hard DriveCoolingMonitorKeyboard
WD Caviar Black 750GB Scythe Mugen 3 Samsung SyncMaster P2370 Razer Lycosa 
PowerCaseMouse
Seasonic M12II Bronze 650W Generic beige server case, on wheels. Mad Catz R.A.T. 5 
  hide details  
Reply
post #3 of 8
Thread Starter 
Code:
111111111:last:3500
join: file 1 is not in sorted order
234845050:lastname:9900

For some reason the first line and third line work perfectly with your code! The second line... not so much?? Strange lol
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
post #4 of 8
Thread Starter 
For some reason it didnt like the format of one of my entries in the files. I removed the entry and its working fine now. Thank you!
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
post #5 of 8
I think that columns you are joining have to be sorted in both files.
    
CPUMotherboardGraphicsRAM
AMD Phenom II X2 550 M4N78 SE NVIDIA GeForce GTS 250 G. Skill 
Hard DriveCoolingMonitorKeyboard
WD Caviar Black 750GB Scythe Mugen 3 Samsung SyncMaster P2370 Razer Lycosa 
PowerCaseMouse
Seasonic M12II Bronze 650W Generic beige server case, on wheels. Mad Catz R.A.T. 5 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
AMD Phenom II X2 550 M4N78 SE NVIDIA GeForce GTS 250 G. Skill 
Hard DriveCoolingMonitorKeyboard
WD Caviar Black 750GB Scythe Mugen 3 Samsung SyncMaster P2370 Razer Lycosa 
PowerCaseMouse
Seasonic M12II Bronze 650W Generic beige server case, on wheels. Mad Catz R.A.T. 5 
  hide details  
Reply
post #6 of 8
Thread Starter 
Am I doing this correct? Its asking me to format the joined document using awk.
Code:
awk -F: '{printf "%-11s %30s %s\n", $1, $2, $3}' emppay

SSN LName Pay

SSN left justified allowing 11 characters in width
LName one tab away from SSN with 30 characters in width
Pay needs to be right justified and in numeric format up to 99999.99 in size
It is automatically right justified unless specified so that part is taken care of, but what about the size? I have been googling to make sure I did it correct but cant find a resource to use
Edited by kplonsky - 3/31/13 at 5:11pm
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
post #7 of 8
So, if you want to limit your columns to 11 and 30 chars you would use %-11.11s and %30.30s, this tells the awk that you want string padded to 11 characters and if it's longer than 11 characters to print only first 11 characters. Same with 30, - just tells it to be left justified.
To get 'tab' character you would use \t.
The number would be %8.2f, that should print your number right jusified with desired precision, but be careful since i think it will write bigger numbers with more than 5 digits. Also, % 8.2f should leave an additional place for sign, so that it's space on positive numbers and - on negative, and %+8.2f should always print sign for numbers. If you wonder why 8, it's because it defines the length of number, so 5 digits + dot + 2 digits is 8.
So final command would be (i assumed you wanted columns to be tab separated):
Code:
awk -F: '{printf "%-11.11s\t%30.30s\t%8.2f\n", $1, $2, $3}' emppay
    
CPUMotherboardGraphicsRAM
AMD Phenom II X2 550 M4N78 SE NVIDIA GeForce GTS 250 G. Skill 
Hard DriveCoolingMonitorKeyboard
WD Caviar Black 750GB Scythe Mugen 3 Samsung SyncMaster P2370 Razer Lycosa 
PowerCaseMouse
Seasonic M12II Bronze 650W Generic beige server case, on wheels. Mad Catz R.A.T. 5 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
AMD Phenom II X2 550 M4N78 SE NVIDIA GeForce GTS 250 G. Skill 
Hard DriveCoolingMonitorKeyboard
WD Caviar Black 750GB Scythe Mugen 3 Samsung SyncMaster P2370 Razer Lycosa 
PowerCaseMouse
Seasonic M12II Bronze 650W Generic beige server case, on wheels. Mad Catz R.A.T. 5 
  hide details  
Reply
post #8 of 8
Thread Starter 
I appreciate your help! My book was completely useless for awk.
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
X5660 P6T NVIDIA GeForce GTX 770 OCZ3x1333LV2G 
RAMRAMHard DriveHard Drive
OCZ3x1333LV2G OCZ3x1333LV2G Western Digital WD Green WD15EADS Crucial M4 SSD 120 GB 
Hard DriveOSMonitorKeyboard
Western Digital WD Blue WD10EZEX Windows 10 ASUS VG248QE Ducky Keyboard 
PowerCaseMouseMouse Pad
EVGA SuperNOVA 750G2 Fractal Design Define R5 Logitech G5 Steelseries 
Audio
Sennheiser HD 558 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Linux, Unix
Overclock.net › Forums › Software, Programming and Coding › Operating Systems › Linux, Unix › Unix join command help