[sql] Excel 테이블에서 SQL 쿼리를 실행하는 방법은 무엇입니까?

Program / 글쓴이

null이 아닌 전화 번호 필드가있는 AZ로 정렬 된 모든 성 필드의 다른 테이블에서 하위 테이블을 만들려고합니다. SQL을 사용하면이 작업을 매우 쉽게 수행 할 수 있지만 Excel에서 SQL 쿼리를 실행하는 방법에 대한 단서가 없습니다. 데이터를 postgresql로 가져 와서 쿼리하고 싶지만 약간 과도하게 보입니다.

내가하려는 일에 대해 SQL 쿼리 SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname가 트릭을 수행합니다. Excel이 기본적으로 할 수없는 일 이기에는 너무 간단 해 보입니다. Excel에서 이와 같은 SQL 쿼리를 실행하려면 어떻게해야합니까?

답변

이를 수행하는 좋은 방법은 여러 가지가 있으며 다른 사람들이 이미 제안한 바 있습니다. “SQL 트랙을 통해 Excel 데이터 가져 오기”에 이어 몇 가지 지침이 있습니다.

  1. Excel에는 “데이터 연결 마법사”가있어 다른 데이터 소스 또는 동일한 Excel 파일 내에서 가져 오거나 연결할 수 있습니다.

  2. Microsoft Office (및 OS)의 일부로 관심있는 두 가지 공급자는 이전 “Microsoft.Jet.OLEDB”와 최신 “Microsoft.ACE.OLEDB”입니다. 연결을 설정할 때이를 찾으십시오 (예 : 데이터 연결 마법사 사용).

  3. Excel 통합 문서에 연결되면 워크 시트 또는 범위는 테이블 또는 뷰와 동일합니다. 워크 시트의 테이블 이름은 달러 기호 ( “$”)가 추가되고 대괄호 ( “[“및 “]”)로 묶인 워크 시트의 이름입니다. 범위의 이름 일뿐입니다. 레코드 소스로 이름이 지정되지 않은 셀 범위를 지정하려면 대괄호 안의 시트 이름 끝에 표준 Excel 행 / 열 표기법을 추가합니다.

  4. 네이티브 SQL은 Microsoft Access의 SQL이 될 것입니다. (과거에는 JET SQL이라고 불렸지만 Access SQL은 진화했으며 JET는 더 이상 사용되지 않는 오래된 기술이라고 생각합니다.)

  5. 예, 워크 시트 읽기 : SELECT * FROM [Sheet1$]

  6. 예, 범위 읽기 : SELECT * FROM MyRange

  7. 예 : 이름이 지정되지 않은 셀 범위 읽기 : SELECT * FROM [Sheet1$A1:B10]

  8. 세부 사항을 살펴 보는 데 도움이되는 많은 책과 웹 사이트가 있습니다.

=== 추가 참고 사항 ===

기본적으로 Excel 데이터 원본의 첫 ​​번째 행에는 필드 이름으로 사용할 수있는 열 머리글이 포함되어 있다고 가정합니다. 그렇지 않은 경우이 설정을 해제해야합니다. 그렇지 않으면 데이터의 첫 번째 행이 “사라져”필드 이름으로 사용됩니다. HDR= setting연결 문자열의 확장 속성에 옵션 을 추가하면 됩니다. 지정할 필요가없는 기본값은 HDR=Yes입니다. 열 머리글이없는 경우 다음을 지정해야합니다.HDR=No ; 공급자는 필드 이름을 F1, F2 등으로 지정합니다.

워크 시트 지정시주의 사항 : 제공자는 데이터 테이블이 지정된 워크 시트의 맨 위, 맨 왼쪽, 비어 있지 않은 셀로 시작한다고 가정합니다. 즉, 데이터 테이블은 문제없이 행 3, 열 C에서 시작할 수 있습니다. 그러나 예를 들어 A1 셀의 데이터 위와 왼쪽에 워크 시트 제목을 입력 할 수 없습니다.

범위 지정시주의 사항 : 워크 시트를 레코드 원본으로 지정하면 공급자는 공간이 허용하는 한 워크 시트의 기존 레코드 아래에 새 레코드를 추가합니다. 범위 (이름이 지정되거나 지정되지 않음)를 지정하면 Jet는 공간이 허용하는 한 범위의 기존 레코드 아래에 새 레코드도 추가합니다. 그러나 원래 범위를 다시 쿼리하는 경우 결과 레코드 집합에는 범위 밖에 새로 추가 된 레코드가 포함되지 않습니다.

C에 대한 데이터 유형 (해볼 가치가있는) REATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

“오래된 기술”Excel에 연결 (xls 확장자가있는 파일) : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0; . Microsoft Excel 5.0 및 7.0 (95) 통합 문서에는 Excel 5.0 원본 데이터베이스 유형을 사용하고 Microsoft Excel 8.0 (97), 9.0 (2000) 및 10.0 (2002) 통합 문서에는 Excel 8.0 원본 데이터베이스 유형을 사용합니다.

“최신”Excel에 연결 (파일 확장자가 xlsx 인 파일) : Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

데이터를 텍스트로 처리 : IMEX 설정은 모든 데이터를 텍스트로 처리합니다. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

(자세한 내용은 http://www.connectionstrings.com/excel )

자세한 내용은 http://msdn.microsoft.com/en-US/library/ms141683(v=sql.90).aspx 및 http://support.microsoft.com/kb/316934에서

VBA를 통해 ADODB를 통해 Excel에 연결 http://support.microsoft.com/kb/257819에

http://support.microsoft.com/kb/275561의 Microsoft JET 4 세부 정보

답변

tl; dr; Excel은이 모든 작업을 기본적으로 수행합니다. 필터 및 / 또는 표 사용

( http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx )

oledb 연결을 통해 프로그래밍 방식으로 Excel을 열고 워크 시트 내의 테이블에서 SQL을 실행할 수 있습니다.

그러나 수식없이 필터 만 사용하면 원하는 모든 작업을 수행 할 수 있습니다.

  1. 데이터 내 아무 곳이나 클릭보고
  2. 리본 막대의 데이터로 이동
  3. “필터”를 선택하십시오가운데에 대해 하고 깔때기처럼 보입니다.
    • 이제 테이블의 첫 번째 행에있는 각 셀의 좁은쪽에 화살표가 있습니다.
  4. 전화 번호의 화살표를 클릭하고 공백을 선택 취소하십시오. (마지막 옵션)
  5. 성의 화살표를 클릭하고 성의 az ordering (상위 옵션)을 선택합니다.

놀아 보자 ..

  1. 필터링 된 행을 선택하여 다른 곳에 붙여 넣을 수 있습니다.
  2. 왼쪽의 상태 표시 줄에서 총 행 수 중에서 필터링 기준을 충족하는 행 수를 확인할 수 있습니다. (예 : 313 개 레코드 중 308 개 발견)
  3. 와드의 Excel 2010에서 색상별로 필터링 할 수 있습니다.
  4. 때로는 상태 또는 정리 된 버전의 데이터를 제공하는 계산 된 열을 만든 다음 이러한 항목으로도 필터링하거나 정렬 할 수 있습니다. (예 : 다른 답변의 공식처럼)

많은 작업을 수행하지 않거나 어딘가에 데이터 가져 오기를 자동화하려는 경우가 아니면 필터를 사용하여 수행하십시오.

C # 옵션 :

 OleDbConnection ExcelFile = new OleDbConnection( String.Format( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES\"", filename));
 ExcelFile.Open();

시작하기 편리한 곳은 생각보다 더 많은 것이있을 수 있으므로 스키마를 살펴 보는 것입니다.

List<String> excelSheets = new List<string>();

// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows) {
    string temp = row["TABLE_NAME"].ToString();
    if (temp[temp.Length - 1] == '$') {
         excelSheets.Add(row["TABLE_NAME"].ToString());
    }
}

그런 다음 시트를 쿼리하려는 경우 :

 OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + sheet + "]", ExcelFile);
 dt = new DataTable();
  da.Fill(dt);

참고-Excel에서 테이블 사용 :

Excel에는 데이터가 표처럼 작동하도록하는 “표”기능이 있습니다.이 기능은 몇 가지 큰 이점을 제공하지만 모든 유형의 쿼리를 수행 할 수는 없습니다.

http://office.microsoft.com/en-gb/excel-help/overview-of-excel-tables-HA010048546.aspx

Excel의 테이블 형식 데이터의 경우 이것이 내 기본값입니다. 먼저 데이터를 클릭 한 다음 리본의 홈 섹션에서 “표 형식”을 선택합니다. 이렇게하면 기본적으로 필터링 및 정렬이 가능하며 이름 (예 : table [fieldname])으로 테이블 및 필드에 액세스 할 수 있습니다. 이는 또한 최대 및 평균과 같은 열에 대한 집계 함수를 허용합니다.

답변

기본적으로 다음과 같이 할 수 있습니다.

  1. 표를 선택하고 Excel을 사용하여 성으로 정렬
  2. E1 및 E2에서 E1은 비어 있고 E2는 수식을 포함하는 2 행 x 1 열 고급 필터 기준을 만듭니다. =C6=""
    C6이 전화 번호 열의 첫 번째 데이터 셀인 이 됩니다.
  3. 표를 선택하고 고급 필터를 사용하고 E1 : E2의 기준 범위를 사용하여 범위에 복사하고 출력을 복사 할 위치를 지정합니다.

프로그래밍 방식으로이 작업을 수행하려면 매크로 레코더를 사용하여 위의 단계를 기록하고 코드를 살펴 보는 것이 좋습니다.

답변

Excel에서 SQL을 사용할 수 있습니다 . 잘 숨겨져 있습니다. 이 자습서를 참조하십시오.

http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html

답변

QueryStorm을 제공하는 것이 좋습니다. 을 사용해 볼 할 수 있습니다. Excel에서 SQL을 사용하는 것이 매우 편리하게 만드는 Excel 용 플러그인입니다.

또한 부분 유료화입니다. 자동 완성, 오류 신호등에 신경 쓰지 않는다면 무료로 사용할 수 있습니다. 다운로드하여 설치하기 만하면 Excel에서 SQL 지원이 제공됩니다.

면책 조항 : 저는 저자입니다.

답변

이 작업을 한 번만 수행해야하는 경우 Charles의 설명을 따르지만 필터를 동적으로 만들려는 경우 Excel 수식 및 도우미 열을 사용하여이 작업을 수행 할 수도 있습니다.

데이터가 데이터 시트 시트에 있고 다음 열의 2 행에서 시작한다고 가정합니다.

  • A : 성
  • B : 이름
  • C : 전화 번호

이 시트에 두 개의 도우미 열이 필요합니다.

  • D2 : =if(A2 = "", 1, 0), Where 조건에 해당하는 필터 열입니다.
  • E2 : =if(D2 <> 1, "", sumifs(D$2:D$1048576, A$2:A$1048576, "<"&A2) + sumifs(D$2:D2, A$2:A2, A2)), 이것은 주문에 해당합니다.

데이터가가는 한이 공식을 복사하십시오.

결과를 표시해야하는 시트에 다음 열을 만듭니다.

  • A : 2 행에서 1로 시작하는 일련의 숫자로, 얻을 수있는 총 행 수를 제한합니다 (속편의 제한과 유사).
  • B2 : =match(A2, DataSheet!$E$2:$E$1048576, 0), 이것은 해당 데이터의 행입니다.
  • C2 : =iferror(index(DataSheet!A$2:A$1048576, $B2), ""), 실제 데이터이거나 데이터가 없으면 비어 있습니다.

B2 및 C2의 공식을 복사하고 C 열을 D와 E로 복사합니다.

답변

Excel 파일을 직접 쿼리하는 경우 esProc를 사용해보십시오. https://esprocforbp.medium.com/directly-query-excel-text-files-using-sql-5315788231e4 를 참조하십시오 . 일반 SQL을 지원할뿐만 아니라 Group HAVING, Subquery, Nested Subquery, Join, 심지어 ‘with… as’Table Expression도 지원합니다. 예를 들면 다음과 같습니다.

$with A as
(select NAME as DEPT from E:/department.xlsx where NAME='HR' or NAME='Sales')
select A.DEPT DEPT,count(*) NUM,avg(B.SAL_ARY) AVG_SALARY from A left join E:/employee.xlsx B on A.DEPT=B.DEPT
where B.GENDER='F' group by A.DEPT

Excel에서 영역을 쿼리하려면 데이터를 클립 보드에 복사합니다. 그런 다음 esProc는 클립 보드 기능에 액세스하여 데이터 테이블을 가져온 다음 단일 테이블 쿼리를 수행합니다. 여러 영역을 쿼리하려면 각 데이터 영역을 esProc에 복사하고 esProc에서 여러 테이블을 가져와 예제와 같이 다중 테이블 쿼리를 수행합니다.

면책 조항 : 이것은 우리 도구 esProc에 관한 것입니다. 부분 유료화입니다.