[단순반복작업은 파이썬으로!] 엑셀 빅(?)데이터 간단 작업

문제 : 전산프로그램 ERP에서 값을 빼온 후 엑셀로 비교하며 정리를 하는 업무

자동으로 진행하지 않을시, 품목코드 약 6천여개에 대해 최근 2개년치 약 5만여개 데이터를 확인해 기입하여야 함

 

1. ERP상의 원하는 데이터를 기간별로 엑셀로 추출하기

  - 기간별로 추출하는 이유는 엑셀최대 열 개수를 초과해서 추출되어서 데이터 누락이 발생됨.(아마 ERP프로그램 문제)

 

17년부터 19년까지 4개의 엑셀파일로 추출했고 품목코드와 단가를 비교해서 일치하면 단가를 입력하게 만드는 단순한 작업

 

2. 품목코드와 단가를 딕셔너리로 생성해 키값과 밸류값으로 나누고, 키값은 고정, 밸류값은 최근의 엑셀파일 일수록 최근의 단가로 정해짐. (자동으로 단가가 업데이트 됨)

 

3. 만들어진 딕셔너리로 입력할 엑셀을 불러와 키값을 비교하며 일치할때 밸류값을 입력하게 만들자 !

라고 계획은 거창하게 세웠지만 늘 계획은 계획대로 되지 않지..

 

코드

import openpyxl
wb = openpyxl.load_workbook(filename='불러올 파일.xlsx')
wb2 = openpyxl.load_workbook(filename='입력할 파일.xlsx')
a_sheet = wb['시트1']
b_sheet = wb2['시트1']

baseList = {}
for i in range(2,22052): # erp 엑셀화 갯수
 baseList[a_sheet['b' + str(i)].value] = a_sheet['e' + str(i)].value

for a in range(0,1167): # print(len(baseList)) 수량
 code = list(baseList.keys())[a]
 for b in range(2,6595): # 작성해야할 원가표 총 6594개
 if code == b_sheet['a'+str(b)].value:
 b_sheet['e'+str(b)] = baseList[code]
 if a < 10000:
 continue
wb2.save('완성본.xlsx')

5번째 줄까지는 파일의 시트를 불러오기

 

딕셔너리 만들고 불러올 파일의 품목코드 열 개수를 써주고

len함수로 딕셔너리 키값의 개수를 구해서 두번째 반복문을 돌린다.

 

키값의 리스트를 순서대로 code라는 변수명에 저장하고 해당 code가 입력할 파일에 일치하는지를 찾아서

일치할때 그 밸류값을 입력해라.

 

반복을 위해 a를 비교해서 컨티뉴

마지막 저장

 

 

약간 중구난방에 줄이려면 훨씬 줄일 수 있겠지만, 결과적으로 제대로 잘 입력이 되었다.

 

 

초반에 오류났던 2가지 내용이 있는데,

첫번째는 엑셀을 17년~19년로 한번에 긁어서 저장하면, 최대 저장한도를 넘어서는지 1만6천개 정도밖에 값이 안나왔다.

기간을 약 6개월로 끊어서 하니 정상적으로 긁어와졌다.(아마 ERP 아웃풋 오류?)

 

두번째는 품목코드가 비어있는 경우에 단가가 입력되있는경우인데, 품목코드는 None, 단가는 입력되는 과정이 반복되어서 그런건지, 일정구간 이후에 단가가 다르게 입력되어있어 모두 제거하고 진행했다.

 

 

* 개선사항

엑셀 4개를 한번에 하지 않고, 한개씩 불러서 저장하고,

다시 하나 불러서 직전에 저장된 파일에 덮어쓰고, 이런식으로 4번을 코드바꿔서 실행시켰는데

한번에 하려면 할수야 있지만 그냥 파일명 바꿔가면서 4번을 진행했다.

 

데이터가 1,2만개가 넘어가니 실행시간도 제법 걸리긴 했지만.. openpyxl은 웬만큼 사용하게 된듯하다.

 

 

# 회고

데이터 수만개를 다뤄본게 처음인데, 오류가 생각보다 금방 잡히고 제대로 기입된 결과를 보니 매우 뿌듯하다.

실행시간도 제법 오래 걸리긴 했지만.. openpyxl은 어느정도 사용할 수 있게 된듯?

덕분에 일주일동안 야근할 걱정이던 팀원들이 매우 만족했다는 후문.