Como executar uma consulta SQL em uma tabela Excel?
estou a tentar criar uma sub-tabela de outra tabela de todos os campos de sobrenome ordenados A-Z que têm um campo de número de telefone que não é nulo. Eu poderia fazer isso muito fácil com SQL, mas eu não tenho nenhuma pista de como fazer sobre a execução de uma consulta SQL dentro do Excel. Estou tentado a importar os dados para o postgresql e apenas consultá-lo lá, mas isso parece um pouco excessivo.
Pelo que estou a tentar fazer, a consulta SQL faria o truque. Parece demasiado simples para ser algo que Excel não pode fazer nativamente. Como posso executar uma consulta SQL como esta a partir do Excel?11 answers
O Excel tem o" Assistente de conexão de dados " que lhe permite importar ou ligar de outra fonte de dados ou mesmo dentro do mesmo arquivo Excel.
Como parte do Microsoft Office (e OS's) são dois provedores de interesse: o antigo Microsoft.Jacto.OLEDB", e o mais recente "Microsoft.AS.OLEDB". Olhar para eles ao configurar uma conexão (como com o Assistente de conexão de dados).
Uma vez conectado a um manual do Excel, uma planilha ou Gama é o equivalente a uma tabela ou vista. O nome da tabela de um documento de trabalho é o nome da folha de trabalho com um sinal de Dólar ("$") anexado a ele, e rodeado por parênteses quadrados ("[" e "]"); de um intervalo, é simplesmente o nome do intervalo. Para especificar uma gama de células sem nome como sua fonte de registros, adicione a notação padrão linha/coluna do Excel ao fim do nome da folha entre parênteses rectos.
O SQL nativo (mais ou menos ser) o SQL do Microsoft Access. (No passado, era chamado JET SQL; no entanto o acesso SQL evoluiu, e eu acredito que JET é obsoleta tecnologia antiga.)
Exemplo: ler uma folha de trabalho: Seleccionar * de [Sheet1$]
Exemplo: ler um intervalo: Seleccionar * de MyRange
Exemplo: ler um intervalo de células sem nome: Seleccionar * de [Sheet1$A1: B10]
Há muitos muitos livros e sites disponíveis para ajudá-lo a trabalhar através dos detalhes.
= = = Notas Adicionais = = =
Por omissão, assume-se que a primeira linha da sua fonte de dados do Excel contém títulos de colunas que podem ser usados como nomes de campos. Se este não for o caso, você deve desligar esta configuração, ou a sua primeira linha de dados "desaparece" para ser usado como nomes de campos. Isto é feito adicionando a opção HDR= ao Propriedades extendidas do texto de ligação. O padrão, que não precisa ser especificado, é HDR=Sim. Se você não tiver cabeçalho de colunas, você precisa especificar HDR=No; o provedor nomeia seus campos F1, F2, etc.
Uma precaução em Especificar folhas de trabalho: o fornecedor assume que a sua tabela de dados começa com a célula mais alta, Mais esquerda, não-em branco na folha de trabalho especificada. Em outras palavras, sua tabela de dados pode começar na linha 3, coluna C sem um problema. No entanto, não pode, por exemplo, digite um título da planilha acima e à esquerda dos dados na célula A1.
Uma precaução sobre a especificação dos intervalos: Quando indicar uma folha de trabalho como fonte de registo, o fornecedor adiciona novos registos abaixo dos registos existentes na folha de trabalho, à medida que o espaço o permite. Quando você especifica um intervalo (nomeado ou não), Jet também adiciona novos registros abaixo dos registros existentes na faixa como o espaço permite. No entanto, se você requisitar na gama original, o conjunto de recordes resultante não inclui o recém-adicionado registos fora do alcance.
Tipos de dados (vale a pena tentar) para criar tabela: Curto, Longo, Único, Duplo, moeda, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.
Ligação ao" old tech " Excel (ficheiros com extensão xls): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0;
. Use o Excel 5.0 source database type para Microsoft Excel 5.0 e 7.0 (95) workbooks e use o Excel 8.0 source database type para Microsoft Excel 8.0 (97), 9.0 (2000) e 10.0 (2002) livro.
A ligar ao" latest " Excel (ficheiros com a extensão de ficheiro xlsx): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"
Tratar os dados como texto: a configuração IMEX trata todos os dados como texto. Provedor = Microsoft.AS.OLEDB.12.0; Data Source=Excel2007file.xlsx; Extended Properties= "Excel 12. 0 Xml; HDR=sim; IMEX=1";
(Mais detalhes em http://www.connectionstrings.com/excel)
Mais informação em http://msdn.microsoft.com/en-US/library/ms141683 (V = sql.90).aspx , e at http://support.microsoft.com/kb/316934
Ligação ao Excel via ADODB via VBA detalhada em http://support.microsoft.com/kb/257819
Detalhes do Microsoft JET 4 em http://support.microsoft.com/kb/275561
Você pode fazer isso nativamente da seguinte forma:
- seleccione a tabela e use o Excel para a classificar no último nome
- criar um filtro avançado de 2 linhas por 1 coluna, por exemplo em
E1 e E2, em que o E1 está vazio e o E2 contém a fórmula
=C6=""
onde C6 é a primeira célula de dados da coluna do número de telefone. - seleccione a tabela e use o filtro avançado, copie para um intervalo, usando o intervalo de critérios em E1:E2 e especificar onde deseja copiar o saída para
Se quero fazer isso programaticamente, sugiro que você use o gravador Macro para gravar os passos acima e olhar para o código.
TL; dr; Excel faz todos estes filtros nativamente - use e ou tabelas
(http://office.microsoft.com/en-gb/excel-help/filter-data-in-an-excel-table-HA102840028.aspx)
Você pode abrir o excel programaticamente através de uma ligação oledb e executar o SQL nas tabelas dentro da folha de trabalho.
Mas podes fazer tudo o que estás a pedir para fazer sem fórmulas apenas filtros.
- clique em qualquer lugar dentro do data {[10] } estás a olhar para
- Ir para os dados da barra de fita
-
Seleccione "Filtro" é sobre o meio e parece um funil
- você terá setas no lado apertado de cada célula na primeira linha da sua mesa agora
- carregue na seta do número de telefone e retire os espaços em branco (última opção)
- carregue na seta do último nome e Seleccione a-z ordenando (Opção superior)
Brincar.. algumas coisas a notar:
- Você pode seleccionar as linhas filtradas e pastá-las noutro lugar
- na barra de estado à esquerda, irá ver quantas linhas satisfazem os critérios de filtragem do número total de linhas. (por exemplo, 308 dos 313 registos encontrados)
- Você pode filtrar por cor no excel 2010 em wards
- às vezes eu crio colunas calculadas que dão status ou versões limpas de dados que você pode filtrar ou Ordenar por teses também. (por exemplo, como o fórmulas nas outras respostas)
Faça-o com filtros a menos que vá fazê-lo muito ou queira automatizar a importação de dados em algum lugar ou algo assim.. mas para completar:
Uma opção 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();
Um bom ponto de partida é ver o esquema, pois pode haver mais do que pensa.
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());
}
}
Então quando quiser consultar uma folha:
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + sheet + "]", ExcelFile);
dt = new DataTable();
da.Fill(dt);
NOTE-Use tabelas no excel!:
O Excel tem "tabelas" funcionalidade que faz com que os dados se comportem mais como uma tabela.. isso lhe dá alguns grandes benefícios, mas não vai deixá-lo fazer todos os tipos de consulta.
Http://office.microsoft.com/en-gb/excel-help/overview-of-excel-tables-HA010048546.aspx
Para os dados tabulares no excel este é o meu padrão.. a primeira coisa que eu faço é clicar nos dados, em seguida, selecione "format as table" da seção home na fita. isto dá-lhe filtragem e ordenação por omissão e permite-lhe aceder ao tabela e campos pelo nome (por exemplo, tabela [nome do campo]) isto também permite funções agregadas nas colunas, por exemplo, max e average
Você Pode Usar o SQL no Excel. Está bem escondido. Veja este tutorial:
Http://smallbusiness.chron.com/use-sql-statements-ms-excel-41193.html
Se precisar de fazer isto uma vez, basta seguir as descrições do Charles, mas também é possível fazê-lo com fórmulas do Excel e Colunas auxiliares, no caso de querer tornar o filtro dinâmico.
Vamos assumir que os dados estão na folha de dados e começam na linha 2 das seguintes colunas:
- A: apelido
- B: nome próprio
- C: Número de telefone
- D2:
=if(A2 = "", 1, 0)
, esta é a coluna do filtro, correspondente à sua condição de onde - E2:
=if(D2 <> 1, "", sumifs(D$2:D$1048576, A$2:A$1048576, "<"&A2) + sumifs(D$2:D2, A$2:A2, A2))
, isto corresponde à ordem de
Na folha que deve mostrar o seu resultado crie as seguintes colunas.
- a: uma sequência de números que começa com 1 na linha 2, isto limita o número total de linhas que pode obter (como um limite na sequência)
- B2:
=match(A2, DataSheet!$E$2:$E$1048576, 0)
, esta é a linha dos dados correspondentes - C2:
=iferror(index(DataSheet!A$2:A$1048576, $B2), "")
, Esta é a dados reais ou vazios se não existirem dados
Copie as fórmulas em B2 E C2 e as colunas C A D E e.
Você pode experimentar com o driver DB nativo para o Excel na linguagem / plataforma de sua escolha. No mundo Java, você pode tentar com http://code.google.com/p/sqlsheet / que fornece um driver JDBC para trabalhar directamente com folhas do Excel. Da mesma forma, você pode obter drivers para a tecnologia DB para outras plataformas.
No entanto, posso garantir que em breve você vai bater uma parede com o número de recursos que essas bibliotecas de envoltório fornecem. Melhor maneira será usar o Apache HSSF / POI ou nível semelhante de biblioteca, mas vai precisar de mais esforço de codificação.O Microsoft Access e a base LibreOffice podem abrir uma folha de cálculo como fonte e executar consultas sql sobre ela. Essa seria a maneira mais fácil de executar todos os tipos de consultas, e evitar a confusão de executar macros ou escrever código.
O Excel também tem auto-filtros e ordenação de dados que irão realizar um monte de consultas simples como o seu exemplo. Se você precisar de ajuda com esses recursos, o Google seria uma fonte melhor para tutoriais do que eu.
Sugiro que veja o motor de armazenamento do MySQL que basicamente lhe permite carregar QUALQUER ficheiro csv (facilmente criado a partir do excel) para a base de dados, assim que o tiver, poderá usar qualquer comando SQL que desejar.
Vale a pena dar uma vista de olhos.Posso sugerir dar QueryStorm uma tentativa-é um plugin para o Excel que torna bastante conveniente usar SQL no Excel.
Além disso, é freemium. Se você não se importa com o autocompletar, erros squigglies etc, Você pode usá-lo gratuitamente. Basta baixar e instalar, e você tem suporte SQL no Excel.Disclaimer: eu sou o autor.
Se tem GDAL / OGR compilado com o 'contra' a biblioteca Expat, poderá usar o controlador XLSX para ler .ficheiros xlsx e executar expressões SQL a partir de uma linha de comandos. Por exemplo, a partir de uma linha de comandos OSGeo4W na mesma pasta que a folha de cálculo, use a ogrinfo utilitário:
ogrinfo -dialect sqlite -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx
Irá executar uma consulta SQLite em sheet1
, e enviar o resultado da consulta de uma forma invulgar:
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
...
Ou executar a mesma pesquisa usando ogr2ogr para fazer um arquivo simples 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
...
Fazer semelhante com o mais velho .arquivos xls, você precisaria do driver XLS , construído contra a Biblioteca FreeXL, o que não é realmente comum (por exemplo, não do OSGeo4w).