Excel

Riepilogo

Prerequisiti

Per connettersi a una cartella di lavoro legacy, ad esempio .xls o xlsb, è necessario il provider OLEDB (o ACE) di Access motore di database. Per installare questo provider, passare alla pagina di download e installare la versione pertinente (32 bit o 64 bit). Se non è installato, verrà visualizzato l'errore seguente durante la connessione alle cartelle di lavoro legacy:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 32-bit (or 64-bit) version of the Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987.

Ace non può essere installato negli ambienti del servizio cloud. Pertanto, se viene visualizzato questo errore in un host cloud (ad esempio Power Query Online), è necessario usare un gateway in cui è installato ACE per connettersi ai file di Excel legacy.

Funzionalità supportate

• Import

Connessione a una cartella di lavoro di Excel da Power Query Desktop

Per stabilire la connessione da Power Query Desktop:

1. Selezionare Cartella di lavoro di Excel nell'esperienza di recupero dei dati. L'esperienza di recupero dei dati in Power Query Desktop varia a seconda delle app. Per altre informazioni sull'esperienza dati di Power Query Desktop per l'app, vedere Dove ottenere i dati.

2. Cercare e selezionare la cartella di lavoro di Excel da caricare. Quindi selezionare Apri.

   

   Se la cartella di lavoro di Excel è online, utilizzare il connettore Web per connettersi alla cartella di lavoro.

3. In Strumento di navigazione selezionare le informazioni della cartella di lavoro desiderate, quindi selezionare Carica per caricare i dati o Trasforma dati per continuare a trasformare i dati in editor di Power Query.

   

Connessione a una cartella di lavoro di Excel da Power Query Online

Per stabilire la connessione da Power Query Online:

1. Selezionare l'opzione cartella di lavoro di Excel nell'esperienza di recupero dei dati. Diverse app hanno diversi modi per ottenere l'esperienza dei dati in Power Query Online. Per altre informazioni su come accedere a Power Query Online, vedere Dove ottenere i dati dall'app.

   

2. Nella finestra di dialogo excel visualizzata specificare il percorso della cartella di lavoro di Excel.

   

3. Se necessario, selezionare un gateway dati locale per accedere alla cartella di lavoro di Excel.

4. Se è la prima volta che si accede a questa cartella di lavoro di Excel, selezionare il tipo di autenticazione e accedere al proprio account (se necessario).

5. In Strumento di navigazione selezionare le informazioni della cartella di lavoro desiderate e quindi Trasformare i dati per continuare a trasformare i dati in editor di Power Query.

   

Tabelle suggerite

Se ci si connette a una cartella di lavoro di Excel che non contiene in modo specifico una singola tabella, lo strumento di navigazione di Power Query tenterà di creare un elenco suggerito di tabelle tra cui è possibile scegliere. Si consideri, ad esempio, l'esempio di cartella di lavoro seguente che contiene dati da A1 a C5, più dati da D8 a E10 e altro da C13 a F16.

Quando ci si connette ai dati in Power Query, lo strumento di navigazione di Power Query crea due elenchi. Il primo elenco contiene l'intero foglio della cartella di lavoro e il secondo elenco contiene tre tabelle suggerite.

Se si seleziona l'intero foglio nello strumento di spostamento, la cartella di lavoro viene visualizzata come appare in Excel, con tutte le celle vuote riempite con null.

Se si seleziona una delle tabelle suggerite, ogni singola tabella che Power Query è stata in grado di determinare dal layout della cartella di lavoro viene visualizzata nello strumento di navigazione. Ad esempio, se si seleziona Tabella 3, vengono visualizzati i dati originariamente visualizzati nelle celle da C13 a F16.

Risoluzione dei problemi

Precisione numerica (o "Perché i miei numeri cambiano?")

Quando si importano dati di Excel, è possibile notare che alcuni valori numerici sembrano cambiare leggermente quando vengono importati in Power Query. Ad esempio, se si seleziona una cella contenente 0,049 in Excel, questo numero viene visualizzato nella barra della formula come 0,049. Tuttavia, se si importa la stessa cella in Power Query e la si seleziona, i dettagli di anteprima lo visualizzano come 0.0490000000000002 (anche se nella tabella di anteprima è formattato come 0.049). Cosa avviene qui?

La risposta è un po' complicata e ha a che fare con il modo in cui Excel archivia i numeri usando una notazione a virgola mobile binaria. La linea inferiore è che ci sono alcuni numeri che Excel non può rappresentare con precisione del 100%. Se si apre il file .xlsx e si esamina il valore effettivo archiviato, si noterà che nel file .xlsx, 0.049 viene effettivamente archiviato come 0.04900000000000000002. Si tratta del valore letto da Power Query dal .xlsx e quindi il valore visualizzato quando si seleziona la cella in Power Query. Per altre informazioni sulla precisione numerica in Power Query, vedere le sezioni "Numero decimale" e "Numero decimale fisso" di Tipi di dati in Power Query.

Connessione a una cartella di lavoro di Excel online

Se si vuole connettersi a un documento di Excel ospitato in SharePoint, è possibile farlo tramite il connettore Web in Power BI Desktop, Excel e Flussi di dati e anche con il connettore Excel nei flussi di dati. Per ottenere il collegamento al file:

1. Aprire il documento in Excel Desktop.
2. Aprire il menu File , selezionare la scheda Info e quindi selezionare Copia percorso.
3. Copiare l'indirizzo nel campo Percorso file o URL e rimuovere ?web=1 dalla fine dell'indirizzo.

Connettore ACE legacy

Power Query legge le cartelle di lavoro legacy, ad esempio .xls o xlsb, usando il provider OLEDB access motore di database (o ACE). Per questo motivo, è possibile riscontrare comportamenti imprevisti durante l'importazione di cartelle di lavoro legacy che non si verificano durante l'importazione di cartelle di lavoro OpenXML, ad esempio .xlsx. Ecco alcuni esempi comuni.

Formattazione di valori imprevisti

A causa di ACE, i valori di una cartella di lavoro legacy di Excel potrebbero essere importati con precisione o fedeltà inferiori a quanto previsto. Si supponga, ad esempio, che il file di Excel contenga il numero 1024.231, formattato per la visualizzazione come "1.024.23". Quando viene importato in Power Query, questo valore viene rappresentato come valore di testo "1.024.23" anziché come numero di fedeltà completa sottostante (1024.231). Questo perché, in questo caso, ACE non espone il valore sottostante a Power Query, ma solo il valore visualizzato in Excel.

Valori Null imprevisti

Quando ACE carica un foglio, esamina le prime otto righe per determinare i tipi di dati delle colonne. Se le prime otto righe non sono rappresentative delle righe successive, ACE può applicare un tipo non corretto a tale colonna e restituire valori Null per qualsiasi valore che non corrisponda al tipo. Ad esempio, se una colonna contiene numeri nelle prime otto righe (ad esempio 1000, 1001 e così via), ma contiene dati non numerici nelle righe successive (ad esempio "100Y" e "100Z"), ACE conclude che la colonna contiene numeri e i valori non numerici vengono restituiti come Null.

Formattazione del valore incoerente

In alcuni casi, ACE restituisce risultati completamente diversi tra gli aggiornamenti. Usando l'esempio descritto nella sezione formattazione, potrebbe essere visualizzato improvvisamente il valore 1024.231 anziché "1.024.23". Questa differenza può essere causata dall'apertura della cartella di lavoro legacy in Excel durante l'importazione in Power Query. Per risolvere il problema, chiudere la cartella di lavoro.

Dati di Excel mancanti o incompleti

A volte Power Query non riesce a estrarre tutti i dati da un foglio di lavoro di Excel. Questo errore è spesso causato dalla presenza di dimensioni non corrette del foglio di lavoro, ad esempio con dimensioni di A1:C200 quando i dati effettivi occupano più di tre colonne o 200 righe.

Come diagnosticare dimensioni non corrette

Per visualizzare le dimensioni di un foglio di lavoro:

1. Rinominare il file xlsx con un'estensione .zip.
2. Aprire il file in Esplora file.
3. Passare a xl\worksheets.
4. Copiare il file XML per il foglio problematico (ad esempio, Sheet1.xml) dal file ZIP in un altro percorso.
5. Esaminare le prime righe del file. Se il file è sufficientemente piccolo, aprirlo in un editor di testo. Se il file è troppo grande da aprire in un editor di testo, eseguire il comando seguente da un prompt dei comandi: più Sheet1.xml.
6. Cercare un <dimension .../> tag , ad esempio <dimension ref="A1:C200" />.

Se il file ha un attributo della dimensione che punta a una singola cella ,ad esempio <dimension ref="A1" />, Power Query usa questo attributo per trovare la riga iniziale e la colonna dei dati nel foglio.

Tuttavia, se il file ha un attributo della dimensione che punta a più celle ,ad esempio <dimension ref="A1:AJ45000"/>, Power Query usa questo intervallo per trovare la riga e la colonna iniziale, nonché la riga e la colonna finale. Se questo intervallo non contiene tutti i dati nel foglio, alcuni dei dati non verranno caricati.

Come correggere le dimensioni non corrette

È possibile risolvere i problemi causati da dimensioni non corrette eseguendo una delle azioni seguenti:

• Aprire e ricampionare il documento in Excel. Questa azione sovrascriverà le dimensioni non corrette archiviate nel file con il valore corretto.

• Verificare che lo strumento che ha generato il file di Excel sia fisso per restituire correttamente le dimensioni.

• Aggiornare la query M per ignorare le dimensioni non corrette. A partire dalla versione di dicembre 2020 di Power Query, Excel.Workbook ora supporta un'opzione InferSheetDimensions . Se true, questa opzione causerà l'ignorare le dimensioni archiviate nella cartella di lavoro e determinarle esaminando i dati.

  Ecco un esempio di come fornire questa opzione:

  Excel.Workbook(File.Contents("C:\MyExcelFile.xlsx"), [DelayTypes = true, InferSheetDimensions = true])

Rallentamento o rallentamento delle prestazioni durante il caricamento dei dati di Excel

Il caricamento lento dei dati di Excel può anche essere causato da dimensioni non corrette. Tuttavia, in questo caso, la lentezza è causata da dimensioni molto più grandi di quelle che devono essere, anziché essere troppo piccole. Dimensioni eccessivamente grandi causeranno la lettura di una quantità di dati molto maggiore da parte di Power Query dalla cartella di lavoro rispetto a quanto effettivamente necessario.

Per risolvere questo problema, è possibile fare riferimento a Individuare e reimpostare l'ultima cella in un foglio di lavoro per istruzioni dettagliate.

Prestazioni scarse durante il caricamento dei dati da SharePoint

Quando si recuperano dati da Excel nel computer o da SharePoint, considerare sia il volume dei dati coinvolti, sia la complessità della cartella di lavoro.

Si noterà una riduzione delle prestazioni durante il recupero di file di grandi dimensioni da SharePoint. Tuttavia, si tratta solo di una parte del problema. Se si dispone di una logica di business significativa in un file di Excel recuperato da SharePoint, questa logica di business potrebbe dover essere eseguita quando si aggiornano i dati, causando calcoli complessi. È consigliabile aggregare e pre-calcolare i dati o spostare più logica di business dal livello Excel e nel livello Power Query.

Errori durante l'uso del connettore Excel per importare file CSV

Anche se i file CSV possono essere aperti in Excel, non sono file di Excel. Usare invece il connettore Text/CSV.

Errore durante l'importazione di cartelle di lavoro "Strict Open XML Spreadsheet"

Durante l'importazione delle cartelle di lavoro salvate nel formato "Strict Open XML Spreadsheet" di Excel potrebbe essere visualizzato l'errore seguente:

DataFormat.Error: The specified package is invalid. The main part is missing.

Questo errore si verifica quando il driver ACE non è installato nel computer host. Le cartelle di lavoro salvate nel formato "Strict Open XML Spreadsheet" possono essere lette solo da ACE. Tuttavia, poiché tali cartelle di lavoro usano la stessa estensione di file delle normali cartelle di lavoro Open XML (.xlsx), non è possibile usare l'estensione per visualizzare il normale the Access Database Engine OLEDB provider may be required to read this type of file messaggio di errore.

Per risolvere l'errore, installare il driver ACE. Se si verifica l'errore in un servizio cloud, è necessario usare un gateway in esecuzione in un computer in cui è installato il driver ACE.

Errori "File contenente dati danneggiati"

È possibile che venga visualizzato l'errore seguente durante l'importazione di determinate cartelle di lavoro di Excel.

DataFormat.Error: File contains corrupted data.

In genere questo errore indica che si è verificato un problema con il formato del file.

Tuttavia, a volte questo errore può verificarsi quando un file sembra essere un file Open XML (ad esempio .xlsx), ma il driver ACE è effettivamente necessario per elaborare il file. Per altre informazioni su come elaborare i file che richiedono il driver ACE, vedere la sezione Connettore ACE legacy.

Problemi noti e limitazioni

• Power Query Online non è in grado di accedere ai file di Excel crittografati. Poiché i file di Excel etichettati con tipi di riservatezza diversi da "Public" o "Non-Business" sono crittografati, non sono accessibili tramite Power Query Online.
• Power Query Online non supporta i file di Excel protetti da password.