이 글의 목적은 엑셀에서 형식에 맞지 않는 CSV 파일을 불러올 때 발생하는 열 틀어짐, 한글 깨짐, 줄바꿈 오류, 따옴표 처리 실패 등 다양한 문제를 진단하고 재현 가능한 해결 절차를 제공하는 것이다.
1. CSV 파일 구조 이해와 오류 진단 기본
CSV는 쉼표로 필드를 구분하고 줄바꿈으로 레코드를 구분하는 단순한 텍스트 포맷이다. 그러나 실제 현장에서는 구분자 혼용, 텍스트 한정자 누락, 인코딩 불일치, 레코드 길이 불일치 등으로 엑셀에서 올바르게 열 분할이 되지 않는 경우가 많다. 문제 해결의 첫 단계는 파일의 원시 텍스트를 확인하는 것이다.
# 점검 체크리스트 1) 구분자: , ; | \t 중 실제 사용 확인 2) 텍스트 한정자: " 또는 ' 사용 여부, 이스케이프 규칙("") 3) 줄바꿈: CRLF(\r\n) vs LF(\n) vs CR(\r) 4) 인코딩: UTF-8(BOM/무BOM), EUC-KR(CP949), UTF-16LE/BE 5) 필드 수: 헤더와 데이터 행의 열 개수 일치 여부 6) 특수문자: 필드 내부의 , ; " \n 존재 여부 7) 널 값 표기: 빈 문자열, NULL, NA 등 혼용 여부 8) 선행 0, 큰 수, 날짜 자동형식 변환 위험 여부주의 : 엑셀에서 CSV를 더블클릭으로 바로 열면 로캘·시스템 설정에 따라 구분자와 인코딩 추정이 자동 결정되어 오판단이 발생한다. 반드시 ‘데이터 → 텍스트/CSV에서’ 또는 ‘데이터 → 가져오기’ 절차로 수동 제어를 권장한다.
2. 증상별 원인-해결 요약 표
| 증상 | 주요 원인 | 해결 요약 |
|---|---|---|
| 열이 한 칸씩 밀림 | 필드 내부 쉼표 존재, 텍스트 한정자 미적용 | 텍스트 한정자 지정 또는 Power Query에서 인용부호 설정 |
| 한글 깨짐(� 표시) | 인코딩 오탐지(UTF-8 무BOM, CP949) | 가져오기 마법사에서 인코딩 수동 선택 |
| 여러 행이 한 셀로 합쳐짐 | 줄바꿈 문자 혼합, CR만 존재 | 줄바꿈 통일 후 재가져오기 |
| 따옴표가 셀에 그대로 남음 | 텍스트 한정자 처리 비활성 | 한정자 지정 또는 이스케이프 규칙 설정 |
| 숫자가 1.23E+11 형태 | 자동 지수 표기 | 데이터형을 텍스트 또는 0 반복 사용자 지정으로 고정 |
| 전화번호 선행 0 소실 | 자동 숫자 변환 | 가져오기 단계에서 열 데이터형을 텍스트로 지정 |
| 필드 분리가 세미콜론 기준 | 로캘 구분자 세미콜론 | 구분자를 세미콜론으로 수동 지정 |
| 행 수가 다르게 인식 | 따옴표 미닫힘으로 레코드 깨짐 | 잘못된 인용부호 교정 후 재불러오기 |
3. 엑셀 버전별 안정적 불러오기 절차
3.1 Microsoft 365/엑셀 2019 이상
- 메뉴에서 데이터 → 텍스트/CSV에서를 클릭한다.
- 파일 선택 후 미리보기 창에서 원본 파일을 UTF-8, 65001, 또는 949 등으로 바꿔 한글 표시를 확인한다.
- 구분 기호를 자동 → 쉼표 또는 세미콜론 등으로 수동 지정한다.
- 데이터 변환을 눌러 Power Query 편집기로 들어간다.
- 열 머리글에서 각 열의 데이터 형식을 텍스트/숫자/날짜로 지정한 뒤 닫기 & 로드를 실행한다.
주의 : 자동 감지는 재현성이 낮다. 동일한 파일이라도 PC 지역 설정과 업데이트 상태에 따라 결과가 달라질 수 있다. 미리보기의 ‘구분 기호’·‘원본 파일’·‘데이터 형식’ 세 항목을 항상 수동으로 고정한다.
3.2 엑셀 2016 이하(텍스트 가져오기 마법사)
- 데이터 → 텍스트 파일을 선택한다.
- 1단계에서 구분 기호로 분리됨을 선택한다.
- 2단계에서 쉼표·세미콜론·탭 등 실제 구분자를 체크한다. 텍스트 한정자는 기본값 " 로 유지한다.
- 3단계에서 열별 데이터 서식을 텍스트로 지정하여 날짜 오인식과 선행 0 소실을 방지한다.
3.3 Mac용 엑셀
Mac에서는 시스템 로캘에 따라 기본 구분자 추정이 다르게 작동한다. Power Query 경로는 유사하나 미리보기에서 인코딩 선택 메뉴가 간헐적으로 다르게 보일 수 있다. 동일하게 수동 지정 원칙을 적용한다.
4. 인코딩 문제 정복: UTF-8, CP949, BOM
한글 깨짐의 대부분은 UTF-8 무BOM 파일을 엑셀이 ANSI로 오판단하면서 발생한다. 해결은 간단하다. 가져오기 단계에서 UTF-8로 명시 지정하거나, 파일 자체를 UTF-8 with BOM 또는 CP949로 변환한다. 변환은 텍스트 편집기나 스크립트를 활용한다.
# PowerShell 예: UTF-8 무BOM → BOM 변환 Get-Content .\source.csv | Set-Content -Encoding utf8 .\utf8_bom.csv
Python 예: CP949를 UTF-8로 변환
import codecs
with codecs.open('source_cp949.csv','r','cp949') as f,
codecs.open('target_utf8.csv','w','utf-8') as g:
for line in f:
g.write(line)주의 : 원본 시스템이 CP949로 생성한 CSV를 UTF-8로 변환하면 특수문자가 예상과 다르게 치환될 수 있다. 항상 샘플 3~5행을 비교해 변환 품질을 검증한다.
5. 구분자·텍스트 한정자 오류 처리
필드 내부에 쉼표가 포함되어 있다면 해당 필드는 반드시 따옴표로 둘러싸여야 한다. 또한 따옴표 자체가 데이터에 포함된 경우에는 두 번 반복하여 이스케이프해야 한다.
# 올바른 예 "제품명","설명","가격" "ACME, Inc.","He said ""Hello"" yesterday",1200
잘못된 예
ACME, Inc.,He said "Hello" yesterday,1200 # 인용부호 불균형
엑셀에서 불러올 때는 Power Query의 소스 설정 편집에서 인용 스타일을 CSV 표준으로 강제한다. 텍스트 가져오기 마법사에서는 텍스트 한정자를 “ 로 지정한다.
6. 줄바꿈·개행문자 혼재 해결
CRLF와 LF가 혼재하면 일부 레코드가 한 셀에 합쳐지거나 불필요한 빈 행이 생긴다. Power Query의 고급 편집기에서 줄바꿈을 정규화하거나, 사전 전처리로 통일한다.
# Power Query M 코드: LF를 CRLF로 정규화 후 분할 let Source = Text.FromBinary(File.Contents("C:\data\raw.csv")), Normalize = Text.Replace(Source, "#(lf)", "#(cr)#(lf)"), ToTable = Csv.Document(Text.ToBinary(Normalize), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), Promote = Table.PromoteHeaders(ToTable, [PromoteAllScalars=true]) in Promote주의 : 텍스트 필드 내부의 실제 줄바꿈은 합법이다. 인용부호 안의 줄바꿈만 허용되어야 하므로, 선행 작업에서 인용부호 균형을 먼저 교정한 뒤 개행 정규화를 수행한다.
7. 날짜·숫자 자동 변환 차단
주문번호 1-2가 날짜 1-2월로, 유전자 ID가 과학 표기로 바뀌는 문제는 데이터형 자동 감지 때문이다. 해결은 가져오기 단계에서 열 서식을 텍스트로 지정하는 것이다. 대량 데이터는 Power Query에서 전체 열에 데이터형 텍스트를 먼저 적용하고, 필요한 열만 숫자·날짜로 단계적으로 변환한다.
# Power Query에서 열 데이터형 강제 = Table.TransformColumnTypes(PreviousStep, {{"주문번호", type text}, {"전화번호", type text}})8. 필드 수 불일치와 깨진 레코드 복구
일부 행의 따옴표 누락이나 구분자 추가로 열 개수가 달라지는 경우가 있다. 이때는 CSV를 표준화하는 정규식 또는 Power Query 분기 처리가 효과적이다.
# Power Query: 열 수가 기대와 다르면 원본 텍스트로 보내 검토 테이블 생성 let Raw = Csv.Document(File.Contents("C:\data\issue.csv"), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]), WithCount = Table.AddColumn(Raw, "열수", each Table.ColumnCount(Table.FromRows({[Column1..]}))) in WithCount보다 직접적인 방식으로는 사전 전처리 스크립트를 사용해 잘못된 인용부호를 교정한다.
# Python: 따옴표 균형 맞추기 개념 예시(프로덕션에서는 csv 모듈 권장) import csv fixed = [] with open('bad.csv','r',encoding='utf-8',newline='') as f: reader = csv.reader(f, delimiter=',', quotechar='"', doublequote=True) for row in reader: fixed.append(row) with open('fixed.csv','w',encoding='utf-8',newline='') as g: writer = csv.writer(g, delimiter=',', quotechar='"', lineterminator='\r\n') writer.writerows(fixed)9. QueryTables와 VBA로 완전 수동 제어
레거시 환경이나 반복 업무에서는 VBA로 구분자, 인코딩, 데이터형을 고정하면 안정적이다.
' VBA: QueryTables로 CSV 가져오기(UTF-8, 쉼표, 인용부호 처리) Sub ImportCsvStrict() Dim qt As QueryTable Dim ws As Worksheet Set ws = ActiveSheet Set qt = ws.QueryTables.Add( _ Connection:="TEXT;C:\data\source.csv", _ Destination:=ws.Range("A1")) With qt .TextFilePlatform = 65001 ' UTF-8 .TextFileCommaDelimiter = True ' 쉼표 .TextFileSemicolonDelimiter = False .TextFileOtherDelimiter = False .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat) ' 열 형식 고정 .AdjustColumnWidth = False .Refresh BackgroundQuery:=False End With End Sub주의 : .TextFilePlatform은 인코딩 코드페이지를 사용한다. 65001은 UTF-8, 949는 CP949이다. 파일 인코딩과 불일치하면 깨짐이 발생한다.
10. ADODB.Stream으로 인코딩을 강제 로드
QueryTables가 특정 인코딩을 정확히 해석하지 못하는 상황에서는 ADODB.Stream으로 텍스트를 읽어 셀에 직접 파싱한다.
' VBA: ADODB.Stream + Text Parsing(간단 파서) Sub LoadCsvByStream() Dim stm As Object, txt As String, lines, i As Long Set stm = CreateObject("ADODB.Stream") With stm .Charset = "utf-8" .Open .LoadFromFile "C:\data\source.csv" txt = .ReadText .Close End With lines = Split(txt, vbCrLf) For i = 0 To UBound(lines) Dim cellsArr: cellsArr = SplitCsvLine(lines(i)) If Not IsEmpty(cellsArr) Then Range("A1").Offset(i, 0).Resize(1, UBound(cellsArr)+1).Value = cellsArr End If Next i End Sub
Private Function SplitCsvLine(ByVal s As String)
' CSV 표준에 맞는 간단 파서(따옴표, 쉼표 대응)
Dim res() As String, cur As String, i As Long, q As Boolean, ch As String
ReDim res(0)
For i = 1 To Len(s)
ch = Mid$(s, i, 1)
If ch = """" Then
If q And i < Len(s) And Mid$(s, i+1, 1) = """" Then
cur = cur & """" : i = i + 1
Else
q = Not q
End If
ElseIf ch = "," And Not q Then
res(UBound(res)) = cur
cur = ""
ReDim Preserve res(UBound(res)+1)
Else
cur = cur & ch
End If
Next i
res(UBound(res)) = cur
SplitCsvLine = res
End Function11. Power Query 고급 전략: 구분자 자동 감지 억제와 스키마 고정
대규모 파이프라인에서는 소스 단계에서 구분자·인코딩·인용 스타일을 코드로 고정해 재현성을 확보한다.
let Source = Csv.Document( File.Contents("C:\data\stable.csv"), [Delimiter=",", Columns=8, Encoding=65001, QuoteStyle=QuoteStyle.Csv]), Promote = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), Types = Table.TransformColumnTypes(Promote, { {"주문일자", type text}, {"주문번호", type text}, {"고객ID", type text}, {"수량", Int64.Type}, {"단가", type number}, {"금액", type number}, {"전화번호", type text}, {"비고", type text}}) in Types주의 : Columns 인수를 명시하면 열 수 불일치 시 오류가 발생하여 문제 행을 즉시 탐지할 수 있다. 파이프라인 초기에 실패하도록 설계하는 것이 사후 교정보다 안전하다.
12. 특수 사례 처리
12.1 세미콜론 CSV
유럽권 또는 로캘 설정에 따라 CSV가 세미콜론으로 만들어지는 경우가 있다. Power Query에서 Delimiter를 “;”로 지정하거나, 텍스트 마법사에서 세미콜론 체크 후 가져온다.
12.2 탭 구분 TSV
실제 파일이 TSV인데 확장자만 CSV인 경우가 있다. 이때는 구분자를 탭으로 지정한다.
12.3 따옴표 없는 CSV
필드 내부에 쉼표가 전혀 없는 데이터라면 텍스트 한정자를 사용하지 않은 CSV도 가능하다. 그러나 안전하지 않다. 가능하면 사전 전처리로 필드 전체에 인용부호를 추가한다.
# 안전한 변환 예시(Python) import csv with open('raw.csv','r',encoding='utf-8',newline='') as f, \ open('quoted.csv','w',encoding='utf-8',newline='') as g: w = csv.writer(g, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL) for row in csv.reader(f): w.writerow(row)12.4 빈 값과 NULL 혼재
빈 문자열과 문자열 "NULL"을 동일 취급하면 계산 오류가 발생한다. Power Query에서 값 바꾸기 단계로 "NULL"을 실제 null로 치환한 뒤 데이터형 변환을 수행한다.
13. 대용량 CSV 불러오기 성능 팁
- Power Query로 로드 후 필요한 열만 선택 로드한다.
- 텍스트 열은 초기에 모두 텍스트로 고정하고, 계산에 필요한 열만 숫자로 변환한다.
- 필요 시 CSV를 여러 파일로 쪼개고 폴더 연결 기능으로 결합한다.
14. 자동화와 재현성 확보 체크리스트
| 항목 | 권장 설정 | 검증 방법 |
|---|---|---|
| 인코딩 | UTF-8(BOM) 또는 원본 코드페이지 명시 | 미리보기에서 한글·특수문자 점검 |
| 구분자 | 쉼표/세미콜론/탭 고정 | 첫 3행 열 개수 일치 확인 |
| 텍스트 한정자 | 더블쿼트, 이스케이프 "" | 내부 쉼표·줄바꿈 포함 필드 테스트 |
| 데이터형 | 초기 텍스트, 필요 열만 변환 | 전화번호·ID 선행 0 유지 확인 |
| 줄바꿈 | CRLF 통일 | 빈 행·합쳐진 셀 발생 여부 점검 |
15. 문제 재현 및 회귀 방지 템플릿
아래 샘플 CSV를 사용해 파이프라인이 문제 없이 처리되는지 회귀 테스트를 수행한다.
"주문일자","주문번호","고객ID","수량","단가","금액","전화번호","비고" "2025-10-01","001-01","U001",10,1200,12000,"010-0123-4567","정상" "2025-10-02","001-02","U002",5,1000,5000,"010-0000-0000","ACME, Inc. 내부 회의" "2025-10-03","001-03","U003",3,999999999999,2999999999997,"012-3456-7890","He said ""Hello""" "2025-10-04","001-04","U004",2,800,1600,"010-1111-2222","다행히 줄바꿈 예시↓ 여기부터는 같은 셀 내부 줄바꿈"이 샘플은 쉼표 포함 텍스트, 큰 숫자, 인용부호 이스케이프, 셀 내부 줄바꿈을 모두 포함한다. Power Query에서 CSV 표준 인용 스타일을 적용하고, 전화번호와 주문번호는 텍스트로 고정하여 회귀를 확인한다.
16. 현장 적용 흐름도
- 원시 파일 검사: 인코딩·구분자·인용부호·개행 확인
- 사전 정규화: 개행 통일, 인용부호 교정, 필요 시 인코딩 변환
- 엑셀 가져오기: Power Query로 인코딩·구분자·열 수·인용 스타일 고정
- 데이터형 지정: 텍스트 우선, 단계적 변환
- 검증: 표준 샘플로 회귀 테스트, 오류 행 캡처
FAQ
CSV가 너무 커서 엑셀이 멈춘다. 어떻게 하나?
Power Query로 불러와 필요한 열만 선택하고, 데이터 미리보기를 제한한다. 폴더 연결 기능으로 여러 파일을 분할 결합하되 열 스키마를 고정한다.
한글이 깨지는데 UTF-8로도 해결이 안 된다.
원본이 CP949로 생성되었을 가능성이 높다. 가져오기 단계에서 코드페이지 949를 선택하여 미리보기로 확인한다.
날짜가 자동으로 바뀐다.
가져오기 시 해당 열을 텍스트로 지정한다. 변환이 필요하면 Power Query에서 명시적으로 날짜형으로 바꾼다.
필드 내부 줄바꿈 때문에 행이 깨진다.
CSV 표준 인용을 적용하면 해결된다. 인용부호가 균형을 이루는지 먼저 검증한다.
세미콜론 CSV를 쉼표로 바꾸고 싶다.
Power Query에서 구분자를 세미콜론으로 지정하거나, 전처리 스크립트로 세미콜론을 쉼표로 치환하되 인용부호 내부 문자는 보존한다.
전화번호 선행 0이 사라진다.
열 데이터형을 텍스트로 가져오고, 표시 형식으로 제어한다.