1

I have two separate CSV files. I want to scan through both CSV files and find SKU numbers that match. And if they do match, update the number in sheet 2 from the number in sheet 1 that correlates to its SKU numbers.

  import csv
  
  with open("C:\\Users\\Owner\\OneDrive\\Desktop\\Test\\read.csv", 'r') as file:
  # Create a CSV reader
  reader = csv.reader(file)
  rows1 = [row for row in reader]

  with  open("C:\\Users\\Owner\\OneDrive\\Desktop\\Test\\import.csv", 'r') as file:
  reader = csv.reader(file)
  rows2 = [row for row in reader]

  column1_values = [row[7] for row in rows1]
  column2_values = [row[8] for row in rows2]
  column3_values = [row[8] for row in rows1]

  for row in rows1:
      value2 = row[7]

  for row in rows2:
      value1 = row[8]


  if value1 in column1_values and value2 in column2_values:
    value3 = column3_values
    print(value3)

So far, I have the python code able to read through both files. However I am having trouble with matching the sku numbers and then updating the inventory numbers after.

First File that I want to extract data from:

,,kh10,,,,0
,,kh12,,,,21
,,kh13,,,,1
,,kh11,,,,45
,,kh20,,,,26

Second File that I want to update:

,kh20,,,0
,kh16,,,47
,kh12,,,31
,kh10,,,2
,kh13,,,0

I want to match those KH numbers, and then after from the first file extract the numbers from the last column in the first file and replace the last column in the second file with those numbers.

Pranav Hosangadi
  • 23,755
  • 7
  • 44
  • 70
nealgoogs
  • 21
  • 5
  • What is the problem with the sku numbers? Either they match or they don't. Or are some damaged? perhaps leading / trailing spaces? – Solar Mike Jan 10 '23 at 15:59
  • 1
    Hi. Please edit your question and fix the indentation of your code, it's not exactly clear how it should be indented. Also, please include a small sample of your input CSVs and how you expect one of them to be updated. – Zach Young Jan 10 '23 at 16:12
  • @SolarMike the problem is one file has like 47000 sku numbers, and the other file has only 23000 sku numbers. I only care about the 23000 sku numbers, but I need to extract the data from 47000 and update the numbers in the 23000. – nealgoogs Jan 10 '23 at 16:51
  • @ZachYoung Hey I added a small sample. Help this helps. – nealgoogs Jan 10 '23 at 17:34
  • I transcribed your CSV files to text based on your screenshots, and updated your question. In the future, please post text _as text_, [not as a screenshot](//meta.stackoverflow.com/q/285551). This allows us to copy the text to use in our code, instead of spending time transcribing the file. – Pranav Hosangadi Jan 10 '23 at 18:06

1 Answers1

1

You want to create a dictionary, where the keys are the SKU numbers, and the values are the quantities that you want in the result.

 with open("C:\\Users\\Owner\\OneDrive\\Desktop\\Test\\read.csv", 'r') as file:
    # Create a CSV reader
    reader = csv.reader(file)
    inventory = {row[2].strip(): int(row[7]) for row in reader}

This should create an inventory dictionary that looks like:

{'kh10': 0, 'kh12': 21, 'kh13': 1, 'kh11': 45, 'kh20': 26}

Now, as you read your other file, replace its E column with the corresponding values from this dict:

with open("C:\\Users\\Owner\\OneDrive\\Desktop\\Test\\import.csv", 'r') as file:
    reader = csv.reader(file)
    rows2 = [] # Create an empty list to hold all rows
    for row in reader:
        sku = row[1]
        current_inventory = int(row[4]) # Current value

        # Get updated value, keep current value if it doesn't exist in lookup dict
        updated_inventory = inventory.get(sku, current_inventory)

        # Store updated value
        row[4] = updated_inventory

        # Append row to master list
        rows2.append(row)

Now, you have a list of lists containing the data in your second CSV file, but with updated inventory numbers.

[['', 'kh20', '', '', 26],
 ['', 'kh16', '', '', 47],
 ['', 'kh12', '', '', 21],
 ['', 'kh10', '', '', 0],
 ['', 'kh13', '', '', 1]]

You probably already know how to write this to a CSV file. If not, see this answer

Pranav Hosangadi
  • 23,755
  • 7
  • 44
  • 70