CSV 형식은 일반적으로 다양한 스프레드시트 프로그램 간에 데이터를 가져오거나 내보내는 데 사용됩니다. CSV(쉼표로 구분된 값)라는 이름은 쉼표를 사용하여 데이터 필드를 구분하는 것을 의미합니다.
실제로 많은 소위 CSV 파일은 세미콜론이나 탭과 같은 다른 문자를 사용하여 데이터를 분리합니다. 일부 구현에서는 데이터 필드를 작은따옴표 또는 큰따옴표로 묶는 반면, 다른 구현에서는 올바른 유니코드 해석을 위해 유니코드 BOM(바이트 순서 표시)(예: UTF-8)이 필요합니다. 표준이 없으면 CSV에서 Excel로의 변환에 다양한 문제가 발생합니다.
CSV 파일이 Excel의 한 열에서 열릴 경우
증상. Excel에서 csv 파일을 열면 모든 데이터가 단일 열에 나타납니다.
원인. 데이터를 열로 분할하기 위해 Excel에서는 Windows 국가별 설정에 설정된 목록 구분 기호를 사용합니다. 쉼표(북미 및 일부 다른 국가) 또는 세미콜론(유럽 국가)일 수 있습니다. 특정 .csv 파일에 사용된 구분 기호가 기본 구분 기호와 다르면 해당 파일이 한 열에서 열립니다.
솔루션. VBA 매크로 또는 Windows 설정의 전역 변경을 포함하여 이 경우에 대한 몇 가지 가능한 솔루션이 있습니다. 컴퓨터의 기본 목록 구분 기호를 변경하지 않고 문제를 신속하게 해결하여 응용 프로그램이 영향을 받지 않도록 하는 방법을 보여줍니다.
CSV 파일의 구분 기호 변경
Excel에서 다른 구분 기호를 사용하여 CSV를 읽을 수 있도록 하려면 해당 파일에서 직접 구분 기호를 정의할 수 있습니다. 이 작업을 수행하려면 텍스트 편집기로 파일을 열고(메모장에서도 가능함) 첫 번째 줄에 아래 텍스트를 추가합니다.
다른 데이터 앞에 별도의 줄이 있어야 합니다.
- 쉼표로 구분하려면: sep=,
- 세미콜론으로 구분하려면: sep=;
같은 방식으로, 다른 사용자 정의 구분 기호를 설정할 수 있습니다 - 단순히 같음 기호 뒤에 입력하십시오.
적절한 구분 기호를 정의하면 이제 Excel 자체 또는 Windows 탐색기에서 일반적인 방법으로 파일을 열 수 있습니다.
CSV 파일을 Excel로 가져올 때 구분 기호 지정
Excel에서 csv 파일을 여는 대신 텍스트 가져오기 마법사(모든 버전) 또는 파워 쿼리(Excel 365 - 2016)를 사용하여 가져옵니다.
텍스트 가져오기 마법사(데이터 탭 > 텍스트에서)는 2단계의 구분 기호에 대한 몇 가지 선택 사항을 제공합니다.
일반적으로 다음을 선택합니다.
- 쉼표로 구분된 값 파일의 쉼표
- 텍스트 파일용 Tab
- 세미콜론으로 구분된 값 파일의 경우 세미콜론
데이터에 어떤 구분 기호가 포함되어 있는지 확실하지 않은 경우 다른 구분 기호를 시도하고 데이터 미리 보기에서 어떤 구분 기호가 제대로 작동하는지 확인하십시오.
Power Query 연결을 만들 때 미리 보기 대화 상자 창에서 구분 기호를 선택할 수 있습니다.
Text to Columns 기능을 사용하여 셀 분할
데이터가 이미 Excel로 전송 된 경우 Text to Columns 기능을 사용하여 데이터를 다른 열로 구분할 수 있습니다. 기본적으로, 그것은 텍스트 가져 오기 마법사처럼 작동합니다.
구분 기호를 선택하면 데이터 미리보기가 변경 사항을 즉석에서 반영합니다.
Excel CSV에서 선행 0을 유지하는 방법
조짐. csv 파일의 일부 값에는 선행 0이 포함되어 있습니다. Excel에서 파일을 열면 앞의 0이 손실됩니다.
원인. 기본적으로 Microsoft Excel은 csv 파일을 선행 0을 제거하는 일반 형식으로 변환합니다.
해결책. 여는 대신 CSV를 Excel로 가져오고 문제가 있는 열의 텍스트 형식을 선택합니다.
텍스트 가져오기 마법사 사용
텍스트 가져오기 마법사를 자동으로 시작하려면 파일 확장명을 .csv에서 .txt로 변경한 다음 Excel에서 텍스트 파일을 엽니다. 또는 원본 텍스트(레거시) 기능을 활성화하고 CSV를 Excel로 가져오기를 시작합니다.
마법사의 3단계에서 선행 0이 있는 값이 들어 있는 열을 선택하고 형식을 텍스트로 변경합니다. 이렇게 하면 값을 텍스트 문자열로 가져와 모든 선행 0을 제자리에 유지합니다.
파워 쿼리 사용
csv 파일에 연결하여 Excel로 가져 오는 것을 선호하는 경우 선행 0을 유지하는 두 가지 방법이 있습니다.
방법 1: 모든 데이터를 텍스트 형식으로 가져오기
미리 보기 대화 상자의 [Data Type Detection]에서 [Do not detect data types]를 선택합니다. csv 파일의 내용이 Excel에 텍스트로 로드되고 모든 선행 0이 유지됩니다.
이 방법은 파일에 텍스트 데이터만 포함된 경우 제대로 작동합니다. 다른 종류의 값이 있는 경우 방법 2를 사용하여 각 열에 대해 개별적으로 적절한 형식을 정의합니다.
방법 2: 각 열의 형식 설정
csv 파일에 텍스트, 숫자, 통화, 날짜 및 시간과 같은 다양한 데이터 유형이 포함된 경우 각 특정 열에 사용해야 하는 형식을 명시적으로 나타낼 수 있습니다.
- 데이터 미리 보기 아래에서 데이터 변환을 클릭합니다.
- Power Query 편집기에서 앞의 0을 유지할 열을 선택하고 데이터 형식 > 텍스트를 클릭합니다.
- 필요한 경우 다른 열에 대한 데이터 형식을 정의합니다.
- 편집이 완료되면 홈(Home) 탭의 닫기(Close) 그룹에서 다음 중 하나를 클릭합니다.
- Close & Load - 현재 통합 문서의 새 시트에 결과를로드합니다.
- 닫기 & 로드 받는 사람... - 결과를 로드할 위치를 결정할 수 있습니다.
이러한 메서드는 Excel에서 자동으로 수행하려고 시도하는 데이터에 대한 다른 조작을 방지할 수도 있습니다. 예를 들어 가져온 데이터가 "="로 시작하는 경우 Excel에서 계산을 시도합니다. Text 형식을 적용하면 값이 수식이 아닌 문자열임을 나타냅니다.
Excel에서 CSV 날짜 형식 문제를 해결하는 방법
CSV를 Excel로 변환 한 후 날짜 형식이 잘못되고, 일과 월이 바뀌고, 일부 날짜가 텍스트로 변경되고, 일부 텍스트 값이 날짜로 자동 서식이 지정됩니다.
csv 파일에서 날짜는 운영 체제에 설정된 기본 날짜 형식과 다른 형식으로 작성되므로 Excel에서 날짜를 올바르게 해석하지 못합니다.
일과 월이 뒤섞여 있을 경우
Windows 국가별 설정과 csv 파일의 날짜 형식이 다를 경우 Excel에서 찾고 있는 mm/dd/yy 날짜가 해당 파일의 dd/mm/yy 형식으로 저장되어 있는지 확인할 수 없습니다. 그 결과, 일-3이 Mar-1이 되고, 일-10이 Oct-1이 되는 등 일-월 단위가 반대로 됩니다. 또한 1월 12일 이후의 날짜는 13번째, 14번째 등의 달이 없기 때문에 텍스트 문자열로 변환됩니다.
날짜를 올바르게 가져오려면 텍스트 가져오기 마법사를 실행하고 3단계에서 적절한 날짜 형식을 선택합니다.
일부 값은 날짜로 변환됩니다
Microsoft Excel은 다양한 종류의 값을 쉽게 입력할 수 있도록 설계되었습니다. 따라서 Excel이 지정된 값이 날짜를 나타낸다고 생각하는 경우 해당 값은 자동으로 날짜 형식으로 지정됩니다. 예를 들어 텍스트 문자열 apr23은 4월 23일과 매우 유사하고 11/3은 11월 3일과 유사하므로 두 값 모두 날짜로 변환됩니다.
Excel이 텍스트 값을 날짜로 변경하지 않도록 하려면 CSV를 가져와 Excel로 변환하는 이미 익숙한 방법을 사용합니다. 텍스트 가져오기 마법사의 3단계에서 문제가 있는 열을 선택하고 형식을 텍스트로 변경합니다.
날짜 형식이 잘못되었습니다.
Excel에서 csv 파일을 열면 일반적으로 날짜가 기본 형식으로 표시됩니다. 예를 들어 원본 파일에는 7-May-21 또는 05/07/21이 있을 수 있지만 Excel에서는 5/7/2021로 표시됩니다.
날짜를 원하는 형식으로 표시하려면 셀 서식 기능을 사용합니다.
- 날짜 열을 선택합니다.
- 누르다 Ctrl + 1 을 클릭하여 셀 서식 대화 상자를 엽니다.
- 번호 탭의 범주에서 날짜를 선택합니다.
- 유형에서 원하는 서식을 선택합니다.
- 확인을 클릭합니다.
미리 설정된 형식 중 어느 것도 적합하지 않은 경우 Excel에서 사용자 지정 날짜 형식을 만드는 방법에 설명 된대로 자신 만의 형식을 만들 수 있습니다.
Excel에서 숫자를 과학적 표기법으로 변환하지 못하도록 방지
CSV를 Excel로 변환한 후 긴 숫자는 과학적 표기법으로 포맷됩니다. 예를 들어 1234578900은 1.23E+09로 나타납니다.
Microsoft Excel에서는 숫자가 15자리로 제한됩니다. csv 파일의 숫자가 이 제한을 초과하면 Excel은 해당 제한을 준수하기 위한 방법으로 자동으로 과학적 표기법으로 변환합니다. 숫자에 포함된 유효 숫자가 15자리를 초과하면 마지막에 있는 모든 "추가" 숫자가 0으로 변경됩니다.
Excel에서 긴 숫자를 텍스트로 가져오거나 숫자 형식을 직접 변경합니다.
긴 숫자를 텍스트로 가져오기
CSV에서 Excel로 큰 숫자를 정확하게 전송하려면 텍스트 가져오기 마법사를 실행하고 대상 열의 형식을 텍스트로 설정합니다.
16번째 및 이후 숫자를 0으로 바꾸거나 선행 0을 제거하지 않고 데이터를 잃지 않고 숫자 문자열을 정확하게 가져올 수 있는 유일한 실제 솔루션입니다. 제품 ID, 계정 번호, 바코드 등의 항목에 적합합니다.
그러나 값이 문자열이 아닌 숫자인 경우에는 결과 텍스트 값을 계산할 수 없으므로 이 방법이 가장 좋은 방법은 아닙니다.
이 방법을 사용하면 CSV 파일을 변환할 때 원치 않는 다른 자동 데이터 형식을 방지할 수도 있습니다.
Excel에서 숫자 형식 변경
데이터가 이미 Excel에 있는 경우 아래와 같이 형식을 일반에서 텍스트 또는 숫자로 변경할 수 있습니다.
이 방법은 0으로 대체된 15번째 위치 이후에 삭제된 선행 0 또는 숫자를 복원하지 않습니다.
열을 더 넓게 만들기
가장 간단한 경우, 숫자에 15자리 미만의 숫자가 포함된 경우 숫자를 정상적으로 표시하기 위해 열을 조금 더 넓게 만드는 것으로 충분합니다.
'워드프로세서 > 엑셀' 카테고리의 다른 글
| 엑셀 조건부 서식 색상 스케일(Color Scale) (0) | 2023.06.08 |
|---|---|
| 엑셀에서 IF 문으로 VLOOKUP (0) | 2023.06.06 |
| 엑셀 AVERAGE 함수 (산술평균) (0) | 2023.06.06 |
| 엑셀 및 구글 스프레드시트 - 드롭다운 목록 생성(색상포함) (0) | 2023.06.06 |
| 엑셀 셀 값에 따라 행 색상을 변경하는 방법 (0) | 2023.06.05 |