Linux下实现两个带表头TXT表格的左连接：问题排查与正确实现方案

Let's break down what went wrong with your current approach and fix it step by step—you're close, just missing a few key details about how join handles sorting and table joins, plus some tweaks to your awk logic.

First: Why Your Initial join Attempts Failed #

The core issues are:

• You sorted headers with data: When you ran plain sort on your files, you included the header line, which got mixed into the sorted data rows. join expects files to be sorted only on the join field (PROJECT_ID), so headers disrupting this order caused the "not sorted" errors.
• Default join is an inner join: The standard join command only keeps rows that exist in both tables—you need to explicitly tell it to retain all rows from your left table (PROJECT_NAME.txt).
• Dictionary vs numeric sorting: Plain sort uses lexicographical order (so "10" comes before "2"), but join needs numeric sorting on PROJECT_ID to match rows correctly.

Fix 1: Use join Correctly (With Proper Sorting) #

First, we'll sort the data rows without touching the headers, then run join with the left-join flag.

Step 1: Sort each file separately (preserve headers) #

For PROJECT_NAME.txt:

# Extract header, sort data rows numerically by PROJECT_ID, then recombine
head -n 1 PROJECT_NAME.txt > sorted_project_name.txt
tail -n +2 PROJECT_NAME.txt | sort -k1,1n >> sorted_project_name.txt

For PROJECT_LOCATIONS.txt:

head -n 1 PROJECT_LOCATIONS.txt > sorted_project_locations.txt
tail -n +2 PROJECT_LOCATIONS.txt | sort -k1,1n >> sorted_project_locations.txt

The -k1,1n flag tells sort to sort only the first column, treating it as a number instead of text.

Step 2: Run the left join with join #

Use the -a 1 flag to retain all rows from the first (left) file, and -o auto to automatically include all fields in their original order:

join -a 1 -1 1 -2 1 -o auto sorted_project_name.txt sorted_project_locations.txt > combined.txt

Let's break down the flags:

• -a 1: Keep all rows from the first input file (even if no match exists in the second)
• -1 1: Use column 1 from the first file as the join key
• -2 1: Use column 1 from the second file as the join key
• -o auto: Output all fields from both files in their original order

This will produce exactly your expected output, with no missing rows, correct headers, and single-line merged entries.

Fix 2: Use awk for More Flexibility (No Pre-Sorting Needed) #

If you prefer to avoid pre-sorting entirely, awk lets you handle the join directly with more control. Your original awk script had the wrong traversal order (it looped through the right table instead of the left), which caused the line-break issues.

Here's the corrected awk script:

awk '
BEGIN {
    # Print the combined header first
    print "PROJECT_ID PROJECT_NAME PROJECT_DUE_DATE CITY STATE ZIP"
}
# Process the left table (PROJECT_NAME.txt) first
FNR == NR {
    if (FNR > 1) {
        # Store name and due date mapped to PROJECT_ID
        project[$1] = $2 " " $3
    }
    next
}
# Process the right table (PROJECT_LOCATIONS.txt)
FNR > 1 {
    # Store location data mapped to PROJECT_ID
    location[$1] = $2 " " $3 " " $4
}
# After processing all files, output left table rows + matching locations
END {
    # Iterate through all PROJECT_IDs from the left table
    for (id in project) {
        print id, project[id], location[id]
    }
}
' PROJECT_NAME.txt PROJECT_LOCATIONS.txt | sort -k1,1n > combined.txt

• We first read and store all data from the left table, then the right table.
• Finally, we loop through every ID from the left table to ensure all rows are retained, merging in location data where it exists.
• The final sort ensures the output is ordered by PROJECT_ID, matching your expected result.

Verify the Output #

Either method will produce your desired combined table:

PROJECT_ID PROJECT_NAME PROJECT_DUE_DATE CITY STATE ZIP
1 SCUTUM 02/01/2022 LAKEWOOD CO 80227
4 VERITAS 04/10/2022 LAKEWOOD CO 80228
7 GLADIUS 06/30/2022 DENVER CO 80237

内容的提问来源于stack exchange，提问作者Martha