Hoe een SQL-query uitvoert op een Excel-tabel?

Ik probeer een subtabel te maken van een andere tabel met alle achternaam Velden gesorteerd A-Z met een veld met een telefoonnummer dat niet null is. Ik zou dit vrij eenvoudig kunnen doen met SQL, maar ik heb geen idee hoe ik een SQL-query kan maken binnen Excel. Ik ben verleid om de gegevens in PostgreSQL te importeren en vraag het gewoon daar, maar dat lijkt een beetje overdreven.

Voor wat ik probeer te doen, de SQL-query SELECT lastname, firstname, phonenumber WHERE phonenumber IS NOT NULL ORDER BY lastnamezou de truc doen. Het lijkt er te eenvoudig om iets te zijn dat Excel niet native kan doen. Hoe kan ik een SQL-query uitvoeren zoals dit vanuit Excel?


Antwoord 1, Autoriteit 100%

Er zijn veel mooie manieren om dit gedaan te krijgen, welke anderen al hebben gesuggereerd. Volgens de “Get Excel-gegevens via SQL Track” zijn hier enkele wijzers.

  1. Excel heeft de “Data Connection Wizard” waarmee u kunt importeren of koppelen van een andere gegevensbron of zelfs binnen hetzelfde Excel-bestand.

  2. Als onderdeel van Microsoft Office (en OS’s) zijn twee interessante aanbieders: de oude “Microsoft.jet.oledb” en de nieuwste “Microsoft.ace.oledb”. Zoek ze op bij het instellen van een verbinding (zoals met de wizard voor gegevensverbinding).

  3. Eenmaal verbonden met een Excel-werkmap, is een werkblad of bereik het equivalent van een tabel of weergave. De tabelnaam van een werkblad is de naam van het werkblad met een dollarteken (“$”) toegevoegd aan het en omringd met vierkante haakjes (“[” en “]”); Van een bereik is het gewoon de naam van het bereik. Om een ​​niet-benoemde cellen te specificeren als uw recordsource, voegt u standaard Excel Row / Column Notatie toe aan het einde van de velnaam in de vierkante haakjes.

  4. De native SQL zal (min of meer) de SQL van Microsoft Access zijn. (In het verleden heette het JET SQL, maar Access SQL is geëvolueerd en ik geloof dat JET verouderde technologie is.)

  5. Voorbeeld, een werkblad lezen: SELECT * FROM [Sheet1$]

  6. Voorbeeld, een bereik lezen: SELECT * FROM MyRange

  7. Voorbeeld, het lezen van een naamloos celbereik: SELECT * FROM [Sheet1$A1:B10]

  8. Er zijn vele vele boeken en websites beschikbaar om u te helpen bij het doornemen van de details.

Verdere opmerkingen

Standaard wordt aangenomen dat de eerste rij van uw Excel-gegevensbron kolomkoppen bevat die als veldnamen kunnen worden gebruikt. Als dit niet het geval is, moet u deze instelling uitschakelen, anders “verdwijnt” uw eerste rij gegevens om als veldnamen te worden gebruikt. Dit wordt gedaan door de optionele HDR= settingtoe te voegen aan de uitgebreide eigenschappen van de verbindingsreeks. De standaard, die niet gespecificeerd hoeft te worden, is HDR=Yes. Als u geen kolomkoppen heeft, moet u HDR=Nospecificeren; de provider noemt je velden F1, F2, etc.

Een waarschuwing bij het specificeren van werkbladen: de provider gaat ervan uit dat uw gegevenstabel begint met de bovenste, meest linkse, niet-lege cel op het opgegeven werkblad. Met andere woorden, uw gegevenstabel kan probleemloos in rij 3, kolom C beginnen. U kunt echter geen werkbladtitel typen boven en links van de gegevens in cel A1.

Een waarschuwing bij het opgeven van bereiken: wanneer u een werkblad opgeeft als uw recordbron, voegt de provider nieuwe records toe onder bestaande records in het werkblad, voor zover de ruimte dit toelaat. Wanneer u een bereik opgeeft (met of zonder naam), voegt Jet ook nieuwe records toe onder de bestaande records in het bereik, voor zover de ruimte dit toelaat. Als u echter een query uitvoert op het oorspronkelijke bereik, bevat de resulterende recordset niet de nieuw toegevoegde records buiten het bereik.

Gegevenstypen (het proberen waard) voor CREATE TABLE: Short, Long, Single, Double, Currency, DateTime, Bit, Byte, GUID, BigBinary, LongBinary, VarBinary, LongText, VarChar, Decimal.

Verbinding maken met “old tech” Excel (bestanden met de xls-extensie): Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyFolder\MyWorkbook.xls;Extended Properties=Excel 8.0;. Gebruik het Excel 5.0-brondatabasetype voor Microsoft Excel 5.0 en 7.0 (95) werkmappen en gebruik het Excel 8.0-brondatabasetype voor Microsoft Excel 8.0 (97), 9.0 (2000) en 10.0 (2002) werkmappen.

Verbinding maken met “nieuwste” Excel (bestanden met de xlsx-bestandsextensie): Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;"

Gegevens als tekst behandelen: de IMEX-instelling behandelt alle gegevens als tekst. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Excel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

(Meer details op http://www.connectionstrings.com/excel)

Meer informatie op http://msdn .microsoft.com/en-US/library/ms141683(v=sql.90).aspx, en op http://support.microsoft.com/kb/316934

Verbinding maken met Excel via ADODB via VBA gedetailleerd op http://support.microsoft.com/kb/ 257819

Microsoft JET 4-details op http://support.microsoft.com/kb/275561


Antwoord 2, autoriteit 11%

tl;dr; Excel doet dit allemaal native – gebruik filtersen of tabellen

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

Je kunt Excel programmatisch openen via een oledb-verbinding en SQL uitvoeren op de tabellen in het werkblad.

Maar u kunt alles doen wat u vraagt, zonder formules, alleen filters.

  1. klik ergens binnen de gegevensdie u bekijkt
  2. ga naar gegevens op de lintbalk
  3. selecteer “Filter”het is ongeveer in het midden en ziet eruit als een trechter
    • je hebt nu pijlen aan de krappe kant van elke cel in de eerste rij van je tabel
  4. klik op de pijl op telefoonnummer en deselecteer spaties(laatste optie)
  5. klik op de pijl op achternaam en selecteer a-z bestellen(bovenste optie)

een beetje spelen.. een paar dingen om op te letten:

  1. je kunt de gefilterde rijen selecteren en ze ergens anders plakken
  2. in de statusbalk aan de linkerkant ziet u hoeveel rijen voldoen aan uw filtercriteria uit het totale aantal rijen. (bijv. 308 van 313 gevonden records)
  3. u kunt op kleur filteren in Excel 2010 op afdelingen
  4. Soms maak ik berekende kolommen die statussen of opgeschoonde versies van gegevens geven, die u vervolgens kunt filteren of sorteren op stellingen. (bijv. zoals de formules in de andere antwoorden)

Doe het met filters, tenzij je het veel gaat doen of je het importeren van gegevens ergens of zo wilt automatiseren.. maar voor de volledigheid:

Een c#-optie:

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();

een handige plek om te beginnen is door naar het schema te kijken, want er kan meer zijn dan je denkt:

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());
    }
}

dan wanneer u een blad wilt opvragen:

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

OPMERKING – Gebruik tabellen in Excel!:

Excel heeft “tabellen”-functionaliteit die ervoor zorgen dat gegevens zich meer als een tabel gedragen.

http://office.microsoft.com/en -nl/excel-help/overzicht-van-excel-tabellen-HA010048546.aspx

Voor tabelgegevens in Excel is dit mijn standaard. Het eerste wat ik doe is in de gegevens klikken en vervolgens “format als tabel” selecteren in het hoofdgedeelte op het lint. dit geeft u standaard filtering en sortering en geeft u toegang tot de tabel en velden op naam (bijv. tabel[veldnaam] ) dit maakt ook aggregatiefuncties op kolommen mogelijk, bijv. max en gemiddeld


Antwoord 3, autoriteit 10%

Je kunt dit als volgt native doen:

  1. Selecteer de tabel en gebruik Excel om deze te sorteren op Achternaam
  2. Maak een geavanceerd filtercriterium van 2 rijen en 1 kolom, bijvoorbeeld in
    E1 en E2, waarbij E1 leeg is en E2 de formule =C6="". bevat
    waarbij C6 de eerste gegevenscel van de telefoonnummerkolom is.
  3. Selecteer de tabel en gebruik geavanceerd filter, kopieer naar een bereik, gebruik
    het criteriabereik in E1:E2 en specificeer waar u de . wilt kopiëren
    uitvoer naar

Als je dit programmatisch wilt doen, raad ik je aan de Macro Recorder te gebruiken om de bovenstaande stappen op te nemen en de code te bekijken.


Antwoord 4, autoriteit 7%

Mag ik voorstellen om QueryStormeens te proberen – het is een plug-in voor Excel die het heel handig maakt om SQL te gebruiken in Excel.

Het is ook freemium. Als u niet geïnteresseerd bent in automatisch aanvullen, kronkels van fouten, enz., kunt u het gratis gebruiken. Gewoon downloaden en installeren en je hebt SQL-ondersteuning in Excel.

Disclaimer: ik ben de auteur.


Antwoord 5, autoriteit 5%

U kuntSQL gebruiken in Excel. Het is alleen goed verstopt.
Bekijk deze tutorial:

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


Antwoord 6, autoriteit 3%

Als je dit een keer moet doen, volg dan gewoon de beschrijvingen van Charles, maar het is ook mogelijk om dit te doen met Excel-formules en hulpkolommen voor het geval je het filter dynamisch wilt maken.

Laten we aannemen dat uw gegevens op het blad DataSheet staan en beginnen in rij 2 van de volgende kolommen:

  • A: achternaam
  • B: voornaam
  • C: telefoonnummer

Je hebt twee hulpkolommen nodig op dit blad.

  • D2: =if(A2 = "", 1, 0), dit is de filterkolom die overeenkomt met uw waar-voorwaarde
  • E2: =if(D2 <> 1, "", sumifs(D$2:D$1048576, A$2:A$1048576, "<"&A2) + sumifs(D$2:D2, A$2:A2, A2)), dit komt overeen met de bestelling van

Kopieer deze formules zo ver als uw gegevens gaan.

Maak de volgende kolommen op het blad dat uw resultaat moet weergeven.

  • A: Een reeks getallen die begint met 1 in rij 2, dit beperkt het totale aantal rijen dat je kunt krijgen (een soort limiet in vervolg)
  • B2: =match(A2, DataSheet!$E$2:$E$1048576, 0), dit is de rij met de bijbehorende gegevens
  • C2: =iferror(index(DataSheet!A$2:A$1048576, $B2), ""), dit zijn de feitelijke gegevens of leeg als er geen gegevens zijn

Kopieer de formules in B2 en C2 en kopieer kolom C naar D en E.


Antwoord 7

U kunt experimenteren met de native db-driver voor Excel in taal / platform van uw keuze. In Java-wereld kun je het proberen met http://code.google.com/p/sqlsheet/die een JDBC-stuurprogramma biedt voor het rechtstreeks werken met Excel-vellen. Evenzo kunt u bestuurders voor de DB-technologie voor andere platforms krijgen.

Ik kan echter garanderen dat u snel een muur zult raken met het aantal functies Deze wrapperbibliotheken bieden. Betere manier zal zijn om Apache HSSF / POI of een vergelijkbaar niveau van bibliotheek te gebruiken, maar het heeft meer codering nodig.


Antwoord 8

Ik kan me verkeerd begrijpen, maar is dit niet precies wat een draaitafel doet? Heeft u de gegevens in een tabel of gewoon een gefilterde lijst? Als het geen tabel kan maken (CTRL + L) als het is, activeer vervolgens eenvoudig elke cel in de tabel en plaats een draaitafel op een ander blad. Voeg vervolgens de Columns LASTNAME, FIRELNAME, PHONEENUMPER AAN DE RIJS SECTIE. Voeg vervolgens telefoonnummer toe aan het filtergedeelte en filter de nulwaarden. Nu sorteren zoals normaal.


Antwoord 9

Ik raad u aan om een ​​kijkje te nemen op de MySQL Met CSV-opslagmotor Hiermee kunt u in wezen een CSV-bestand (eenvoudig gemaakt van Excel) in de database laden, zodra u dat hebt, kunt u een SQL-opdracht gebruiken die u wilt.

Het is de moeite waard om het te bekijken.


Antwoord 10

Als je GDAL/OGRhebt gecompileerd met de tegen de Expat-bibliotheek, je kunt het XLSX-stuurprogrammagebruiken om .xlsx-bestanden te lezen en SQL-expressies uit te voeren vanaf een opdrachtprompt. Gebruik bijvoorbeeld vanuit een osgeo4w-shell in dezelfde map als de spreadsheet de ogrinfohulpprogramma:

ogrinfo -dialect sqlite -sql "SELECT name, count(*) FROM sheet1 GROUP BY name" Book1.xlsx

voert een SQLite-query uit op sheet1en voert de zoekresultaat in een ongebruikelijke vorm:

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
...

Of voer dezelfde zoekopdracht uit met ogr2ogrom een eenvoudige CSV-bestand:

$ 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
...

Om hetzelfde te doen met oudere .xls-bestanden, hebt u het XLS-stuurprogrammanodig, gebouwd tegen de FreeXL-bibliotheek, die niet echt gebruikelijk is (bijvoorbeeld niet van OSGeo4w).


Antwoord 11

Microsoft Access en LibreOffice Base kunnen een spreadsheet als bron openen en daarop SQL-query’s uitvoeren. Dat zou de gemakkelijkste manier zijn om allerlei soorten query’s uit te voeren en de rommel van het uitvoeren van macro’s of het schrijven van code te vermijden.

Excel heeft ook autofilters en gegevenssortering waarmee u veel eenvoudige zoekopdrachten kunt uitvoeren, zoals uw voorbeeld. Als je hulp nodig hebt met die functies, zou Google een betere bron voor tutorials zijn dan ik.

Other episodes