Excel 테이블에서 SQL 쿼리를 실행하는 방법은 무엇입니까?
전화 번호 필드가 null이 아닌 A-Z로 정렬된 모든 성 필드의 다른 테이블에서 하위 테이블을 작성하려고 합니다.SQL을 사용하면 이 작업을 쉽게 수행할 수 있지만, Excel 내에서 SQL 쿼리를 실행하는 방법에 대해 전혀 알지 못합니다.저는 postgresql로 데이터를 가져와서 거기에 쿼리하고 싶지만, 그것은 약간 지나친 것 같습니다.
쿼리입니다.SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastname할 수 .엑셀이 네이티브로 할 수 없는 것이기에는 너무 단순한 것 같습니다.엑셀 내에서 이와 같은 SQL 쿼리를 실행하려면 어떻게 해야 합니까?
다른 사람들이 이미 제안한 바와 같이, 이를 수행하는 데에는 많은 훌륭한 방법이 있습니다."SQL 트랙을 통해 Excel 데이터 가져오기"에 이어 다음과 같은 포인터가 있습니다.
Excel에는 다른 데이터 원본 또는 동일한 Excel 파일 내에서 가져오거나 연결할 수 있는 "데이터 연결 마법사"가 있습니다.
Microsoft Office(및 OS)의 일부로서 관심을 끄는 두 가지 공급업체는 이전의 "Microsoft"입니다."Jet.OLEDB", 그리고 최신 "Microsoft".ACE.OLEDB.연결을 설정할 때(예: 데이터 연결 마법사) 해당 항목을 찾습니다.
Excel 워크북에 연결되면 워크시트 또는 범위가 표 또는 보기와 동일합니다.워크시트의 테이블 이름은 워크시트에 달러 기호("$")가 추가되고 대괄호("[" 및 "]")로 둘러싸인 워크시트의 이름입니다. 범위의 이름은 단순히 범위의 이름입니다.이름 없는 셀 범위를 레코드 원본으로 지정하려면 각괄호의 시트 이름 끝에 표준 Excel 행/열 표기법을 추가합니다.
네이티브 SQL은 (거의) Microsoft Access의 SQL이 될 것입니다.(과거에는 JET SQL로 불렸지만 Access SQL은 발전했으며 JET는 오래된 기술로 사용되지 않는다고 생각합니다.)
: 예제, 워트읽:
SELECT * FROM [Sheet1$]: 위, 범읽기:
SELECT * FROM MyRange이름 : 예: 이: 름셀 범위 기읽:
SELECT * FROM [Sheet1$A1:B10]세부 사항을 검토하는 데 도움이 되는 많은 책과 웹 사이트가 있습니다.
추가 참고 사항
기본적으로 Excel 데이터 원본의 첫 번째 행에는 필드 이름으로 사용할 수 있는 열 제목이 포함되어 있다고 가정합니다.그렇지 않은 경우 이 설정을 해제해야 합니다. 그렇지 않으면 필드 이름으로 사용할 첫 번째 데이터 행이 "사라집니다".은 선택 인 " 작은옵을추수다니행됩여하가션이업다▁the니▁adding수▁this됩▁optional"를 추가함으로써 됩니다.HDR= setting연결 문자열의 확장 속성으로 이동합니다.은 " 할필없기는같다다습니음"입니다.HDR=Yes제목이 에는 열제이없경다지정합니다야를 .HDR=No공급자는 필드 이름을 F1, F2 등으로 지정합니다.
워크시트 지정 시 주의사항:공급자는 데이터 표가 지정된 워크시트의 맨 위, 맨 왼쪽, 맨 위 셀로 시작한다고 가정합니다.즉, 데이터 표는 3행 C열에서 문제 없이 시작할 수 있습니다.그러나 예를 들어, 셀 A1의 데이터 위와 왼쪽에 워크시트 제목을 입력할 수는 없습니다.
범위를 지정할 때 주의해야 할 사항워크시트를 레코드 원본으로 지정하면 공급자는 공간이 허용하는 대로 워크시트의 기존 레코드 아래에 새 레코드를 추가합니다.범위(이름 지정 또는 이름 지정)를 지정하면 공간이 허용하는 범위의 기존 레코드 아래에 새 레코드를 추가합니다.그러나 원래 범위에 대해 요청할 경우 결과 레코드 집합에는 새로 추가된 레코드가 범위 밖에 포함되지 않습니다.
다음을 위한 데이터 유형(시도해 볼 가치가 있음)CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.
"tech" 확장자가 에 "old tech" 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 에서 확인할 수 있습니다.
ADODB를 통해 Excel에 연결하는 VBA에 대한 자세한 내용은 http://support.microsoft.com/kb/257819 을 참조하십시오.
Microsoft JET 4 세부 정보는 http://support.microsoft.com/kb/275561 에서 확인할 수 있습니다.
tl;dr; Excel은 이 모든 것을 기본적으로 수행합니다 - 필터 및/또는 테이블 사용
(http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx)
oledb 연결을 통해 Excel을 프로그래밍 방식으로 열고 워크시트 내 테이블에서 SQL을 실행할 수 있습니다.
그러나 공식 없이 필터만 사용하여 원하는 모든 작업을 수행할 수 있습니다.
- 보고 있는 데이터 내의 아무 곳이나 클릭
- 리본 막대의 데이터로 이동
- "필터"를 선택하십시오. 중간 정도이며 깔때기처럼 보입니다.
- 이제 테이블의 첫 번째 줄에 있는 각 셀의 꽉 막힌 손에 화살표가 있을 것입니다.
- 전화 번호의 화살표를 클릭하고 공백 선택 취소(마지막 옵션)
- 성에 있는 화살표를 클릭하고 a-z 순서를 선택합니다(맨 위 옵션).
주위에서 연극을 합니다.주의할 사항:
- 필터링된 행을 선택하여 다른 곳에 붙여넣을 수 있습니다.
- 왼쪽의 상태 표시줄에는 총 행 수 중 필터 기준을 충족하는 행 수가 표시됩니다.(예: 313개 레코드 중 308개 레코드 발견)
- Excel 2010 이후에는 색상별로 필터링할 수 있습니다.
- 때때로 저는 상태나 정리된 데이터 버전을 제공하는 계산된 열을 생성한 다음 이를 기준으로 필터링하거나 정렬할 수 있습니다.(예: 다른 답변의 공식과 같이)
필터를 많이 사용하거나 데이터 가져오기를 자동화하려는 경우가 아니라면 필터를 사용하여 작업을 수행합니다.하지만 완전성을 위해:
Ac# 옵션:
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])으로 테이블과 필드에 액세스할 수 있습니다. 또한 최대 및 평균과 같은 열에서 집계 함수를 사용할 수 있습니다.
QueryStorm을 사용해 보는 것이 좋습니다. Excel용 플러그인이므로 Excel에서 SQL을 사용하는 것이 매우 편리합니다.
SQL 스크립트에서 Excel 테이블은 일반 데이터베이스 테이블인 것처럼 표시됩니다.
선택/업데이트/삽입/삭제의 4가지 SQL 데이터 작업이 모두 지원됩니다.
쿼리를 실행하는 엔진은 SQLite이므로 조인, 공통 테이블 표현식, 창 함수 등을 사용할 수 있습니다.그리고 코드 완성, 자동 포맷, 기호 툴팁 등과 같은 멋진 것들을 얻을 수 있습니다.
개인과 중소기업이 사용할 수 있는 완전 무료 커뮤니티 에디션이 있습니다.직원 수가 5명 이상이거나 연간 매출이 100만 달러 이상인 회사의 경우 유료 라이센스가 필요하지만 평가를 위해 무료 평가판 키를 사용할 수 있습니다.
이 블로그 게시물은 플러그인의 SQL 기능에 대해 훨씬 더 자세히 설명합니다.
고지 사항:제가 작가입니다.
기본적으로 다음과 같이 이 작업을 수행할 수 있습니다.
- 테이블을 선택하고 Excel을 사용하여 성에 따라 정렬합니다.
- x 기준을 합니다. 있고 식 예 E1과 E2에 2행 x 1열을 합니다. 여기서 E1은 비어 있고 E2는 공식을 포함합니다.
=C6=""여기서 C6은 전화 번호 열의 첫 번째 데이터 셀입니다. - 테이블을 선택하고 고급 필터를 사용하여 E1:E2의 기준 범위를 사용하여 범위에 복사하고 출력을 복사할 위치를 지정합니다.
프로그래밍 방식으로 이 작업을 수행하려면 매크로 레코더를 사용하여 위의 단계를 기록하고 코드를 확인하는 것이 좋습니다.
여기서 허용되는 답변은 오래된 기술이므로 시도해서는 안 됩니다.
이 질문이 작성되었을 때만 해도 전원 쿼리는 잘 알려진 옵션이 아니었고 최신 버전의 Office에서 별도의 추가 기능으로 설치하지 않은 경우에만 사용할 수 있었습니다.
이제 Power Query는 Excel에 포함되어 있으며 기본적으로 데이터를 가져오는 데 사용됩니다.이것은 올바른 방법입니다.그것은 간단하고, 빠르고, 효과적입니다.
다음은 Power Query의 질문에 대한 대답입니다.이를 복제하는 데 도움이 필요한 경우 "전원 쿼리 시작하기"를 검색합니다.Power Query를 시작하면 Advanced Editor를 사용하여 매우 기본적이고 쉽게 수행할 수 있습니다.
let
Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"lastname", type text}, {"firstname", type text}, {"phonenumber", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([phonenumber] <> null)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"lastname", "firstname", "phonenumber"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"lastname", Order.Ascending}})
in
#"Sorted Rows"
당신은 엑셀에서 SQL을 사용할 수 있습니다.그것은 잘 숨겨져 있을 뿐입니다.이 자습서를 참조하십시오.
http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html
이 작업을 한 번만 수행해야 하는 경우 Charles의 설명을 따르지만 필터를 동적으로 설정하려는 경우 Excel 수식과 도우미 열을 사용하여 수행할 수도 있습니다.
데이터가 시트 데이터 시트에 있고 다음 열의 2행에서 시작한다고 가정합니다.
- A: 성씨
- B: 이름
- C: 전화번호
이 시트에는 도우미 열이 두 개 필요합니다.
- D2:
=if(A2 = "", 1, 0)condition에 열입니다. - 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로 복사합니다.
Expat 라이브러리에 대해 에서 GDAL/OGR을 컴파일한 경우 XLSX 드라이버를 사용하여 .xlsx 파일을 읽고 명령 프롬프트에서 SQL 식을 실행할 수 있습니다.예를 들어 스프레드시트와 동일한 디렉터리에 있는 osgeo4w 셸에서 ogrinfo 유틸리티를 사용합니다.
ogrinfo -dialect sqlite -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx
에서 SQLite 쿼리를 실행합니다.sheet1쿼리 결과를 비정상적인 형식으로 출력합니다.
INFO: Open of `Book1.xlsx'
using driver `XLSX' successful.
Layer name: SELECT
Geometry: None
Feature Count: 36
Layer SRS WKT:
(unknown)
name: String (0.0)
count(*): Integer (0.0)
OGRFeature(SELECT):0
name (String) = Red
count(*) (Integer) = 849
OGRFeature(SELECT):1
name (String) = Green
count(*) (Integer) = 265
...
또는 ogr2ogr을 사용하여 동일한 쿼리를 실행하여 간단한 CSV 파일을 만듭니다.
$ ogr2ogr -f CSV out.csv -dialect sqlite \
-sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx
$ cat out.csv
name,count(*)
Red,849
Green,265
...
이전의 .xls 파일에서도 유사한 작업을 수행하려면 FreeXL 라이브러리를 기반으로 구축된 XLS 드라이버가 필요합니다. 이는 실제로 일반적이지 않습니다(예: OSGeo4w가 아님).
원하는 언어/플랫폼에서 Excel용 네이티브 DB 드라이버를 사용하여 테스트할 수 있습니다.Java 세계에서는 Excel 시트로 직접 작업할 수 있는 JDBC 드라이버를 제공하는 http://code.google.com/p/sqlsheet/ 를 사용해 볼 수 있습니다.마찬가지로 다른 플랫폼의 DB 기술 드라이버를 얻을 수 있습니다.
그러나 이러한 래퍼 라이브러리가 제공하는 기능의 수가 곧 벽에 부딪힐 것이라고 장담할 수 있습니다.더 나은 방법은 Apache HSSF/POI 또는 유사한 수준의 라이브러리를 사용하는 것이지만 더 많은 코딩 노력이 필요합니다.
Microsoft Access 및 LibreOffice Base는 스프레드시트를 원본으로 열고 이에 대한 SQL 쿼리를 실행할 수 있습니다.그것이 모든 종류의 쿼리를 실행하고 매크로를 실행하거나 코드를 작성하는 혼란을 피하는 가장 쉬운 방법입니다.
Excel에는 자동 필터와 데이터 정렬 기능이 있어 예시와 같은 많은 간단한 쿼리를 수행할 수 있습니다.이러한 기능에 대한 도움이 필요하다면 Google이 저보다 더 나은 튜토리얼 소스가 될 것입니다.
제가 오해를 하고 있을지도 모르지만, 피벗 테이블은 바로 이런 것이 아닌가요?테이블에 데이터가 있습니까? 아니면 필터링된 목록만 있습니까?테이블이 하나(ctrl+l)가 아닌 경우 테이블의 셀을 활성화하고 다른 시트에 피벗 테이블을 삽입합니다.그런 다음 행 섹션에 성, 이름, 전화 번호 열을 추가합니다.그런 다음 필터 섹션에 전화 번호를 추가하고 null 값을 필터링합니다.이제 정상처럼 정렬합니다.
언급URL : https://stackoverflow.com/questions/18798522/how-to-run-a-sql-query-on-an-excel-table
'programing' 카테고리의 다른 글
| VBA 메시지 상자에서 선택 가능한 텍스트 (0) | 2023.05.23 |
|---|---|
| Documents 폴더에 파일이 있는지 확인하는 방법은 무엇입니까? (0) | 2023.05.23 |
| Mongoose - 전자 메일 구문의 유효성 검사 (0) | 2023.05.23 |
| npm을 사용하여 앱의 종속성을 어떻게 다시 설치합니까? (0) | 2023.05.23 |
| 앱스토어에서 애플리케이션에 대한 올바른 'aps-environment' 자격 문자열을 찾을 수 없습니다. (0) | 2023.05.23 |