파이썬으로 여러개의 엑셀 파일에서 필요한 값들만 한 번에 가져오기 (엑셀 크롤링)

회사에서 일을 하다 보면 엑셀 파일로 작성된 템플릿(양식)을 여러 사람에게 배포하고, 양식에 따라 작성한 뒤 제출하도록 안내하는 경우가 있다. 그 결과를 수집해서 보려면 파일을 하나씩 열어서 살펴봐야 하는 단순 반복 업무가 발생한다. 게다가 그렇게 작성된 값들을 한 곳에 모으려면 수고가 이만저만이 아니다.

물론 좋은 성능의 PC와 빠른 복붙의 손놀림을 가졌다면 얼마 안 되는 양은 금방 해결할 수 있다. 그러나 만약 그 양이 방대하다면 어떻게 해결하는 게 좋을까?

그래서 파이썬으로 동일한 양식의 엑셀파일에서 필요한 값만 긁어 모아 새로운 엑셀파일로 저장하는 코드를 짜보았다. 나는 이 과정을 ‘엑셀 크롤링‘이라고 부르기로 하겠다. 이런 작업을 일컫는 구체적인 표현이 있는지는 모르겠지만, 어쨌든 여러 엑셀 파일에서 값을 긁어온다는 의미로 그렇게 부르기로 한다.

이제 단계별로 하나씩 따라해보자.

1. 엑셀 파일을 한 곳에 모으고 공통된 양식을 확인한다.

본 예제에 사용하기 위해 excel_crawling이라는 폴더 안에 files라는 하위폴더를 생성하여 그 안에 a,b,c라는 이름으로 각각 엑셀파일을 만들었다.

각 엑셀 파일에는 다음과 같이 파일마다 다른 값이 입력된 것을 볼 수 있다.
C2: 이름 / C3: 성별 / E2: 직업 / E3: 나이

a, b, c 엑셀파일 Sheet1에 입력된 값들

이 작업의 목표는 다음과 같은 값들을 한 곳에 모아 다른 엑셀 파일에 저장하는 것이다.

  • a.xlsx, 배트맨, 남성, 백수, 23
  • b.xlsx, 엑스맨, 남성, 싸움꾼, 38
  • c.xlsx, 슈퍼맨, 남성, 영웅, 32

2. 파이썬 os 모듈을 활용하여 files 폴더에 저장된 모든 파일명을 리스트로 저장한다.

일단 파이썬 os 모듈을 활용해서 하위폴더 files에 저장된 모든 파일들의 이름을 리스트로 저장하고 출력하는 코드를 작성한다.

import os

path = "./files"
file_list = os.listdir(path)
print(file_list)

출력 결과는 다음과 같다.

['a.xlsx', 'b.xlsx', 'c.xlsx']

3. openpyxl을 활용해 엑셀 파일의 값들을 리스트의 리스트로 저장한다.

openpyxl은 엑셀 파일을 손쉽게 다룰 수 있는 파이썬 라이브러리다. 가장 널리 쓰이는 라이브러리이며, 다른 엑셀 관련 라이브러리보다 가볍고 편리해서 좋다. 자세한 내용은 홈페이지를 참조하자. 설치는 아래와 같은 pip 명령어로 쉽게 진행할 수 있다.

pip install openpyxl

이제 openpyxl의 load_workbook이라는 모듈을 이용할 것이다. for 반복문을 활용해 아까 저장한 파일 이름을 찾아 하나씩 열고, 아래와 같은 구체적인 단계를 통해 값들을 리스트의 리스트로 저장한다.

  1. results라는 이름의 빈 리스트를 생성한다. (이곳에 최종적으로 각 파일에서 수집한 값들을 리스트의 형태로 저장할 것이다.)
  2. result라는 이름의 빈 리스트를 생성한다. (하나의 파일에서 수집된 값을 담는 임시 저장소로 활용한다.)
  3. 각 파일을 열고 그 파일에서 원하는 값을 찾아 result에 순서대로 추가한다.
  4. result를 results에 추가한다.
  5. for 반복문을 활용해 다음 파일을 열고 위 단계를 반복한다.

구체적인 코드는 아래와 같다.

from openpyxl import load_workbook

results = []

for file_name_raw in file_list:

    file_name = "./files/" + file_name_raw
    wb = load_workbook(filename=file_name, data_only=True)
    ws = wb.active

    result = []
    result.append(file_name_raw)
    result.append(ws['C2'].value)
    result.append(ws['C3'].value)
    result.append(ws['E2'].value)
    result.append(ws['E3'].value)

    results.append(result)

print(results)

wb.active는 현재 활성화된 워크시트를 말한다. 여기서 ws[‘C2’].value와 같은 방식으로 C2셀의 값을 찾아오는 것이다.

출력하면 아래와 같은 결과 값을 얻을 수 있다. (리스트 첫번째 값으로는 파일명을 추가했다.)

[['a.xlsx', '배트맨', '남성', '백수', 23], ['b.xlsx', '엑스맨', '남성', '싸움꾼', 38], ['c.xlsx', '슈퍼맨', '남성', '영웅', 32]]

4. openpyxl을 활용해 수집한 값들을 하나의 엑셀파일에 쓰고 저장한다.

이번엔 Workbook이라는 모듈을 활용한다. ws.append를 활용하면 하나의 행에 하나의 리스트를 추가하는 방식으로 편리하게 사용할 수 있다.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

for i in results:
    ws.append(i)

wb.save("results.xlsx")

위에 나열된 코드 묶음 세 개를 이어서 실행하면 다음과 같이 results라는 엑셀 파일이 생성되고 그 안에 값이 정리되어 있음을 볼 수 있다. 짠!


파이썬 예제 코드 (전체)

import os

path = "./files"
file_list = os.listdir(path)
print(file_list)


from openpyxl import load_workbook

results = []

for file_name_raw in file_list:

    file_name = "./files/" + file_name_raw
    wb = load_workbook(filename=file_name, data_only=True)
    ws = wb.active

    result = []
    result.append(file_name_raw)
    result.append(ws['C2'].value)
    result.append(ws['C3'].value)
    result.append(ws['E2'].value)
    result.append(ws['E3'].value)

    results.append(result)

print(results)


from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for i in results:
    ws.append(i)

wb.save("results.xlsx")

아무튼 파이썬을 잘 쓰면
일이 편해진다.

추천 글


“파이썬으로 여러개의 엑셀 파일에서 필요한 값들만 한 번에 가져오기 (엑셀 크롤링)”의 22개의 댓글

  1. 정말로 딱 필요했던 아이디어네요! 공유해주신 좋은 코드 열심히 공부해서 더 스마트하게 일하겟습니다! 감사합니다*^^*

  2. 안녕하세요! 파이썬으로 엑셀을 다루고 싶은 직장인입니다!
    공유해주신 코딩이 제가 업무에서 사용하고자하는 내용과 비슷한데요!

    혹시 시트에서 ‘나이가 23인 사람의 이름만 모아서 보기’ 처럼
    원하는 조건을 가진 셀을 검색해서 일치하는 셀의 행이나열을 가져오는 코딩이 파이썬으로 가능할까요?

    1. 안녕하세요 🙂

      제가 질문을 정확히 이해한 것인지 모르겠는데,
      만약 동일한 위치에 나이 값이 입력되어 있는 엑셀파일들이 있다면
      반복문 내에 조건문을 걸어볼 수 있을 것 같습니다.

      제가 올린 예제에는 E3에 나이 값이 들어 있으니
      17번째 줄에서 이런 식으로 써줘도 될 것 같고요.
      if ws['E3'].value != 23:
          continue

      아니면 모든 파일에서 값을 긁어와서
      일단 results라는 리스트에 저장한 후에
      28번째 줄 정도에서 이런 식으로 조건을 걸어
      리스트를 추리는 방법도 있을 것 같습니다.
      results_filtered = [x for x in results if x[4]==23]

      이 정도 도움이 될런지 모르겠네요.
      작성하신 코드나 엑셀파일이 함께 있으면
      더 구체적으로 알려드릴 수 있을 것 같습니다.

  3. 핑백: 파이썬 이메일 자동화 (여러명에게 이메일 뿌리기) - 아무튼 워라밸

    1. 이 부분을 참고하시면 좋을 것 같습니다.
      https://openpyxl.readthedocs.io/en/stable/tutorial.html#accessing-many-cells

      예를 들어 B2부터 E4까지 셀에 입력된 값을 가져오고 싶다면
      아래와 같이 코드를 써볼 수 있겠네요.

      빈 리스트를 만들어놓고,
      값을 가져오고 싶은 범위를 잡아 놓은 다음,
      한 행씩 돌면서 값을 가져오는 방식입니다.

      from openpyxl import load_workbook

      wb = load_workbook(filename='파일이름', data_only=True)
      ws = wb['시트이름']

      data = []

      cell_range = ws['B2:E4']

      for row in cell_range:
        for cell in row:
          data.append(cell.value)

  4. 안녕하세요~ 좋은 자료 감사합니다.
    각 시트별로 값 말고 열을 추출할라고하면 어떻게 해야하나요?
    만약 파일 앞부분까지 이름이 같고 뒷자리만 년도로 파일이 분리되어있고
    a2011, a2012, a2013 …… a2019,a2020.xlsx
    각시트별 항목이름이 A1 =입사년월일, C1=이름.E1=부서, F1=직급 이며 값들이 각각 A2,C2,E2,F2부터 시작할때
    입사년도가 다른 각 엑셀파일에서 입사월이 3월인 사람의 이름 부서 직급 이런식으로 추출이 가능할까요?

    1. 파일 이름이 규칙적이네요.
      쉽게 접근하면 for 반복문만으로도
      파일을 열어볼 수 있을 것 같습니다.

      for n in range(2011,2021):
        filename = “a” + str(n) + “.xlsx”

      그런데 적어주신 내용을 보면
      엑셀 파일이 애초에 구조가 잘 짜여있는 데이터프레임처럼 보이네요.

      openpyxl은 엑셀 개별 셀에 직접 접근하기 편리한 라이브러리라
      차라리 pandas 같은 라이브러리의 read_excel을 사용해서
      엑셀 파일을 데이터프레임으로 불러오고
      그걸 병합하는 방법이 더 간단할 것 같습니다.

  5. 예시를 따라하고 싶은데 path에서 막혔습니다.
    어떻게 설정해야 하는건가요?? 적어주신 데로 “./files” 이렇게도 써보고
    “C:\Users\user\Documents\03.Python\excel_crawling\files”이렇게도 해보았는데 path에서 에러가 계속 나오고 있습니다.. ㅠ_ㅠ

    1. 오류코드는 다음과 같이 나오고 있습니다.

      C:\Users\user\Documents\03.Python\venv\Scripts\python.exe C:/Users/user/Documents/03.Python/excel_crawling.py
      Traceback (most recent call last):
      File “C:/Users/user/Documents/03.Python/excel_crawling.py”, line 4, in
      file_list = os.listdir(path)
      FileNotFoundError: [WinError 3] 지정된 경로를 찾을 수 없습니다: ‘./files’

    2. 엑셀 파일을 다루기 이전에
      파일명 긁어오는 데에서부터 문제가 발생하네요ㅜㅜ

      윈도우 사용하시는 거라면
      “./files”와 같은 상대경로가 아니라
      절대 경로를 적으실 때 아래와 같이 역슬래시(\) 두 개를 적어서 작성해보세요.
      “C:\\Users\\user\\Documents\\03.Python\\excel_crawling\\files”

      역슬래시는 이스케이프 기능을 하기 때문에
      윈도우에서 절대 경로를 표현할 때 보통 이런 방식으로 작성하곤 합니다.

      저도 초보고 프로그래밍 전문가가 아니라
      이 방법으로 해결이 될지 잘 모르겠지만
      어쨌든 이런 오류들 잡아서 해결해가는 과정에서
      빡침과 재미를 동시에 느끼시길 바라겠습니다. 🙂

  6. 일보형식의 파일을 받아 분류별로 정리를 해야 합니다. 혹시 엑셀 파일에 여러개의 시트가 있는데 그시트별로또 자료를 정리해야 하는데 “1번탱크적산”,”2번탱크적산”,”3번탱크적산” 등으로 시트가 분류되어 있다면 시트이름을 어떤식으로 써주어야 하는지 궁금합니다. 아직 파이썬 설치만하고 openpyxl 설치도 못했네요 3년치 365*3 개의 데이터를 정리해야하는데 시트도 6개나 있습니다. 정리는 1번탱크 <1년치 수치를 정리하려합니다.나머지 시트도 같이 정리하려 합니다. 도와주세요 ~~~^^ 안되면 몇일이되더라도 노가다 해야 합니다 ㅠㅠ

    1. 충분히 가능합니다…!
      아래와 같이 wb.sheetnames라고 쓰시면
      해당 파일에 들어 있는 모든 시트의 이름을 리스트로 불러올 수 있습니다.
      from openpyxl import load_workbook
      wb = load_workbook(‘test.xlsx’)
      ws_names = wb.sheetnames
      print(ws_names)

      그리고 각 시트에 반복문을 돌며 접근하면 되겠네요.
      for ws_name in ws_names:
        ws = wb[ws_name]

      아니면 모든 파일에서 시트 이름이 동일하다면 애초에
      ws1 = wb[“1번탱크적산”]
      ws2 = wb[“2번탱크적산”]
      ws3 = wb[“3번탱크적산”]
      이런 식으로 써서 직접 접근할 수도 있겠습니다.
      이렇게 접근하시려면 시트명에 오타가 없길 기도해야겠죠.

  7. 안녕하세요 엑셀 파일 하나에 a라인에 있는 유저이름들만 뽑아서 정해진 디렉토리에 유저이름 폴더를 생성하려고 합니다
    어떻게 코드를 짜야 할까요?
    고민하고 있는데 잘 안되서 질문해요~~

    1. 엑셀 파일에 있는 값들을 리스트 같은 자료형으로 담아 놓으시고, 폴더 만드는 코드를 반복문을 활용해서 돌리시면 되겠네요. 조금만 검색해보시면 금방 할 수 있는 내용일 거예요~

  8. 안녕하세요. 우선 위 내용 딱 필요했었는데, 감사합니다.
    위와 관련해서 엑셀 파일 중에 데이터 추출해서 가져와 다시 저장하는 과정중에

    1. O, X 로 되어있는 것 중에 O 만 뽑아오는 방법
    2. 행으로 나열해 저장하는 것이 아니라, 열로 저장하는 방법

    두가지 궁금합니다~

댓글 남기기