How can I append new lines in a CSV file and modify them in Unix

Question

I am new to Unix in general and starting to learn shell scripting. I am working with a CSV file with the below sample rows (it's a large CSV file with 4 entries for each item):

Table 1
Item ID   Time                  Available   Location 
0001      02/02/2021  08:00     Y           NJ
0001      02/02/2021  09:00     N           UT
0001      02/02/2021  10:00     Y           AZ
0001      02/02/2021  11:00     Y           CA
0002      02/02/2021  08:00     Y           NJ
0002      02/02/2021  09:00     N           UT
0002      02/02/2021  10:00     Y           AZ
0002      02/02/2021  11:00     Y           CA      

I have another CSV with a bunch of item IDs as follows:

Table 2
Item ID   Item_Name   Item_Aux_ID    Item_Aux_name
1001      IT_1        3323           IT_Aux_1
1002      IT_2        3325           IT_Aux_2
1003      IT_3        3328           IT_Aux_3
1010      IT_4        3333           IT_Aux_4

I would like to create new entries in the first CSV file (one entry for each Item in the second CSV file). Each new entry should be the same as the first row of the Table1 with the Item ID replaced appropriately. The expected output would be:

Table 1
Item ID   Time                  Available   Location 
0001      02/02/2021  08:00     Y           NJ
0001      02/02/2021  09:00     N           UT
0001      02/02/2021  10:00     Y           AZ
0001      02/02/2021  11:00     Y           CA
0002      02/02/2021  08:00     Y           NJ
0002      02/02/2021  09:00     N           UT
0002      02/02/2021  10:00     Y           AZ
0002      02/02/2021  11:00     Y           CA  
1001      02/02/2021  08:00     Y           NJ
1002      02/02/2021  08:00     Y           NJ
1003      02/02/2021  08:00     Y           NJ
1010      02/02/2021  08:00     Y           NJ 

How do I write a script to achieve the above in Unix? Thanks in advance.

Answer 1

Try this; read comments for logics

awk '
NR==2           {SV = $0                # save the relevant info from file1
                 RG = $1
                }
NR != FNR       {if (FNR==1) next       # in new file: skip header
                 sub(RG, $1, SV)        # insert new "item ID" in saved line
                 RG = $1                # save new search pattern
                 sub($0, SV)            # replace input line with modified saved line   
                }
1                                       # print it
' file[12] 
Item ID   Time                  Available   Location 
0001      02/02/2021  08:00     Y           NJ
0001      02/02/2021  09:00     N           UT
0001      02/02/2021  10:00     Y           AZ
0001      02/02/2021  11:00     Y           CA
0002      02/02/2021  08:00     Y           NJ
0002      02/02/2021  09:00     N           UT
0002      02/02/2021  10:00     Y           AZ
0002      02/02/2021  11:00     Y           CA
1001      02/02/2021  08:00     Y           NJ
1002      02/02/2021  08:00     Y           NJ
1003      02/02/2021  08:00     Y           NJ
1010      02/02/2021  08:00     Y           NJ

Answer 2

I highly recommend using a CSV-aware tool to process CSV files, whenever you can. That awk script will work for very simple ("plain") CSV, but if your CSV ever has quotes, parsing in awk becomes problematic.

GoCSV is such a CSV-aware tool that has a number of commands that give you a lot of control over your data, and without having to write imperative code. You declare what you want as a series of steps (that can be pipelined):

#!/bin/sh

# Isolate 1st row from table1
gocsv head -n 1 table1.csv > first_row.csv

# Add a shared key to first_row and table2 (in the new column 'Key')
gocsv add -n Key -t '_key_' first_row.csv > first_row_keyed.csv

gocsv add -n Key -t '_key_' table2.csv > table2_keyed.csv

# Left-join first_row to table2, by matching on the shared key (the Key column)
gocsv join -c Key table2_keyed.csv first_row_keyed.csv > table2_joined.csv

# Select (cut) the first 'Item ID' (column 1) and the other 3 fields that were joined from first_row
gocsv select -c 1,'Time','Available','Location' table2_joined.csv > table2_cut.csv

# Stack table1 then new version of table2
gocsv stack table1.csv table2_cut.csv > final.csv

I mocked up your sample data:

table1

Item ID,Time,Available,Location
0001,02/02/2021  08:00,Y,NJ
0001,02/02/2021  09:00,N,UT
0001,02/02/2021  10:00,Y,AZ
0001,02/02/2021  11:00,Y,CA
0002,02/02/2021  08:00,Y,NJ
0002,02/02/2021  09:00,N,UT
0002,02/02/2021  10:00,Y,AZ
0002,02/02/2021  11:00,Y,CA

table2

Item ID,Item_Name,Item_Aux_ID,Item_Aux_name
1001,IT_1,3323,IT_Aux_1
1002,IT_2,3325,IT_Aux_2
1003,IT_3,3328,IT_Aux_3
1010,IT_4,3333,IT_Aux_4

When I run that script on those two files, I get this for final.csv:

Item ID,Time,Available,Location
0001,02/02/2021  08:00,Y,NJ
0001,02/02/2021  09:00,N,UT
0001,02/02/2021  10:00,Y,AZ
0001,02/02/2021  11:00,Y,CA
0002,02/02/2021  08:00,Y,NJ
0002,02/02/2021  09:00,N,UT
0002,02/02/2021  10:00,Y,AZ
0002,02/02/2021  11:00,Y,CA
1001,02/02/2021  08:00,Y,NJ
1002,02/02/2021  08:00,Y,NJ
1003,02/02/2021  08:00,Y,NJ
1010,02/02/2021  08:00,Y,NJ