CSV를 열었더니 모든 값이 A열 한 칸에 몰려 있을 때

거래처에서 받은 CSV를 더블클릭했더니 한 줄이 통째로 첫 칸에 들어가 있습니다. 주문번호부터 배송지까지가 세미콜론으로 이어진 채 A열 하나를 차지하고, B열부터는 텅 비어 있습니다.

칸이 안 나뉘는 것은 파일 상태와 무관합니다. 칸을 나누는 글자가 무엇인지를 두고 파일과 엑셀의 생각이 다를 뿐입니다. 같은 표를 구분자만 바꿔 저장하고 실제로 엑셀로 열어 확인했습니다.

CSV 구분자 실측 표. 5행 5열 주문 내역을 쉼표, 세미콜론, 탭으로 각각 저장한 결과 세 파일 모두 316바이트로 같고, 파이썬 csv.Sniffer는 세 구분자를 모두 정확히 판별했다. 그러나 엑셀로 열면 쉼표 파일만 5열로 나뉘고 세미콜론과 탭 파일은 1열로 몰린다. 측정 환경은 윈도우 11, 파이썬 3.14.6, 목록 구분 기호는 쉼표다

세 파일의 크기가 똑같았습니다

주문 내역 5행 5열을 쉼표, 세미콜론, 탭으로 각각 저장했습니다. 인코딩은 utf-8-sig 하나로 통일해서 달라지는 조건을 구분자 하나로 묶었습니다.

구분자	칸 사이 바이트	파일 크기	엑셀이 나눈 열
쉼표	2C	316 B	5열
세미콜론	3B	316 B	1열
탭	09	316 B	1열

글자 수가 같으니 크기도 316바이트로 똑같습니다. 자리마다 들어간 바이트 값 하나만 다릅니다. 그런데 엑셀에서는 5열과 1열로 갈렸습니다.

세미콜론 파일을 엑셀로 열었을 때 A2 칸에 실제로 들어 있던 값입니다.

A-1001;무선 마우스;2;25900;서울시 마포구

다섯 칸에 나뉘어야 할 내용이 한 칸 안에 문자열로 붙어 있습니다. 값이 사라지거나 뒤섞인 것은 아니라서 되돌릴 수 있습니다.

---

기준은 파일이 아니라 윈도우 설정에 있습니다

엑셀이 CSV를 더블클릭으로 열 때는 파일 안을 살펴보고 구분자를 고르지 않습니다. 윈도우 지역 설정의 목록 구분 기호를 그대로 씁니다. 측정한 PC의 값은 쉼표였고, 그래서 쉼표 파일만 제대로 나뉘었습니다.

이 값은 나라마다 다릅니다. 소수점에 쉼표를 쓰는 유럽권에서는 목록 구분 기호가 세미콜론인 경우가 많습니다. 그쪽 프로그램이 내보낸 CSV가 세미콜론으로 오는 이유이고, 국내 PC에서 그대로 열면 한 칸에 몰리는 이유이기도 합니다.

파일 자체를 못 알아보는 것은 아닙니다. 파이썬의 구분자 추정 기능으로 같은 세 파일을 검사했더니 쉼표, 세미콜론, 탭 세 가지를 모두 맞혔습니다. 판별할 단서는 파일 안에 충분히 들어 있고, 더블클릭으로 여는 경로가 그 단서를 보지 않을 뿐입니다.

이미 열어 버렸다면

엑셀 안에서 바로 되돌릴 수 있습니다.

1. 값이 몰려 있는 A열 전체를 선택합니다.
2. 데이터 탭에서 텍스트 나누기를 누릅니다.
3. 구분 기호로 분리를 고르고, 파일에 실제로 들어 있는 기호를 지정합니다. 세미콜론은 목록에 있고, 탭도 있습니다.
4. 마침을 누르면 칸이 나뉩니다.

열기 전이라면 데이터 탭의 텍스트/CSV 가져오기로 여는 편이 낫습니다. 미리 보기 화면에서 구분 기호를 직접 고를 수 있어서 지역 설정과 무관하게 원하는 대로 나눌 수 있습니다.

여러 개를 반복해서 받는다면

매달 같은 형식으로 받는 파일이라면 열기 전에 쉼표로 바꿔 두는 쪽이 편합니다. 아래는 구분자를 추정해서 읽고 쉼표로 다시 저장하는 코드입니다. 별도 설치 없이 동작합니다.

import csv

with open("received.csv", encoding="utf-8-sig", newline="") as f:
    sample = f.read(2048)
    f.seek(0)
    dialect = csv.Sniffer().sniff(sample)   # 구분자를 파일에서 추정
    rows = list(csv.reader(f, dialect))

with open("fixed.csv", "w", encoding="utf-8-sig", newline="") as f:
    csv.writer(f).writerows(rows)           # 쉼표로 다시 저장

추정이 빗나갈 때는 sniff(sample, delimiters=";,\t")처럼 후보를 좁혀 주면 됩니다. 저장할 때 utf-8-sig를 쓰는 것은 엑셀이 한글을 바로 읽게 하려는 것입니다.

---

비슷해 보이는 다른 증상과 구분하기

CSV가 이상하게 열리는 상황은 원인이 세 갈래로 갈립니다. 증상을 보면 어느 쪽인지 알 수 있습니다.

모든 내용이 한 칸에 몰려 있다면 지금 이 글의 경우입니다. 칸은 제대로 나뉘었는데 주소 뒷부분만 옆 칸으로 밀려 있다면 값 안에 들어 있던 쉼표가 원인이고, 이쪽은 값 속 쉼표로 열이 밀리는 문제에서 따로 다뤘습니다. 칸은 멀쩡한데 한글이 물음표나 깨진 글자로 보인다면 구분자가 아니라 인코딩 문제이고, CSV를 UTF-8로 저장하는 방법 쪽을 보시면 됩니다.

 

받은 CSV가 한 칸에 몰려 열렸다면, 파일을 다시 받기 전에 메모장으로 열어 첫 줄만 눈으로 확인해 보세요. 값 사이에 무엇이 들어 있는지 5초면 보이고, 그 기호를 텍스트 나누기에 그대로 넣으면 끝납니다.

 

위 크기와 열 개수는 윈도우 11에서 파이썬 3.14.6으로 같은 표를 세 가지 구분자로 저장한 뒤, 엑셀로 실제로 열어 읽은 값입니다. 목록 구분 기호가 다른 PC에서는 나뉘는 결과가 달라질 수 있습니다.