“I want to add stock quantities to a product list” or “Combine a price list with a product master.”
Use product codes shared by two files to join their data horizontally.
Copying and pasting by row order can attach stock or prices to the wrong product. Keeping only matching products can also hide products present in just one file.
For code-based joins, check matching fields, which rows to retain, and unmatched data.
This article explains joining two files by product code with JIM-JIN’s Excel / CSV Merge and Split Tool and checking unmatched data.
What can a product-code join do?
It uses shared codes to add information from another file to the same row.
Suppose you have these two files.
Product list
| Product code | Product name |
|---|---|
| 00123 | Pen |
| 00045 | Notebook |
| 00067 | Sticky notes |
Inventory list
| Product code | Stock quantity |
|---|---|
| 00045 | 30 |
| 00123 | 100 |
| 00089 | 20 |
The inventory list has a different order and a partly different set of products.
Matching product codes and keeping all product-list rows produces:
| Product code | Product name | Stock quantity |
|---|---|---|
| 00123 | Pen | 100 |
| 00045 | Notebook | 30 |
| 00067 | Sticky notes |
Matching pens and notebooks receive their stock quantities. Sticky notes have no corresponding inventory code, so their stock field is empty.
Code “00089”, present only in inventory, is excluded with this setting.
The rows you choose to keep determine what data you can review.
Appending rows versus joining by product code
There are two main ways to combine Excel or CSV data.
| Method | Operation | Suitable task |
|---|---|---|
| Append rows | Append data rows from multiple tables | Combine monthly sales lists |
| Join by a shared column | Add fields to rows with matching codes | Add stock quantities or prices to a product list |
Here we use “Join horizontally by common fields.” That is the method.
Appending inventory below products does not add stock to each product. Choose the merge method according to the information you want to combine.
Four checks before joining
1. Do the codes identify the same products?
A “Code” column in both files does not necessarily have the same meaning.
Your product code and a supplier’s code may differ for the same product.
Check the meaning of the codes, not just the column names.
Conversely, “Product code” and “Part number” can be matching keys if they use the same numbering system.
2. Are leading zeros preserved?
“00123” and “123” are different strings as written.
Zeros missing from only one file can prevent a match for the same product.
| Product-list code | Inventory-list code | What to check |
|---|---|---|
| 00123 | 00123 | Codes match |
| 00123 | 123 | Were leading zeros lost? |
| AB-001 | AB001 | Does the presence of a hyphen matter? |
| ABC123 | ＡＢＣ１２３ | Are full-width and half-width characters mixed? |
Standardize codes according to the original management rules.
Adding zeros without knowing the required length or removing all symbols can make different products share a code.
3. Does the same code occur in multiple rows?
A product master may contain one row per product, but sales details or warehouse inventory can repeat the same code.
For example, inventory may look like this.
| Product code | Warehouse code | Stock quantity |
|---|---|---|
| 00123 | TOKYO | 100 |
| 00123 | OSAKA | 40 |
The product code alone does not identify a single row to join.
For warehouse-level matching, combine fields such as product code and warehouse code. For total stock per product, aggregate quantities before joining.
Joining and aggregation are separate operations.
4. Check rows with empty product codes
An empty code cannot reliably identify a product.
Empty values may match each other or be treated as duplicates, so review them before joining.
Verify unknown codes against the source system or product master rather than guessing.
Join by product code with JIM-JIN
JIM-JIN lets you choose a base file and an additional file and join them horizontally by common fields.
It supports .xlsx and CSV, including an Excel product list joined with CSV inventory.
Here is how to add stock quantities to a product list.
Step 1: Choose merging and add both files
Choose “Merge” and add the product and inventory lists.
For Excel, check the intended worksheet. For CSV, check readable Japanese text and correct column boundaries.
For tables with headers, treat the first row as field names.
Processing on your computer is the default. Server processing uploads files; choose it according to the on-screen guidance and your organization’s data rules.
Step 2: Choose joining horizontally by common fields
Select “Join horizontally by common fields.”
Appending rows does not match product codes. Use a horizontal join to add fields to product information.
Step 3: Choose the base file
Select the product list as the base file.
The base file forms the foundation of the output table.
Use products as the base when adding stock, or sales details as the base when adding product names. This clarifies the goal.
Step 4: Select product code as the matching field in both files
Select the product-code column in both the product list and inventory list.
| File | Matching field to select |
|---|---|
| Product list | Product code |
| Inventory list | Product code |
Even if column names differ, choose fields with the same meaning.
If product code alone cannot identify a row, use “Add matching field” and combine corresponding fields, such as warehouse code.
Step 5: Choose which rows to keep
Choose retained rows according to your goal.
| Settings | Rows in the result | Suitable purpose |
|---|---|---|
| Keep base-file rows | All base rows; unmatched additional fields are empty | Retain the product list and add stock quantities |
| Keep only matching rows | Rows whose product code appears in both files | Output only matched products |
| Keep all rows | Rows from both files, including products present in only one | Review all data, including unmatched products |
To investigate unmatched products, avoid starting with “Keep only matching rows.”
Unmatched products disappear from that output, making missing products harder to identify.
Keep base rows to review products, or keep all rows to inspect products unique to either file.
Step 6: Review duplicate-key handling
When matching keys repeat, JIM-JIN’s default is to stop for review.
Available choices include:
- Stop and review
- Use the first row
- Use the last row
- Create every combination
Do not choose the first or last row solely to bypass an error.
These choices do not automatically find the latest record by update date. Repeated keys in the base file may also cause some rows to be excluded.
Understand why codes repeat before choosing how to handle them.
Step 7: Review the preview and save
Check counts before and after joining, initial records, and whether product names match their stock quantities.
Choose Excel or CSV output for the intended use. For system imports, match CSV encoding and delimiters to the destination.
Server previews show only an initial portion, so also inspect the saved file.
Excel output is intended for joined data; it does not preserve the original workbook’s formatting, charts, or formulas.
How to check unmatched data
Review products present in only one file as well as matched rows to find missing registrations or inconsistent codes.
Keep all rows to check both sides
Joining the initial product and inventory examples while keeping all rows produces:
| Product code | Product name | Stock quantity |
|---|---|---|
| 00123 | Pen | 100 |
| 00045 | Notebook | 30 |
| 00067 | Sticky notes | |
| 00089 | 20 |
Comparing this result with the source reveals:
- “00067” is present in products but absent from inventory
- “00089” is present in inventory but absent from products
However, an empty joined field alone does not prove there was no match.
An originally empty stock quantity also appears empty after joining.
Add marker columns to distinguish unmatched rows from empty source values
For a clear distinction, add marker columns to working copies of the source files.
Add “Present in products” to the product list and “Present in inventory” to inventory, entering “Yes” in every data row.
Then keep all rows and interpret the result as follows.
| Product code | Present in products | Present in inventory | Result |
|---|---|---|---|
| 00123 | Yes | Yes | Present in both |
| 00045 | Yes | Yes | Present in both |
| 00067 | Yes | Present only in products | |
| 00089 | Yes | Present only in inventory |
“Result” is an explanatory column, not a classification automatically added by the tool.
This method shows whether a matching inventory code existed even if its stock quantity was empty.
Add markers to working copies and remove them from final import files according to the destination specification.
Investigate why a product appears on only one side
Do not immediately delete unmatched records; first investigate the reason.
| Situation | Possible reasons |
|---|---|
| Present only in products | Inventory not registered, new product, or excluded from inventory export |
| Present only in inventory | Missing product-master entry, old code, or discontinued product |
| The same product does not match | Differences in leading zeros, spaces, character width, or symbols |
| Only some products are missing | Different export dates, stores, warehouses, or filter conditions |
Even correct codes can fail to match if one file is current and the other is several days old. Check export conditions as well as code notation.
What to check when product codes do not match
Are there leading or trailing spaces?
“00123” and “00123 ” look similar, but the latter has a trailing space.
Copying, pasting, or external exports may introduce hard-to-see spaces.
Check for full-width spaces and line breaks as well as ordinary surrounding spaces.
Do character width or letter case differ?
Check differences such as “ＡＢＣ１２３” versus “ABC123”, or “abc123” versus “ABC123.”
Do not assume JIM-JIN’s horizontal join automatically normalizes matching values. Align source data according to its management rules.
Do not normalize case or symbols indiscriminately when they have meaning.
Do displayed codes differ from actual values?
Excel cell formatting may display leading zeros that are not part of the underlying value.
A displayed “00123” whose actual value is “123” may differ from the text value “00123.”
Check the imported preview to see the actual codes.
Joining cannot recover codes already truncated or rounded. Retrieve correct source data.
If row counts increase, check duplicate codes
Creating every combination can increase output rows according to the repeated codes on each side.
If one code appears twice in the base and three times in the additional file, it produces:
2 rows × 3 rows = 6 rows
This is because every combination is output.
Summing quantities or amounts afterward can count the same value repeatedly.
If there are more rows than expected, check:
- Does the product code repeat in both files?
- Are additional matching fields needed?
- Should data be aggregated per product before joining?
- Did retaining unmatched rows increase the count?
Do not delete rows merely because counts increased. First establish what each row represents.
Checklist after saving
Inspect the saved file before importing or aggregating it.
- Base products or detail rows were not unintentionally removed
- Codes, names, stock quantities, and prices correspond correctly
- Reasons for unmatched products have been reviewed
- Duplicate codes did not unexpectedly multiply rows
- Leading zeros and long codes are preserved
- Same-named fields from different sources are clearly distinguished
- CSV encoding and column order meet the destination requirements
If the additional file contains an identically named column, JIM-JIN retains it with a filename-qualified name rather than overwriting the original.
If both files have “Product name”, decide which source’s name to use in the final output.
Frequently asked questions
Can files be joined when product rows are in different orders?
Yes. Product-code keys match by code rather than row position.
Check duplicate and empty codes beforehand.
Can I use differently named columns such as Product code and Part number?
Yes, if they share a numbering system identifying the same products. Select corresponding matching fields in both files.
Similar column names alone are insufficient when numbering systems differ.
Can I join an Excel file with a CSV?
JIM-JIN supports .xlsx and CSV. Check the Excel worksheet and the CSV’s encoding and delimiters before joining.
Can unmatched products be retained?
Keep base rows to retain base products, or keep all rows to include products present only in either source.
Choose retained rows carefully while investigating unmatched data.
Can stock quantities for matching product codes be totaled automatically?
A horizontal join adds information to matching rows. It does not total inventory per product.
Aggregate warehouse-level inventory at the required level before joining.
Can I match on both product code and warehouse code?
Yes. Add matching fields and pair product code and warehouse code between the two files.
Combining fields can identify the intended record level when product code alone is insufficient.
