vba 배열 공백 제거, 메모장 utf-8 인코딩 관련해서 도와주세요~~

VBA
작성자
audtjs1366@naver.com
작성일
2020-11-03 11:16
조회
1802

엑셀로 메모장을 한줄씩 가져와서 콤마(,) 기준으로 열로 나누었는데

각 열에 있는 공백을 제거하는게 너무 어려워요ㅠㅠ

이 부분은 다양한 코드를 적용하려 해봤는데 형식오류와 개체오류가 생기네요

 

두번째는 메모장을 생성하면 utf-8 형식이라 한글이 깨져요

많은 양의 데이터를 불러와야 되서 메모장 하나하나 형식을 바꿀 수 없어서

vba로 해결해야 하는데요.

아래의 코드를 이용하면 된다고 하는데 어떻게 사용해야 에러없이 사용할 수 있을 지 모르겠어요ㅠㅠ

 

도움 부탁드려요~

 

Dim objStream, strData Set objStream = CreateObject("ADODB.Stream") objStream.CharSet = "utf-8" objStream.Open objStream.LoadFromFile("C:\Users\admin\Desktop\test.txt") strData = objStream.ReadText()

 

 

현재 코드입니다.

Option Explicit

Sub ImportTxt()
Dim c As Range '메모장 범위
Dim FilePath As String, FileName As String '메모장 위치, 파일명
Dim data As String '메모장 한줄당 임시저장
Dim i As Long '메모장 다음줄로 이동
Dim FileNum As Integer '파일 자동번호 부여
Dim arr() As String '1줄씩 가져온 내용을 콤마(,) 단위로 나눠서 저장

Set c = Sheet1.Range("a1") 'a1부터 출력
Sheet1.Cells.Clear 'sheet1 모두 지우기

'폴더 선택 창 & 폴더 경로
With Application.FileDialog(msoFileDialogFolderPicker)
.Show '선택 창 보여줌
If .SelectedItems.Count = 0 Then '선택하지 않는 경우
Exit Sub
Else
FilePath = .SelectedItems(1) & "\" '선택한 폴더 내 모두
End If
End With

'폴더 내, 텍스트 파일 존재 여부
FileName = Dir(FilePath & "*.txt")

If FileName = "" Then
MsgBox "폴더 내 텍스트 파일이 존재하지 않습니다"
Exit Sub
End If

Do While FileName <> ""

FileNum = FreeFile '파일번호 자동 부여
Open FilePath & FileName For Input As #FileNum '파일 모두 열기

Do Until EOF(FileNum) '메모장 첫줄부터 마지막줄까지 읽어서 엑셀에 나타냄
Line Input #FileNum, data '한줄씩 읽어와서 data에 저장

data = Trim(data) 'data 공백 제거

arr = Split(data, ",") ' 콤마(,) 기준으로 나눠서 arr에 저장

c.Offset(i).Resize(1, UBound(arr) + 1) = arr 'UBound : 배열의 마지막 위치값이므로 + 1로 함. 화면 출력

i = i + 1

Loop

Close #FileNum
FileName = Dir '다음 파일 가져오기
Loop

c.CurrentRegion.Columns.AutoFit '글자에 맞게 자동으로 열너비 조정

End Sub

 

 

전체 5

  • 2020-11-03 14:18

    @audtjs1366@naver.com 님 질문을 정리해 보겠습니다.
    - TXT 파일이 여러개 있다.
    . 각 컬럼은 comma 로 구분되어 있다.
    - 각 각이 파일을 엑셀로 읽어서
    . 공백을 제거하고 표시한다.

    - 처리사항
    . 텍스트 파일을 모두 읽어서, 하나의 엑셀파일에 통합하여 표시 ?
    . 파일을 읽을 때, 한글이 깨진다 (UTF-8 포맷)

    - 궁금한 사항
    . 각각의 txt 파일은 컬럼이 동일한가
    . 제시한 vba 코드는 한줄씩 처리하는 코드라, 실행속도에 문제가 있지 않은가?

    아래와 같이 한번 해 보세요.
    - 엑셀에서 매크로 기록을 누르고
    - txt 파일을 열고, 매크로 기록을 멈추고
    - 생성된 코드를 열어 보세요.
    . 아래와 같은 구조가 나올 갑니다.
    . 한글 깨짐은 첨부 이미지 참조하세요.

    Workbooks.OpenText Filename:="\\aa\aa\텍스트샘플.txt", _
    Origin:=949, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, _
    Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

    - 해당 코드를 loop 문에서 반복 실행합니다.

    20201103_141624.png


    • 2020-11-03 15:16

      @야휘 님 안녕하세요 답변 감사드립니다.

      회사 내부에서 사용할 폼을 만드는 중이라 버튼 한번만 누르면 하나의 폴더에 있는 모든 파일이 엑셀로 출력되어야 하는데 위의 방법으로 가능할까요?

      txt 파일의 컬럼은 시스템에 따른 결과값이라 차이가 조금은 있을 수 있으나, 컬럼이 같은 txt파일을 하나의 폴더에 넣고 순서대로 읽어온다면 상관없을 듯 합니다.

      실행속도는 너무 느리지만 않다면 괜찮습니다.


      • 2020-11-03 17:59

        @audtjs1366@naver.com 님 작성자님의 제시한 소스코드를 이해하고 있으면 가능합니다.

        다만,

        • 단순히 txt 파일을 열어서, 하나의 엑셀에 통합하는 것이 1차적 목적이고
        • 이 결과물로 가공을 하기 위해서는
        • 시스템에서 제공하는 컬럼이 다르다면, 다른 유형이 몇가지 인지 파악이 필요합니다.
        • 전체 발생하는 유형을 파악해여 통합된 결과물을 제시할 수 있기 때문입니다

         


  • 2020-11-03 14:24

    @audtjs1366@naver.com 님  

    추가적으로

    텍스트 파일이 많은데, 컬럼 구조가 동일하다면

    파일을 OS함수로 배치로 병합하여 처리하면, 속도는 상상할 수 없을 정도로 빨라 집니다.

    ( 예, 3000 행 * 80개 컬럼 * 200개 파일을 처리하는데, 단 20초에 정리가 되더군요)

     

    • 파일 병합관련 내용은 (지나가다 봐서 위치를 안내 못 드리네요) 사이트에서 검색하면 나옵니다.

     


  • 2020-11-03 21:02
    채택된 답변

    @audtjs1366@naver.com

    • 첨부 참조하세요.

    첨부파일 : 텍스트파일한번에읽어오기_201103.xlsm


전체 7,355
번호 카테고리 제목 작성자 작성일 추천 조회
알림
[🏆 VBA 4주 완성 특별 스터디] 1기 - 우수 졸업생 및 후기, 축하합니다! (6)
오빠두엑셀 | 2022.07.31 | 추천 8 | 조회 1246
오빠두엑셀 2022.07.31 8 1246
공지사항 함수/공식
[신규 기능 업데이트!] 👉 이제 게시글 작성시 스크린샷 복/붙이 가능합니다! 😎 (2)
오빠두엑셀 | 2022.08.04 | 추천 4 | 조회 108
오빠두엑셀 2022.08.04 4 108
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (88)
오빠두엑셀 | 2021.10.28 | 추천 117 | 조회 6200
오빠두엑셀 2021.10.28 117 6200
41174 함수/공식
New 엑셀 다른시트에서 날짜에 맞춰서 값 불러오기 수식을 어떻게 짜야할지 모르겠어요. 첨부파일 (6)
민호 | 15:37 | 추천 0 | 조회 28
민호 15:37 - 28
41173 함수/공식
New 오빠두에서 제공하는 견적서에 외화를 추가하고 싶습니다. (1)
농푸 | 15:36 | 추천 0 | 조회 17
농푸 15:36 - 17
41170 문서서식
New (초보질문)채우기 색만 복사 붙여넣기 하는 방법 좀 알려주세요~ 엑셀파일첨부파일 (4) 답변완료
추바카 | 12:57 | 추천 0 | 조회 29
추바카 12:57 - 29
41167 기능/도구
New 고급필터 결과값 셀에 형식을 주려면 어떻게 해야 할까요? (1)
| 06:38 | 추천 0 | 조회 24
06:38 - 24
41162 VBA
New 달력 유저폼에서 유저폼 X 하면 현재 날짜 변경 없으면 합니다. 엑셀파일첨부파일 (2) 답변완료
033cola | 2022.08.13 | 추천 0 | 조회 36
033cola 2022.08.13 - 36
41159 함수/공식
New 해당 조건 개수찾기 Max 함수 예제 엑셀파일 (1)
Kim Si Yoon | 2022.08.13 | 추천 0 | 조회 26
Kim Si Yoon 2022.08.13 - 26
41148 VBA
New 셀에서 특정 위치에 있는 문자의 색상을 변경하는 방법 첨부파일 (2) 답변완료
:+:하늘비:+: | 2022.08.13 | 추천 0 | 조회 47
:+:하늘비:+: 2022.08.13 - 47
41147 문서서식
New 엑셀에서 픽셀에 의미에 대해서 질문드립니다. 첨부파일 (5) 답변완료
월마리아 | 2022.08.13 | 추천 0 | 조회 48
월마리아 2022.08.13 - 48
41145 함수/공식
New 이런 경우는 어떻게 해야할까요? 첨부파일 (3) 답변완료
Utaeks | 2022.08.13 | 추천 1 | 조회 47
Utaeks 2022.08.13 1 47
41141 함수/공식
New 어떤 함수를 써야할까요? 첨부파일 (4)
머지않아VBA고수 | 2022.08.13 | 추천 0 | 조회 46
머지않아VBA고수 2022.08.13 - 46
41140 VBA
New 매크로 파일 저장방법이 따로 있나요?파일목록이 추가될때마다 저장확인창이 하나씩 늘어나요ㅠㅠ 첨부파일 (1)
pjc**** | 2022.08.13 | 추천 0 | 조회 22
pjc**** 2022.08.13 - 22
41132 함수/공식
New 함수를 간단히 할 수 있을까요? 엑셀파일 (3)
안동근 | 2022.08.12 | 추천 0 | 조회 46
안동근 2022.08.12 - 46
41129 기능/도구
New 빠른 실행 도구 모음에서 명령 레이블 표시 또는 숨기기 첨부파일 (9) 답변완료
fabikim | 2022.08.12 | 추천 0 | 조회 44
fabikim 2022.08.12 - 44
41126 VBA
New VBA 자동화 용어 및 규칙 등의 작동원리 해석 문의 엑셀파일 (2) 답변완료
pjc**** | 2022.08.12 | 추천 0 | 조회 49
pjc**** 2022.08.12 - 49
41123 함수/공식
New 어떤 함수가 입력된건지 알고싶어요.(함수가 가려져 있는건지 여부도 알고싶네요) 첨부파일 (2) 답변완료
일개회사원 | 2022.08.12 | 추천 0 | 조회 44
일개회사원 2022.08.12 - 44
41117 피벗테이블
New 데이터 모델 관련 오류인데 어떤 오류인지 도저히 모르겠습니다ㅠ 첨부파일 (2) 답변완료
ㅎㅎ | 2022.08.12 | 추천 0 | 조회 39
ㅎㅎ 2022.08.12 - 39
41116 피벗테이블
New 피벗테이블 슬라이서 문의 첨부파일 (1)
조동호 | 2022.08.12 | 추천 0 | 조회 31
조동호 2022.08.12 - 31
41114 함수/공식
New Lookup 함수사용시 출력되는 날짜의 형식오류 첨부파일 (3)
조민양 | 2022.08.12 | 추천 0 | 조회 31
조민양 2022.08.12 - 31
41112 피벗테이블
New 엑셀 피벗테이블 필터부분을 가리는 방법을 알고 싶습니다! 첨부파일 (3)
이재호 | 2022.08.12 | 추천 0 | 조회 29
이재호 2022.08.12 - 29
41107 함수/공식
New 중첩 이프 함수 해결 방법 / 중첩 if 해결 방법 / 다중조건 첨부파일 (2) 답변완료
떡상가즈아 | 2022.08.12 | 추천 0 | 조회 40
떡상가즈아 2022.08.12 - 40
41106 함수/공식
New 2X2 엑셀 값 비교하는 함수가 가능할까요? 첨부파일 (1)
클루 | 2022.08.12 | 추천 0 | 조회 38
클루 2022.08.12 - 38
41099 파워쿼리/피벗
New 여러개의 데이터 파일(같은 양식)을 데이터연결하여 하나의 파일로 관리(주기 관리)하는 방법 엑셀파일 (4) 답변완료
pjc**** | 2022.08.12 | 추천 -1 | 조회 67
pjc**** 2022.08.12 -1 67
41096 피벗테이블
New 피벗테이블 날짜 슬라이서 제작시 오류 첨부파일 (4)
naver_616841992e55a | 2022.08.12 | 추천 0 | 조회 37
naver_616841992e55a 2022.08.12 - 37
41095 VBA
New 매크로 관련 질문이예요. 도움 부탁드립니다. (3)
AANNNDDDYY | 2022.08.12 | 추천 0 | 조회 47
AANNNDDDYY 2022.08.12 - 47
41094 함수/공식
New 수식의 이름관리자가 열리지 않습니다 (6)
abbado | 2022.08.12 | 추천 0 | 조회 41
abbado 2022.08.12 - 41
41090 VBA
New 길이 계산 관련 문의 드립니다. 첨부파일 (2)
심우민 | 2022.08.11 | 추천 -1 | 조회 49
심우민 2022.08.11 -1 49
41087 문서서식
New 셀 초록색 표시 오류 해결 유튜브를 봤습니다. (1)
이성일 | 2022.08.11 | 추천 0 | 조회 34
이성일 2022.08.11 - 34
41086 함수/공식
New 어떤 수식 사용해야되는지 알려주세요ㅜ 첨부파일 (4)
🍒 | 2022.08.11 | 추천 0 | 조회 58
🍒 2022.08.11 - 58
41083 VBA
New VBA LISTBOX 활용에 대한 궁금증 엑셀파일 (3) 답변완료
박성원 | 2022.08.11 | 추천 0 | 조회 41
박성원 2022.08.11 - 41
41080 함수/공식
New index, match 함수 사용해서 일치하는 날짜 찾기 첨부파일 (2)
033cola | 2022.08.11 | 추천 0 | 조회 34
033cola 2022.08.11 - 34