How to Work with Large CSV Files (1GB+) Without Crashing Excel

지난 화요일, 한 주니어 분석가의 얼굴이 세 번째로 엑셀이 멈춘 그 아침에 핏기가 없어지는 모습을 보았습니다. 그녀는 두 시간 동안 2.3GB의 고객 거래 파일을 열려고 하고 있었습니다. 죽음의 회전 바퀴는 그녀의 숙적이 되어버렸습니다. 저는 데이터 운영 엔지니어로 11년을 일해왔고, 금융 팀, 마케팅 부서, 연구실에서 이와 같은 시나리오가 수백 번 펼쳐지는 것을 봐왔습니다. 불만은 현실적이고, 시간 낭비는 측정 가능하며, 무엇을 하고 있는지 아는 것만으로도 놀랍게 간단한 해결책들이 존재합니다.

대부분의 사람들은 몰랐다: 엑셀은 1GB 이상의 파일을 위해 설계되지 않았다. 마이크로소프트 자사 문서에 따르면, 엑셀 2016 이후 버전은 이론적으로 최대 1,048,576행과 16,384열을 다룰 수 있지만, 실제로는 100MB 임계값을 넘으면 성능이 급격히 저하된다. 저는 이를 광범위하게 테스트해 왔습니다. 500MB의 CSV 파일을 여는 데는 8-12분이 소요되며, 8GB RAM을 탑재한 일반 비즈니스 노트북에서 그럽니다. 1.5GB 파일이라면? 열리기나 할 것인지 20분 이상 걸릴 것이며(데이터를 가지고 무엇을 시도하기 전까지도 말이죠).

실제 비용은 단순한 대기 시간에 그치지 않습니다. 그것은 연쇄적인 생산성 손실입니다. 도구가 반복적으로 중단되면 생각의 흐름을 잃고, 마감일을 놓치며, 전체 데이터 세트가 아닌 불완전한 데이터 샘플을 기반으로 결정을 내리기 시작하게 됩니다. 저는 이전에 다섯 명의 분석가 팀이 대용량 CSV 파일과 싸우며 하루에 단 30분만 소모해도 연간 약 650시간을 잃는다는 계산을 해본 적이 있습니다. 이는 멈춰 있는 진행 막대의 공허 속으로 사라지는 생산 작업의 거의 4개월에 해당합니다.

이 가이드는 제가 엑셀을 열지 않고도 거대한 CSV 파일을 처리하는 방법을 정확하게 보여줄 것입니다. 이들은 이론적인 접근 방식이 아니라, 제가 매일 사용하는 전투 테스트된 방법입니다. 1GB에서 50GB에 이르는 파일을 처리하는 데 사용됩니다. 웹 서버 로그를 분석하든, 전자상거래 거래를 처리하든, 과학 데이터 세트를 작업하든, 이러한 기법들은 대규모 데이터를 다루는 방식을 혁신적으로 변화시킬 것입니다.

엑셀이 대용량 파일에서 실패하는 이유 이해하기

해결책에 들어가기 전에, .csv 파일에서 엑셀이 멈췄을 때 실제로 어떤 일이 일어나는지를 이해해야 합니다. 이는 엑셀이 나쁜 프로그램이라는 것이 아닙니다. 이는 일을 위한 잘못된 도구를 사용하는 것에 관한 것입니다. 엑셀은 전체 데이터 세트를 RAM에 로드합니다. 모든 단일 행, 모든 셀, 모든 수식이 컴퓨터 메모리에 저장됩니다. 2GB CSV 파일을 열 때, 엑셀은 단순히 2GB를 읽는 것이 아닙니다. 내부 데이터 구조, 포맷 오버헤드 및 계산 엔진 때문에 일반적으로 메모리에서 3-5배의 양을 소비합니다.

저는 지난 달 8.5백만 행의 판매 데이터가 포함된 1.2GB CSV 파일로 테스트를 수행했습니다. 16GB RAM을 갖춘 노트북의 엑셀 2021에서 열면 메모리 사용량이 6.8GB로 급증했습니다. 파일을 여는 데 14분이 걸렸고, 스크롤하거나 필터를 적용하는 것과 같은 간단한 작업에도 추가로 2-3분의 지연이 발생했습니다. 피벗 테이블을 생성하려고 시도할 때 애플리케이션이 완전히 중단되었습니다. 이것은 극단적인 상황이 아닙니다. 이는 엑셀의 설계 한계를 넘길 때 기대되는 행동입니다.

행 수 제한도 또 다른 중요한 제약 사항입니다. 엑셀의 최대 행 수인 1,048,576은 이벤트 로그, IoT 센서 데이터 또는 거래 기록을 다룰 때는 많은 것처럼 들리지 않습니다. 바쁜 전자상거래 사이트는 월에 200-300만 개의 거래 기록을 생성할 수 있습니다. 중간 트래픽 웹사이트의 단하루 서버 로그는 500만 개 이상의 항목을 초과할 수 있습니다. CSV 파일의 행이 엑셀이 처리할 수 있는 수를 초과하면 경고 없이 데이터를 단순히 잘라내고, 불완전한 정보에 기반하여 결정을 내리게 됩니다.

수식 재계산 문제도 있습니다. 스프레드시트에 수식이 포함되어 있으면, 엑셀은 변경할 때마다 이를 재계산합니다. 대량 데이터 세트에서는 이 재계산이 몇 분이 걸릴 수 있습니다. 단일 셀을 변경한 후 50만 행과 열이 12개인 스프레드시트가 재계산하는 데 8분이 걸리는 것도 보았습니다. 이는 반복 분석을 사실상 불가능하게 만듭니다.

파일 형식 자체가 문제에 기여합니다. CSV 파일은 일반 텍스트로, 디스크에서는 상대적으로 압축되어 있지만 엑셀의 이진 형식으로 로드하면 상당히 확장됩니다. 1GB CSV 파일은 엑셀의 포맷팅 및 메타데이터로 저장하면 3.5GB XLSX 파일이 될 수 있습니다. 이 확장은 메모리 문제를 심화시키고 파일 작업을 더 느리게 만듭니다.

방법 1: 빠른 분석을 위한 커맨드라인 도구

대용량 CSV 파일을 다루는 가장 빠른 방법은 그래픽 인터페이스에서 아예 열지 않는 것입니다. 커맨드라인 도구는 메모리에 모든 것을 로드하는 것이 아니라 데이터를 스트리밍하기 때문에 기가바이트 데이터를 몇 초 만에 처리할 수 있습니다. 저는 매일 이러한 도구를 사용하며, 이를 통해 수많은 시간을 절약했습니다. 학습 곡선은 엑셀에서 클릭하는 것보다 가파르지만, 보상은 엄청납니다.

"엑셀은 빅데이터를 위해 만들어진 것이 아니라, 스프레드시트를 위해 만들어졌습니다. 100MB를 넘으면 더 이상 올바른 도구를 사용하고 있는 것이 아닙니다. RAM의 양으로는 해결할 수 없는 아키텍처적 한계를 향해 싸우고 있습니다."

기본부터 시작합시다. 윈도우에서는 PowerShell을 사용할 수 있습니다. 맥이나 리눅스에서는 터미널을 사용할 것입니다. 가장 먼저 알아야 할 도구는 'head'입니다. 이는 파일의 첫 몇 행을 보여줍니다. 2GB 파일을 여는 데 15분을 기다릴 필요 없이, 'head -n 10 yourfile.csv'를 실행하면 첫 10행을 즉시 볼 수 있습니다. 저는 새로운 데이터 세트를 받을 때마다 이 작업을 수행합니다. 2초가 소요되며 파일이 제대로 포맷되어 있는지, 열 헤더가 무엇인지, 데이터가 합리적으로 보이는지를 즉각적으로 알려줍니다.

행을 세기 위해서는 'wc -l yourfile.csv'를 사용하세요. 이는 파일의 줄 수를 세고, 다소 기가바이트에 해당하는 파일에 대해서도 1초 이내에 결과를 반환합니다. 저는 최근 4.2GB 로그 파일에서 2800만 개의 행을 사용하였습니다. 카운트는 0.8초 만에 완료되었습니다. 같은 작업을 엑셀로 한다면 20분 이상 걸릴 것이고, 아마도 크래시가 일어났을 것입니다.

'grep' 명령은 필터링에 대해 믿을 수 없을 만큼 강력합니다. 특정 고객 ID, 제품 코드 또는 오류 메시지를 포함한 모든 행을 찾고 싶다면 grep은 몇 초 만에 기가바이트 데이터를 검색할 수 있습니다. 저는 이것을 디버깅과 빠른 분석을 위해 지속적으로 사용합니다. 예를 들어, 'grep "ERROR" server_logs.csv'를 실행하면 ERROR라는 단어가 포함된 모든 줄이 표시됩니다. 이를 'wc -l'에 파이프하여 오류 수를 셀 수 있습니다: 'grep "ERROR" server_logs.csv | wc -l'. 엑셀에서 몇 분이 걸릴 이 분석이 커맨드라인에서 즉시 발생합니다.

더 복잡한 필터링과 열 선택을 위해 'awk'가 당신의 친구입니다. 이는 텍스트 처리를 위해 특별히 설계된 프로그래밍 언어입니다. 문법이 처음에는 위협적으로 보일 수 있지만, 기본 작업은 간단합니다. CSV 파일의 첫 번째 및 세 번째 열만 인쇄하려면: 'awk -F',' '{print $1, $3}' yourfile.csv'를 사용하세요. -F','는 awk에게 쉼표가 필드 구분자임을 알려줍니다. 이 명령은 저의 노트북에서 약 5초 만에 2GB 파일을 처리합니다.

가장 많이 사용하는 커맨드라인 레시피를 텍스트 파일로 보관합니다. 제가 매주 사용하는 레시피 중 하나가 있습니다: 특정 열에서 고유 값을 얻으려면 'awk -F',' '{print $2}' yourfile.csv | sort | uniq'를 사용합니다. 이는 2열을 추출하고, 정렬하고, 중복을 제거합니다. 1500만 행의 1.5GB 파일로, 이는 약 12초에 완료됩니다. 엑셀에서 수행할 경우 전체 파일을 로드하고, 필터를 적용하고, 고유 값을 복사해야 하며, 그 전에 엑셀이 크래시하지 않는다면 말입니다.

방법 2: 강력한 분석을 위한 판다스와 파이썬 사용하기

커맨드라인 도구로는 부족하고 더 정교한 분석이 필요할 때, 판다스 라이브러리와 함께하는 파이썬이 저의 기본 솔루션입니다. 8년 동안 이 조합을 사용해왔고, 이는 데이터 분석의 업계 표준이 되었습니다. 판다스는 엑셀을 울게 만들 수 있는 파일도 처리할 수 있으며, 우아하고 읽기 쉬운 코드로 이를 수행합니다.

핵심 기술은 청크로 읽는 것입니다. 전체를 한 번에 로드하는 것이 아니라 파일을 조각으로 읽는 것이죠. 일반적으로 이렇게 시작합니다: 3GB 파일을 메모리에 로드하는 대신, 100,000행 청크로 읽습니다. 각 청크가 처리되고 결과가 집계됩니다. 이를 통해 사용 가능한 RAM보다 큰 파일을 분석할 수 있습니다. 저는 이 접근 방식을 사용하여 16GB 메모리의 노트북에서 정기적으로 20GB 파일을 처리합니다.

기본 청크 처리 예시는 이렇습니다: 파일의 청크를 반복하여 각 청크에서 계산을 수행합니다.