Se i dati sono sempre in viaggio, Excel è come Grand Central Station. Immagina che i dati sono un treno pieno di passeggeri che entra regolarmente in Excel, apporta modifiche e poi parte. Esistono decine di modi per immettere Excel, che importano dati di tutti i tipi e l'elenco continua a crescere. Una volta che i dati sono in Excel, è possibile cambiare forma nel modo desiderato usando Power Query. Anche i dati, come tutti noi, richiedono "cure e alimentazione" per mantenere le cose senza problemi. Ecco dove vengono disponibili le proprietà di connessione, query e dati. Infine, i dati lasciano la stazione ferroviaria di Excel in molti modi: importati da altre origini dati, condivisi come report, grafici e tabelle pivot ed esportati in Power BI e Power Apps.
Ecco le principali operazioni che è possibile eseguire quando i dati si trova nella stazione ferroviaria di Excel:
-
Importare È possibile importare dati da diverse origini dati esterne. Queste origini dati possono essere nel computer, nel cloud o a metà strada in tutto il mondo. Per altre informazioni, vedere Importare dati da origini dati esterne.
-
Power Query È possibile usare Power Query (in precedenza Chiamato & Trasforma) per creare query per modellare, trasformare e combinare i dati in diversi modi. È possibile esportare il lavoro come modello di Power Query per definire un'operazione del flusso di dati in Power Apps. È anche possibile creare un tipo di dati per integrare i tipi di dati collegati. Per altre informazioni, vedere la Guida di Power Query per Excel.
-
Sicurezza La privacy, le credenziali e l'autenticazione dei dati sono sempre un problema costante. Per altre informazioni, vedere Gestire le impostazioni e le autorizzazioni dell'origine dati e Impostare i livelli di privacy.
-
Aggiorna I dati importati in genere richiedono un'operazione di aggiornamento per apportare modifiche, ad esempio aggiunte, aggiornamenti ed eliminazioni, in Excel. Per altre informazioni, vedere Aggiornare una connessione dati esterna in Excel.
-
Connessioni/proprietà A ogni origine dati esterna sono associate informazioni sulla connessione e sulle proprietà che a volte richiedono modifiche a seconda delle circostanze. Per altre informazioni, vedere Gestire intervalli di datiesterni e le relative proprietà , Creare,modificare e gestire connessioni a dati esterni e Proprietà connessione.
-
Legacy I metodi tradizionali, ad esempio le procedure guidate di importazione legacy e MSQuery, sono ancora disponibili per l'uso. Per altre informazioni, vedere Opzioni di importazione e analisi dei dati e Usare Microsoft Query per recuperare dati esterni.
Le sezioni seguenti forniscono maggiori dettagli su cosa succede dietro le quinte in questa trafficata stazione ferroviaria di Excel.
Sono disponibili proprietà di connessione, query e intervallo di dati esterno. Le proprietà di connessione e query contengono entrambe le informazioni di connessione tradizionali. Nel titolo di una finestra di dialogo, Proprietà connessione indica che non è associata alcuna query, ma proprietà query indica che è presente. Le proprietà degli intervalli di dati esterni controllano il layout e il formato dei dati. Tutte le origini dati hanno una finestra di dialogo Proprietà dati esterni, ma le origini dati a cui sono associate credenziali e informazioni di aggiornamento usano la finestra di dialogo Proprietà dati intervallo esterno più grande.
Le informazioni seguenti riepilogano le finestre di dialogo, i riquadri, i percorsi dei comandi e gli argomenti della Guida più importanti.
|
Finestra di dialogo o riquadro |
Schede e tunnel |
Argomento principale della Guida |
|---|---|---|
|
Origini recenti Origini > dati |
(Nessuna scheda) Finestra di dialogo > Navigatore tunnel |
Gestire le impostazioni e le autorizzazioni dell'origine dati |
|
Proprietà connessione
Query > dati & connessioni > connessioni > (fare clic con il pulsante destro del mouse su una connessione) > proprietà |
Scheda Uso
|
|
|
Proprietà query
Dati>
connessioni esistenti > (fare clic con il pulsante destro del mouse su una connessione) > Modifica proprietà connessione |
Scheda Uso
|
|
|
Query & connessioni Query > dati & connessioni |
Scheda Query
|
|
|
Connessioni esistenti Connessioni > dati |
Scheda Connessioni
|
|
|
Proprietà dei dati esterni
|
Usato nella scheda (dalla finestra di dialogo Proprietà connessione) Pulsante Aggiorna a destra per accedere a Proprietà query |
Gestire gli intervalli di dati esterni e le relative proprietà |
|
Proprietà connessione >
scheda Definizione > Esporta file di connessione |
(Nessuna scheda) Finestra di dialogo Tunnel al |
I dati in una cartella di lavoro di Excel possono provengono da due posizioni diverse. I dati possono essere archiviati direttamente nella cartella di lavoro oppure possono essere archiviati in un'origine dati esterna, ad esempio un file di testo, un database o un cubo OLAP (Online Analytical Processing). Questa origine dati esterna è connessa alla cartella di lavoro tramite una connessione dati, ovvero un set di informazioni che descrive come individuare, accedere e accedere all'origine dati esterna.
Il vantaggio principale della connessione a dati esterni è che è possibile analizzare periodicamente questi dati senza copiarli più volte nella cartella di lavoro, un'operazione che può richiedere molto tempo e che può determinare errori. Dopo la connessione a dati esterni, è anche possibile aggiornare automaticamente le cartelle di lavoro di Excel dall'origine dati originale ogni volta che l'origine dati viene aggiornata con nuove informazioni.
Le informazioni di connessione vengono archiviate nella cartella di lavoro e possono anche essere archiviate in un file di connessione, ad esempio un file ODC (Office Data Connection) (con estensione odc) o un file di nome dell'origine dati (con estensione dsn).
Per portare dati esterni in Excel, è necessario accedere ai dati. Se l'origine dati esterna a cui si vuole accedere non si trova nel computer locale, potrebbe essere necessario contattare l'amministratore del database per una password, autorizzazioni utente o altre informazioni di connessione. Se l'origine dati è un database, assicurarsi che il database non sia aperto in modalità esclusiva. Se l'origine dati è un file di testo o un foglio di calcolo, assicurarsi che un altro utente non l'abbia aperta per l'accesso esclusivo.
Molte origini dati richiedono anche un driver ODBC o un provider OLE DB per coordinare il flusso di dati tra Excel, il file di connessione e l'origine dati.
Il diagramma seguente riepiloga i punti chiave delle connessioni dati.
1. Sono disponibili diverse origini dati a cui è possibile connettersi: Analysis Services, SQL Server, Microsoft Access, altri database OLAP e relazionali, fogli di calcolo e file di testo.
2. Molte origini dati hanno un driver ODBC o un provider OLE DB associato.
3. Un file di connessione definisce tutte le informazioni necessarie per accedere e recuperare dati da un'origine dati.
4. Le informazioni di connessione vengono copiate da un file di connessione in una cartella di lavoro e possono essere facilmente modificate.
5. I dati vengono copiati in una cartella di lavoro in modo che sia possibile usarli esattamente come si usano i dati archiviati direttamente nella cartella di lavoro.
Per trovare i file di connessione, usare la finestra di dialogo Connessioni esistenti. Selezionare Dati >connessioni esistenti. Questa finestra di dialogo consente di visualizzare i tipi di connessioni seguenti:
-
Connessioni nella cartella di lavoro
Questo elenco visualizza tutte le connessioni correnti nella cartella di lavoro. L'elenco viene creato dalle connessioni già definite, create usando la finestra di dialogo Seleziona origine dati della Connessione guidata dati o da connessioni selezionate in precedenza come connessione da questa finestra di dialogo.
-
File di connessione nel computer
Questo elenco viene creato dalla cartella Origini dati personali, in genere archiviata nella cartella Documenti.
-
File di connessione in rete
Questo elenco può essere creato da un set di cartelle nella rete locale, la cui posizione può essere distribuita nella rete come parte della distribuzione dei criteri di gruppo di Microsoft Office o da una raccolta di SharePoint.
È anche possibile usare Excel come editor di file di connessione per creare e modificare connessioni a origini dati esterne archiviate in una cartella di lavoro o in un file di connessione. Se non si trova la connessione desiderata, è possibile creare una connessione facendo clic su Cerca altro per visualizzare la finestra di dialogo Seleziona origine dati e quindi facendo clic su Nuova origine per avviare la Connessione guidata dati.
Dopo aver creato la connessione, è possibile usare la finestra di dialogo Proprietà connessione (selezionare Query > dati & Connessioni > Connessioni > (fare clic con il pulsante destro del mouse su una connessione) > Proprietà) per controllare varie impostazioni per le connessioni a origini dati esterne e per usare, riutilizzare o cambiare file di connessione.
Nota In alcuni casi, la finestra di dialogo Proprietà connessione è denominata finestra di dialogo Proprietà query quando è associata una query creata in Power Query (in precedenza Denominata trasforma &).
Se si usa un file di connessione per connettersi a un'origine dati, Excel copia le informazioni di connessione dal file di connessione nella cartella di lavoro di Excel. Quando si apportano modifiche usando la finestra di dialogo Proprietà connessione, si modificano le informazioni sulla connessione dati archiviate nella cartella di lavoro corrente di Excel e non il file di connessione dati originale che potrebbe essere stato usato per creare la connessione, indicato dal nome file visualizzato nella proprietà File di connessione nella scheda Definizione. Dopo aver modificato le informazioni di connessione, ad eccezione delle proprietà Nome connessione e Descrizione connessione, il collegamento al file di connessione viene rimosso e la proprietà File di connessione viene cancellata.
Per assicurarsi che il file di connessione sia sempre usato quando un'origine dati viene aggiornata, fare clic su Tenta sempre di usare questo file per aggiornare i dati nella scheda Definizione. Se si seleziona questa casella di controllo, gli aggiornamenti al file di connessione verranno sempre usati da tutte le cartelle di lavoro che usano tale file di connessione, che devono avere anche questa proprietà impostata.
Usando la finestra di dialogo Connessioni, è possibile gestire facilmente queste connessioni, tra cui la creazione, la modifica e l'eliminazione (Seleziona query > dati & Connessioni > Connessioni > (fare clic con il pulsante destro del mouse su una connessione) > Proprietà). È possibile usare questa finestra di dialogo per eseguire le operazioni seguenti:
-
Creare, modificare, aggiornare ed eliminare le connessioni in uso nella cartella di lavoro.
-
Verificare l'origine dei dati esterni. È possibile eseguire questa operazione nel caso in cui la connessione sia stata definita da un altro utente.
-
Mostra dove viene usata ogni connessione nella cartella di lavoro corrente.
-
Diagnosticare un messaggio di errore sulle connessioni a dati esterni.
-
Reindirizzare una connessione a un server o a un'origine dati diversa oppure sostituire il file di connessione per una connessione esistente.
-
Semplificare la creazione e la condivisione di file di connessione con gli utenti.
I file di connessione sono particolarmente utili per condividere le connessioni in modo coerente, rendendo le connessioni più individuabili, contribuendo a migliorare la sicurezza delle connessioni e facilitando l'amministrazione delle origini dati. Il modo migliore per condividere i file di connessione è di metterli in un percorso sicuro e attendibile, ad esempio una cartella di rete o una raccolta di SharePoint, in cui gli utenti possono leggere il file, ma solo gli utenti designati possono modificare il file. Per altre informazioni, vedere Condividere dati con ODC.
Uso dei file ODC
È possibile creare file ODC (Office Data Connection) (con estensione odc) connettendosi a dati esterni tramite la finestra di dialogo Seleziona origine dati o usando la Connessione guidata dati per connettersi a nuove origini dati. Un file ODC usa tag HTML e XML personalizzati per archiviare le informazioni di connessione. È possibile visualizzare o modificare facilmente il contenuto del file in Excel.
È possibile condividere i file di connessione con altre persone per concedere loro lo stesso accesso a un'origine dati esterna. Gli altri utenti non devono configurare un'origine dati per aprire il file di connessione, ma potrebbero dover installare il driver ODBC o il provider OLE DB necessario per accedere ai dati esterni nel computer.
I file ODC sono il metodo consigliato per la connessione ai dati e la condivisione dei dati. È possibile convertire facilmente altri file di connessione tradizionali (DSN, UDL e file di query) in un file ODC aprendo il file di connessione e facendo clic sul pulsante Esporta file di connessione nella scheda Definizione della finestra di dialogo Proprietà connessione.
Uso dei file di query
I file di query sono file di testo che contengono informazioni sull'origine dati, tra cui il nome del server in cui si trovano i dati e le informazioni di connessione fornite durante la creazione di un'origine dati. I file di query sono un modo tradizionale per condividere query con altri utenti di Excel.
Uso di file di query con estensione dqy È possibile usare Microsoft Query per salvare file con estensione dqy che contengono query per i dati di database relazionali o file di testo. Quando si aprono questi file in Microsoft Query, è possibile visualizzare i dati restituiti dalla query e modificare la query per recuperare risultati diversi. È possibile salvare un file DQY per qualsiasi query creata, usando la Creazione guidata Query o direttamente in Microsoft Query.
Uso dei file di query con estensione oqy È possibile salvare i file OQY per connettersi ai dati di un database OLAP, in un server o in un file cubo offline (con estensione cub). Quando si usa la Connessione guidata multidimensionale in Microsoft Query per creare un'origine dati per un database o un cubo OLAP, viene creato automaticamente un file OQY. Poiché i database OLAP non sono organizzati in record o tabelle, non è possibile creare query o file DQY per accedere a questi database.
Uso dei file di query con estensione rqy Excel può aprire i file di query in formato RQY per supportare i driver delle origini dati OLE DB che usano questo formato. Per altre informazioni, vedere la documentazione del driver.
Uso dei file di query QRY Microsoft Query può aprire e salvare file di query in formato QRY da usare con versioni precedenti di Microsoft Query che non possono aprire file con estensione dqy. Se si ha un file di query in formato QRY che si vuole usare in Excel, aprire il file in Microsoft Query e quindi salvarlo come file DQY. Per informazioni sul salvataggio di file con estensione dqy, vedere la Guida di Microsoft Query.
Uso dei file di query Web iqy Excel può aprire file di query Web con estensione iqy per recuperare dati dal Web. Per altre informazioni, vedere Esportare in Excel da SharePoint.
Un intervallo di dati esterno, detto anche tabella query, è un nome definito o un nome di tabella che definisce la posizione dei dati in un foglio di lavoro. Quando ci si connette a dati esterni, Excel crea automaticamente un intervallo di dati esterno. L'unica eccezione è un rapporto di tabella pivot connesso a un'origine dati, che non crea un intervallo di dati esterno. In Excel è possibile formattare e impostare il formato di un intervallo di dati esterno o usarlo nei calcoli, come per qualsiasi altro dato.
Excel nomina automaticamente un intervallo di dati esterno nel modo seguente:
-
Gli intervalli di dati esterni dei file ODC (Office Data Connection) hanno lo stesso nome del nome del file.
-
Gli intervalli di dati esterni dei database sono denominati con il nome della query. Per impostazioneQuery_from_'origine dati è il nome dell'origine dati usata per creare la query.
-
Gli intervalli di dati esterni dei file di testo sono denominati con il nome del file di testo.
-
Gli intervalli di dati esterni delle query Web sono denominati con il nome della pagina Web da cui sono stati recuperati i dati.
Se il foglio di lavoro contiene più intervalli di dati esterni della stessa origine, gli intervalli vengono numerati. Ad esempio, MyText, MyText_1, MyText_2 e così via.
Un intervallo di dati esterno include proprietà aggiuntive, da non confondere con le proprietà di connessione, che è possibile usare per controllare i dati, ad esempio la conservazione della formattazione delle celle e della larghezza delle colonne. È possibile modificare queste proprietà dell'intervallo di dati esterno facendo clic su Proprietà nel gruppo Connessioni della scheda Dati e apportando le modifiche desiderate nelle finestre di dialogo Proprietà intervallo dati esterno o Proprietà dati esterni.
|
|
|
Sono disponibili diversi oggetti dati, ad esempio un intervallo di dati esterno e un rapporto di tabella pivot, che è possibile usare per connettersi a origini dati diverse. Tuttavia, il tipo di origine dati a cui è possibile connettersi è diverso tra ogni oggetto dati.
È possibile usare e aggiornare i dati connessi in Excel Services. Come per qualsiasi origine dati esterna, potrebbe essere necessario autenticare l'accesso. Per altre informazioni, vedere Aggiornare una connessione dati esterna in Excel. Fo altre informazioni sulle credenziali, vedere Excel Services di autenticazione.
La tabella seguente riepiloga le origini dati supportate per ogni oggetto dati in Excel.
|
Excel |
Crea |
OLE |
ODBC |
Testo |
HTML |
XML |
SharePoint |
|
|
Importazione guidata testo |
Sì |
No |
No |
Sì |
No |
No |
No |
|
|
Rapporto di tabella pivot |
No |
Sì |
Sì |
Sì |
No |
No |
Sì |
|
|
Rapporto di tabella pivot |
No |
Sì |
No |
No |
No |
No |
No |
|
|
Tabella di Excel |
Sì |
Sì |
Sì |
No |
No |
Sì |
Sì |
|
|
Mapping XML |
Sì |
No |
No |
No |
No |
Sì |
No |
|
|
Web Query |
Sì |
No |
No |
No |
Sì |
Sì |
No |
|
|
Connessione guidata dati |
Sì |
Sì |
Sì |
Sì |
Sì |
Sì |
Sì |
|
|
Microsoft Query |
Sì |
No |
Sì |
Sì |
No |
No |
No |
|
Nota: Questi file, un file di testo importato con l'Importazione guidata testo, un file XML importato con un mapping XML e un file HTML o XML importato con una query Web, non usano un driver ODBC o un provider OLE DB per stabilire la connessione all'origine dati.
Excel Services soluzione alternativa per tabelle e intervalli denominati di Excel
Se si vuole visualizzare una cartella di lavoro di Excel in Excel Services, è possibile connettersi e aggiornare i dati, ma è necessario usare un rapporto di tabella pivot. Excel Services non supporta intervalli di dati esterni, pertanto Excel Services non supporta una tabella di Excel connessa a un'origine dati, una query Web, un mapping XML o Microsoft Query.
È tuttavia possibile aggirare questa limitazione usando una tabella pivot per connettersi all'origine dati e quindi progettare e layout la tabella pivot come tabella bidimensionale senza livelli, gruppi o subtotali in modo da visualizzare tutti i valori di riga e colonna desiderati.
Facciamo un viaggio lungo la corsia di memoria del database.
Informazioni su MDAC, OLE DB e OBC
Prima di tutto, le scuse per tutte le sigle. Microsoft Data Access Components (MDAC) 2.8 è incluso in Microsoft Windows. Con MDAC è possibile connettersi e usare i dati di un'ampia gamma di origini dati relazionali e non relazionali. È possibile connettersi a molte origini dati diverse usando driver ODBC (Open Database Connectivity) o provider di OLE DB, creati e forniti da Microsoft o sviluppati da terze parti. Quando si installa Microsoft Office, al computer vengono aggiunti altri driver ODBC e provider OLE DB.
Per visualizzare un elenco completo dei provider OLE DB installati nel computer, visualizzare la finestra di dialogo Proprietà collegamento dati da un file Di collegamento dati e quindi fare clic sulla scheda Provider.
Per visualizzare un elenco completo dei provider ODBC installati nel computer, visualizzare la finestra di dialogo Amministratore database ODBC e quindi fare clic sulla scheda Driver.
È anche possibile usare driver ODBC e provider OLE DB di altri produttori per ottenere informazioni da origini dati diverse da Microsoft, inclusi altri tipi di database ODBC e OLE DB. Per informazioni sull'installazione di questi driver ODBC o provider OLE DB, consultare la documentazione del database o contattare il fornitore del database.
Uso di ODBC per connettersi alle origini dati
Nell'architettura ODBC un'applicazione, ad esempio Excel, si connette a Gestione driver ODBC, che a sua volta usa uno specifico driver ODBC, ad esempio il driver ODBC di Microsoft SQL, per connettersi a un'origine dati, ad esempio un database di Microsoft SQL Server.
Per connettersi alle origini dati ODBC, eseguire le operazioni seguenti:
-
Verificare che il driver ODBC appropriato sia installato nel computer che contiene l'origine dati.
-
Definire un nome di origine dati (DSN) usando l'amministratore origine dati ODBC per archiviare le informazioni di connessione nel Registro di sistema o in un file DSN oppure una stringa di connessione nel codice di Microsoft Visual Basic per passare le informazioni di connessione direttamente a Gestione driver ODBC.
Per definire un'origine dati, in Windows fare clic sul pulsante Start e quindi su Pannello di controllo. Fare clic su Sistema e manutenzionee quindi su Strumenti di amministrazione. Fare clic su Prestazioni e manutenzione,quindi su Strumenti di amministrazione. e quindi fare clic su Origini dati (ODBC). Per altre informazioni sulle diverse opzioni, fare clic sul pulsante ? in ogni finestra di dialogo.
Origini dati del computer
Le origini dati del computer archiviano le informazioni di connessione nel Registro di sistema, in un computer specifico, con un nome definito dall'utente. È possibile usare le origini dati del computer solo nel computer in cui sono definite. Esistono due tipi di origini dati del computer: utente e sistema. Le origini dati utente possono essere usate solo dall'utente corrente e sono visibili solo a tale utente. Le origini dati di sistema possono essere usate da tutti gli utenti di un computer e sono visibili a tutti gli utenti del computer.
Un'origine dati del computer è particolarmente utile quando si vuole offrire una sicurezza avanzata, perché consente di verificare che solo gli utenti connessi possano visualizzare un'origine dati del computer e che un'origine dati del computer non possa essere copiata da un utente remoto in un altro computer.
Origini dati file
Le origini dati file, denominate anche file DSN, archiviano le informazioni di connessione in un file di testo, non nel Registro di sistema, e sono in genere più flessibili da usare rispetto alle origini dati del computer. Ad esempio, è possibile copiare un'origine dati file in qualsiasi computer con il driver ODBC corretto, in modo che l'applicazione possa basarsi su informazioni di connessione coerenti e accurate a tutti i computer che usa. Oppure è possibile inserire l'origine dati del file in un unico server, condividerla tra più computer della rete e gestire facilmente le informazioni di connessione in un'unica posizione.
Anche un'origine dati file può essere non condivisibile. Un'origine dati file non condivisibile si trova in un singolo computer e punta a un'origine dati del computer. È possibile usare origini dati file non condivisibili per accedere alle origini dati del computer esistenti da origini dati file.
Uso di OLE DB per connettersi alle origini dati
Nell'architettura OLE DB l'applicazione che accede ai dati è detta consumer di dati , ad esempio Excel, e il programma che consente l'accesso nativo ai dati è denominato provider di database, ad esempio provider Microsoft OLE DB per SQL Server.
Un file Universal Data Link (con estensione udl) contiene le informazioni di connessione utilizzate da un consumer di dati per accedere a un'origine dati tramite il provider OLE DB dell'origine dati. Per creare le informazioni di connessione, eseguire una delle operazioni seguenti:
-
Nella Connessione guidata dati usare la finestra di dialogo Proprietà collegamento dati per definire un collegamento dati per un provider OLE DB.
-
Creare un file di testo vuoto con estensione udl e quindi modificare il file, che visualizza la finestra di dialogo Proprietà collegamento dati.
