ADODB 에서 SQL 사용 시 파라메타 값을 배열로 설정 가능한가요

VBA
작성자
밍장군
작성일
2020-09-16 14:50
조회
54
 
Dim TestElement
dim strSQL as string
TestElement = Array("a", "b", "c", "d", "e")
'1st
strSQL
= "SELECT a, b, c " & _
"FROM [Data$] " & _
"WHERE b > 0 " & _
"ORDER BY a;"
'2nd
strSQL = "SELECT " & testelement(0) & ", b, c " & _
"FROM [Data$] " & _
"WHERE b > 0 " & _
"ORDER BY a;"
 

그리고 반환은 꼭 a, b, c, d 순서로 전체 반환 인가요 아니면 a c 이렇게만 받을 수 있는건가요 그냥 조건 지정해 놓으면

알아서 오토필터 같은 기능을 수행해서 하는거 같은데...

 

그리고 여기 "오빠두엑셀" 님이 말씀하신대로

기초DB 를 가공해서 해보려는데 이게 과연

생성된 파일 복사 > 붙여넣기 > SQL 로 내용검색하기 > 시트에 내용 입력하기 > 복사된 내용 지우기

방법은 시간 소요가 최소 초단위로 걸리겠네요

 

그렇다면 SQL 문이 아닌 ADODB 로 데이터 연결 한 다음 원하는 키워드 찾아서 그 아래 행 데이터를 일괄적으로 받는

방법이 없나요

올린 DB 같이 실제 필요한 일종의 제목이 3행에 위치해 있고 4행은 세부항목으로 나뉘어 져 있어 실제 의미 있는 데이터는

5행부터 끝까지 여서 키워드별로 열을 지정해놓고 (컨디셔닝 = culumn number 6) 이런식으로 키워드별로 define 해 놓고 쓸 수 있는

방법 있나요?

계속 동일한 부분 질문때문에 죄송스럽네요

옆에 있으면 커피한잔 대접하고 싶은데 막심으루 다가

 

 

 

 

 
밍장군
회원등급 : 가지1단계
포인트 : 325 EP
전체 6

  • 2020-09-16 15:33

    안녕하세요.

    질문이 다소 포괄적이지만.. 간단히 답변을 드리면, '데이터만 잘 구성되어 있으면 SQL문으로 모두 해결 가능' 합니다. 여러 데이터셋 간의 JOIN 까지 모두 해결가능하므로, 모든 작업을 해결 가능하구요.. 다만 가장 중요한건 '정제된 데이터'여야 한다는 것 입니다^^;

    반환은 꼭 a, b, c, d 순서로 전체 반환 인가요 아니면 a c 이렇게만 받을 수 있는건가요 

    ==> a,c 만 받아올 수도 있습니다. b를 필터링한다고 해서 b가 반드시 반환범위에 포함되야 하는 것은 아닙니다.

    생성된 파일 복사 > 붙여넣기 > SQL 로 내용검색하기 > 시트에 내용 입력하기 > 복사된 내용 지우기

    ==> 데이터가 정제되어 있지 않을 경우 위 방법으로 진행하셔야겠지만, 그렇다면 굳이 ADO 커넥션으로 데이터를 불러와야 할 이유가 있을지는 모르겠습니다.

    ADODB 로 데이터 연결 한 다음 원하는 키워드 찾아서 그 아래 행 데이터를 일괄적으로 받는 방법이 없나요

    ==> 정제된 데이터에는 항상 고유한 ID열이 추가됩니다. 데이터에 ID열이 포함되어 있지 않다면, 방법이 없습니다.

    5행부터 끝까지 여서 키워드별로 열을 지정해놓고 (컨디셔닝 = culumn number 6) 이런식으로 키워드별로 define 해 놓고 쓸 수 있는 방법 있나요?

    ==> CASE문을 사용하면 조건열을 추가한 데이터셋을 반환할 수 있습니다. CASE문에 대한 설명은 아래 링크를 참고해보세요.

    https://www.essentialsql.com/sql-case-statement/

    답변이 도움이 되셨길 바랍니다.


    • 2020-09-16 16:29

      답변 너무 감사 드립니다 정말 입니다

      그럼 궁금 한게 위

      생성된 파일 복사 > 붙여넣기 > SQL 로 내용검색하기 > 시트에 내용 입력하기 > 복사된 내용 지우기

      ==> 데이터가 정제되어 있지 않을 경우 위 방법으로 진행하셔야겠지만, 그렇다면 굳이 ADO 커넥션으로 데이터를 불러와야 할 이유가 있을지는 모르겠습니다.

       

      요 부분 일단 database 형태는 정재되어 있지 않는 형태를 가지고 제가  일부 필요한 영역 데이터를 다운받아 활용하여야 하는데

      그렇다면 현재까지 알아낸 바에 의하면 그냥 화면 읽기 전용 모드로 읽어서 화면만 보이지 않게 하고 배열에 넣고 실행 후 닫고

      이후 처리 하는 방법까진 했는데 아무래도 시험 데이터가 지속적으로 만들어 지다 보니 강제로 열면 문제의 소지가 있습니다

       

      결론은 그럼 ADO 커넥션 으로 데이터를 부르지 않고 읽기 전용으로 강제로 데이터를 오픈하지 않고 해결 할 방법을 알고 싶습니다

      혹시라도 있다면 말이죠

      혹 아래 함수같은 거 말씀하시는건가요?

      Function getCellValue(filepath, filename, sheetname, row, column)
      Dim msg As Variant
      msg = "'" & filepath & "[" & filename & "]" & sheetname & "'!" & Cells(1, 1).Cells(row, column).Address(, , xlR1C1)
      getCellValue = ExecuteExcel4Macro(msg)

      하루 데이터가 10000행 정도 생기고 열은 50개 정도 되는데 그중 10000개 4개 열의 데이터 정도를 추출해야 할 사항입니다
      근데 여기서 문제는 row 값이 매번 바뀌는것도 그렇고 이 데이터의 최근 range 값을 배열에 넣고 다시 Target 시트의 배열 반환 시
      단순 배열로 하면 빠르겠지만
      만에하나 데이터 값이 (아직 호출은 안해봤지만) 하나씩만 나온다면 최악의 경우 for i = 1 to 10000 이면 노답이라
      설마 데이터 하나씩 나오진 않겠죠??

      혹 닫혀있는 파일 특정 column 의 5번 행부터 마지막 데이터 행 까지 데이터를 주기적으로 받아올 다른 방법 아시나요?
      질문 릴레이네요 유튜브 봐도 안나오고 Stack Fofum 봐도 명확하지 않고 (못찾는걸 수도 있지만)


      • 2020-09-16 21:30

        안녕하세요.

        이후 처리 하는 방법까진 했는데 아무래도 시험 데이터가 지속적으로 만들어 지다 보니 강제로 열면 문제의 소지가 있습니다

        그러시다면 Read-Only 속성을 True로 파일을 실행하는것도 고려해보시면 도움이 될 듯 합니다.

        Wb.Open FileName:="경로", ReadOnly:=True

        for i = 1 to 10000 이면 노답이라 설마 데이터 하나씩 나오진 않겠죠??

        10000개 행을 돌리실 경우 처리속도는 0.05초 내외로 동작합니다. 어느 방법을 사용하셔도 문제 없으실 듯 합니다. (개인적으로 받아오는 데이터가 엑셀(또는 csv) 물리적인 파일이라면 WorkBook.Open 으로 처리하는 것을 선호합니다.^^; 데이터를 서버에서 받아오거나 50만행 이상의 큰 데이터라면 ADO 커넥션이 반드시 필요하구요..)

        마지막 데이터 행 까지 데이터를 주기적으로 받아올 다른 방법 아시나요?

        OnTime 이벤트 관련 명령문을 검색해보세요. 주기적으로 데이터를 받아올 수 있습니다.

        답변이 도움이 되셨길 바랍니다.


        • 2020-09-17 13:34

          죄송하지만 질문 하나 더 드릴께요

          1. 정상적인 폼이 아닌 column 기준으로 데이터를 불러 오는 방법은 없나요 아래 첨부파일이 데이터베이스입니다

          저한테 필요한건 이중에서 K Q V S column 데이터 입니다 (말그대로 그냥 강제로 데이터 호출하는 방식이 없나요 예를 들어

          "K5" "Q5" "V5" "S5" 열 기준으로) Ado 라이브러리를 활용해서요

           

          2. 말씀하신 for 문을 써서 구현해야 한다면

          임시로 배열 메모리에 넣어서 마지막까지 차면 배열을 시트에 반환하는 방법 정도가 될거 같은데 너무 Loss 가 심할 거 같아서요

           

          3. 정 안되면 그냥 DB 내용 전체를 불러와서 시트에 붙여넣고 그담에 나머지 범위를 설정해서 제가 하고 싶은대로 가공해서 사용

          해도 될것 같은데 문제는 이렇게 하면 쓰레기 값이 들어오는 것 같더라구요

          사진 파일 같이 나옵니다

          코드는 아래 코드 입니다

          Dim Target_rng As Range
          Set Target_rng = ThisWorkbook.Sheets(1).Range("a1")
          If Len(Target_rng) > 0 Then
          Rows("1:" & Rows.count).ClearContents
          Set Target_rng = Range("a1")
          End If
          Debug.Print Target_rng
          path = test_fdr & "" & today_total & ".xls"

          OLEDB = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & path & ";Extended Properties=""Excel 8.0;HDR=YES"";"

          Set cnt = New ADODB.Connection
          cnt.Open OLEDB

          Rcdset.Open Source:="[" & TestSheetName & "$]", _
          ActiveConnection:=cnt, _
          CursorType:=adOpenStatic, _
          LockType:=adLockReadOnly, _
          Options:=adCmdTable

          With ActiveSheet.QueryTables.Add(Connection:=Rcdset, Destination:=Target_rng)
          .Refresh
          End With

          cnt.Close
          Set cnt = Nothing

          첨부파일 : sample-back-data.xls


          • 2020-09-17 14:16

            안녕하세요.

            첨부해주신 파일을 봤을 때 구분열이 ID 열로 사용되므로, 5번째 행 이후 값만 가져오신 뒤 처리하시면 될 듯 합니다.

            어떤 프로그래밍 언어로 작업해도, 정규화되지 않은 데이터는 모두 배열로 받아온 뒤 후처리 작업을 해 주셔야 합니다. 그렇기 때문에 많은 양의 데이터를 다루기 위해서는 데이터 형식이 더 중요한 것이구요..

            다시 말씀드리는 듯 하지만 SQL 언어로 특정 행만 호출하는 방법은 고유값이 없으며 불가능합니다.

            첨부데이터를 보시면, 가장 큰 문제로 데이터가 정규화되지 않아 a1셀이 "총생산수량" 그리고 그 밑으로 셀 병합이 되어 있는데요. 따라서 SQL 언어로 값을 불러 올 수 없기 때문에 ADO 연결로는 DB호출이 어렵지 않을까 생각됩니다.

            요약하자면, DB 형식을 바꿀 수 없는 환경이시라면, 배열로 받아온 뒤 후처리 하느 방법으로 접근하셔야 할 것으로 보입니다.

            답변이 도움이 되셨길 바랍니다.


            • 2020-09-18 09:18

              답변 감사합니다

              제가 잘 못 사용하는 걸 수도 있지만 어제 2가지 케이스로 구현해본 결과

              1st : 전체 데이터베이스를 불러온다 -> 시트에 붙여넣는다 -> 배열에 집어넣는다 -> 원하는 시트에 복사한다 -> 실시간 차트로 뿌려준다

              2nd : 읽기 전용으로 네트워크 데이터를 불러본다 -> 원하는데이터를 배열로 넣는다 -> 시트에 복사한다 -> 실시간 차트로 뿌려준다

              확실히 2번째 택했던게 시간이 덜걸립닌다

              1번은 한 2초 정도 걸리는데 반해 2번은 0.5초 이내에 완료가 되네요

              현재 네트워크상에 계속 씌여지고 있는 파일에 영향을 미치지 않는다면 그냥 읽기 전용으로 읽어서 원하는 파일주소를 호출하는 방법이 나을 것 같네요

              힘들게 돌아돌아 결국 원점 이지만 adodb 라는 외부 데이터 읽는 기능이 있다는 거 인지하기에 좋은 기회가 되었습니다

              감사합니다


    전체 1,192
    번호 카테고리제목작성자작성일추천조회
    1188 기능/도구
    New 엑셀 필터 조회 안될때 문의 (1)
    이영호 | 2020.09.29 | 추천 0 | 조회 17
    이영호2020.09.29017
    1187 함수/공식
    New 특정 문자 포함 셀 값 합치기 (5)
    해봐 | 2020.09.29 | 추천 0 | 조회 35
    해봐2020.09.29035
    1186 문서서식
    New 다중필터 관련 질문 (1)
    벚꽃이진다고너를 | 2020.09.29 | 추천 0 | 조회 25
    벚꽃이진다고너를2020.09.29025
    1185 함수/공식
    New 로또 추출기 (6)
    프리맨소울 | 2020.09.28 | 추천 0 | 조회 45
    프리맨소울2020.09.28045
    1184 함수/공식
    New 월별 누적 매출 구하기 질문 있습니다. 첨부파일 (1)
    [email protected] | 2020.09.28 | 추천 0 | 조회 33
    [email protected]2020.09.28033
    1183 함수/공식
    New (연평균 증가률 질문 드려요) 시작값, 종료값이 마이너스 또는 플러스일때 (1)
    푸우 | 2020.09.28 | 추천 0 | 조회 25
    푸우2020.09.28025
    1182 함수/공식
    New xfilter함수 다중범위 이용하는게 가능할까요? (3)
    David Cho | 2020.09.28 | 추천 0 | 조회 31
    David Cho2020.09.28031
    1181 함수/공식
    New Sum(Sumifs) 함수로 조건에 맞는 월별 합계를 구하고자 하는데 다른 방법이 있을지 질문드립니다. (8)
    Hannah | 2020.09.28 | 추천 0 | 조회 40
    Hannah2020.09.28040
    1180 함수/공식
    New 중복 카운트 (2)
    유리비 | 2020.09.28 | 추천 0 | 조회 41
    유리비2020.09.28041
    1179 함수/공식
    New 도움이 필요합니다 (배열형태 개수를 구하는 함수) 첨부파일 (2)
    벗님들 | 2020.09.28 | 추천 0 | 조회 22
    벗님들2020.09.28022
    1178 VBA
    New HTS연동 된 값 질문 입니다. (3)
    COXMAN | 2020.09.27 | 추천 0 | 조회 30
    COXMAN2020.09.27030
    1177 차트/그래프
    조건부서식 규칙관리자 첨부파일 (2)
    강재성 | 2020.09.27 | 추천 0 | 조회 34
    강재성2020.09.27034
    1176 문서서식
    행 2개로 구분되어 있는 텍스트를 열 2개로 만드는 방법은 없을까요..? (4)
    찬찬 | 2020.09.26 | 추천 0 | 조회 40
    찬찬2020.09.26040
    1175 함수/공식
    함수 참조 문제 첨부파일 (2)
    1oi1oi1oi | 2020.09.26 | 추천 0 | 조회 28
    1oi1oi1oi2020.09.26028
    1174 피벗테이블
    시간슬라이서와 피벗테이블 주 간격 생성방법 첨부파일 (4)
    iviolin**** | 2020.09.26 | 추천 0 | 조회 31
    iviolin****2020.09.26031
    1173 함수/공식
    섬프로덱트+인덱스매치 조합 외에도 다른 방식이 있는지 궁금합니다. 첨부파일 (3)
    달퐁이 | 2020.09.26 | 추천 0 | 조회 46
    달퐁이2020.09.26046
    1172 기능/도구
    엑셀늅인데 정렬에 관해서 궁금합니다 ㅠ (3)
    유녕 | 2020.09.25 | 추천 0 | 조회 38
    유녕2020.09.25038
    1171 파워쿼리/피벗
    데이터셋을 재구성하는 방법 질문.. 첨부파일 (2)
    김연수 | 2020.09.25 | 추천 0 | 조회 35
    김연수2020.09.25035
    1170 VBA
    VBA에서 변수로 영역 지정하는 방법과 구문의 구조에 대한 질문입니다 (4)
    공대생 | 2020.09.24 | 추천 0 | 조회 39
    공대생2020.09.24039
    1169 VBA
    웹 크롤링 관련 질문드립니다. (5)
    hall**** | 2020.09.24 | 추천 2 | 조회 55
    hall****2020.09.24255
    1168 함수/공식
    NaverFinanceSearch 함수 기능 문의 첨부파일 (2)
    알파보이 | 2020.09.24 | 추천 0 | 조회 46
    알파보이2020.09.24046
    1167 차트/그래프
    동적범위를 이용한 날짜 합계 구하는 함수 문의 (2)
    째쨈 | 2020.09.24 | 추천 0 | 조회 44
    째쨈2020.09.24044
    1166 피벗테이블
    대량의 두개의 데이터를 합친후 데이터수정 및 합계결과 뽑아내는법이 궁금합니다 (1)
    자유인 | 2020.09.24 | 추천 0 | 조회 49
    자유인2020.09.24049
    1165 VBA
    VBA 여러개 시트를 활용한 피벗데이블 질문 첨부파일 (4)
    sundaychefchef | 2020.09.23 | 추천 0 | 조회 43
    sundaychefchef2020.09.23043
    1164 VBA
    if문의 조건에 대해 질문드립니다. (7)
    엑셀늅늅이 | 2020.09.23 | 추천 0 | 조회 50
    엑셀늅늅이2020.09.23050
    1163 함수/공식
    엑셀 배열을 합칠 수 있는 방법이 있을까요? (7)
    나그네 | 2020.09.23 | 추천 0 | 조회 53
    나그네2020.09.23053
    1162 함수/공식
    경우의 수 관련 질문드립니다. (10)
    speedoo | 2020.09.23 | 추천 0 | 조회 53
    speedoo2020.09.23053
    1161 함수/공식
    데이터 뽑기 첨부파일 (1)
    soung**** | 2020.09.23 | 추천 0 | 조회 51
    soung****2020.09.23051
    1160 VBA
    기초적인 질문 하나만 할께요 2차원 배열에서 range값을 배열로 받아서 다른 시트에 배열 붙여넣는 방법이 어떻게 되나요 (1)
    밍장군 | 2020.09.23 | 추천 0 | 조회 22
    밍장군2020.09.23022
    1159 VBA
    (VBA) 도형서식 -> 채우기 -> 그림 또는 질감 채우기 -> 다음에서 그림 삽입 -> 클립보드 선택 구현 (1)
    건주 | 2020.09.23 | 추천 0 | 조회 32
    건주2020.09.23032