Condividi tramite

Importare dati da Excel o esportare dati in Excel con SQL Server Integration Services (SSIS)

Questo articolo descrive come importare dati da Excel o esportare dati in Excel con SQL Server Integration Services (SSIS). L'articolo descrive anche i prerequisiti, le limitazioni e i problemi noti.

È possibile importare dati da Excel o esportare dati in Excel creando un pacchetto SSIS e utilizzando il Gestore connessione Excel e l'Origine Excel o la Destinazione Excel. È anche possibile usare l'Importazione/Esportazione guidata SQL Server, basata su SSIS.

Questo articolo contiene i tre set di informazioni necessarie per usare Excel correttamente da SSIS o per comprendere e risolvere i problemi comuni:

  1. I file di cui hai bisogno
  2. Le informazioni da fornire quando si caricano dati da o in Excel.
  3. Problemi noti e limitazioni.

Ottenere i file necessari per connettersi a Excel

Prima di poter importare dati da Excel o esportare dati in Excel, potrebbe essere necessario scaricare i componenti di connettività per Excel se non sono già installati. I componenti di connettività per Excel non vengono installati per impostazione predefinita.

Scaricare la versione più recente dei componenti di connettività per Excel qui: Motore di database di Microsoft Access 2016 Redistributable.

La versione più recente dei componenti può aprire i file creati dalle versioni precedenti di Excel.

Assicurarsi di scaricare il motore di database di Access 2016 Redistributable e non di Microsoft Access 2016 Runtime.

Se il computer dispone già di una versione a 32 bit di Office, è necessario installare la versione a 32 bit dei componenti. È anche necessario assicurarsi di eseguire il pacchetto SSIS in modalità a 32 bit o di eseguire la versione a 32 bit dell'Importazione/Esportazione guidata.

Se si ha un abbonamento a Office 365, potrebbe essere visualizzato un messaggio di errore quando si esegue il programma di installazione. L'errore indica che non è possibile installare il download side-by-side con i componenti di Office a portata di clic. Per ignorare questo messaggio di errore, eseguire l'installazione in modalità non interattiva aprendo una finestra del prompt dei comandi ed eseguendo il file .EXE scaricato con l'opzione /quiet . Per esempio:

C:\Users\<user name>\Downloads\AccessDatabaseEngine.exe /quiet

In caso di problemi durante l'installazione del redistributable 2016, installare il redistributable 2010 da questo link: Microsoft Access Database Engine 2010 Redistributable. Non è possibile ridistribuire per Excel 2013.

Specificare Excel

Il primo passaggio consiste nell'indicare che si vuole connettersi a Excel.

In SSIS

In SSIS creare una gestione connessione Excel per connettersi al file di origine o di destinazione di Excel. Esistono diversi modi per creare la gestione connessione:

  • Nell'area Gestioni connessioni fare clic con il pulsante destro del mouse e selezionare Nuova connessione. Nella finestra di dialogo Aggiungi gestione connessione SSIS selezionare EXCEL e quindi Aggiungi.

  • Scegliere Nuova connessione dal menu SSIS. Nella finestra di dialogo Aggiungi gestione connessione SSIS selezionare EXCEL e quindi Aggiungi.

  • Creare il gestore connessione contemporaneamente a quando si configura l'origine Excel o la destinazione Excel nella pagina Gestore connessione dell'Editor origine Excel o dell'Editor destinazione Excel.

Nella procedura guidata di importazione ed esportazione di SQL Server

Nell'Importazione/Esportazione guidata, nella pagina Scegliere un'origine dati o Scegliere una destinazione selezionare Microsoft Excel nell'elenco Origine dati .

Se Excel non è visualizzato nell'elenco delle origini dati, assicurarsi di eseguire la procedura guidata a 32 bit. I componenti di connettività di Excel sono in genere file a 32 bit e non sono visibili nella procedura guidata a 64 bit.

File di Excel e percorso file

La prima parte di informazioni da fornire è il percorso e il nome file per il file di Excel. Queste informazioni vengono fornite nel Excel Connection Manager Editor in un pacchetto SSIS oppure nella pagina Scegli un'origine dati o Scegli una destinazione della procedura guidata di importazione ed esportazione.

Immettere il percorso e il nome del file nel formato seguente:

  • Per un file nel computer locale, C:\TestData.xlsx.

  • Per un file in una condivisione di rete, \\Sales\Data\TestData.xlsx.

In alternativa, fare clic su Sfoglia per individuare il foglio di calcolo usando la finestra di dialogo Apri .

Importante

Non è possibile connettersi a un file di Excel protetto da password.

Versione di Excel

La seconda parte di informazioni da fornire è la versione del file di Excel. Queste informazioni vengono fornite nell'Editor gestione connessione Excel in un pacchetto SSIS oppure nella pagina Scegliere un'origine dati o Scegliere una destinazione dell'Importazione/Esportazione guidata.

Selezionare la versione di Microsoft Excel usata per creare il file o un'altra versione compatibile. Ad esempio, se si verificano problemi durante l'installazione dei componenti di connettività 2016, è possibile installare i componenti 2010 e selezionare Microsoft Excel 2007-2010 in questo elenco.

Potrebbe non essere possibile selezionare versioni più recenti di Excel nell'elenco se sono installate solo versioni precedenti dei componenti di connettività. L'elenco delle versioni di Excel include tutte le versioni di Excel supportate da SSIS. La presenza di elementi in questo elenco non indica che i componenti di connettività necessari sono installati. Ad esempio, Microsoft Excel 2016 viene visualizzato nell'elenco anche se non sono stati installati i componenti di connettività 2016.

La prima riga ha i nomi delle colonne

Se si importano dati da Excel, il passaggio successivo consiste nell'indicare se la prima riga dei dati contiene nomi di colonna. Queste informazioni vengono fornite nell'Editor gestione connessione Excel in un pacchetto SSIS o nella pagina Scegli un'origine dati della procedura guidata di Importazione/Esportazione.

  • Se disabiliti questa opzione perché i dati di origine non contengono nomi di colonna, la procedura guidata utilizzerà F1, F2 e così via come intestazioni di colonna.
  • Se i dati contengono nomi di colonna, ma si disabilita questa opzione, la procedura guidata importa i nomi delle colonne come prima riga di dati.
  • Se i dati non contengono nomi di colonna, ma si abilita questa opzione, la procedura guidata usa la prima riga di dati di origine come nomi di colonna. In questo caso, la prima riga di dati di origine non è più inclusa nei dati stessi.

Se si esportano dati da Excel e si abilita questa opzione, la prima riga di dati esportati include i nomi delle colonne.

Fogli di lavoro e intervalli

Esistono tre tipi di oggetti Excel che è possibile usare come origine o destinazione per i dati: un foglio di lavoro, un intervallo denominato o un intervallo senza nome di celle specificato con il relativo indirizzo.

  • Foglio di lavoro. Per specificare un foglio di lavoro, aggiungere il $ carattere alla fine del nome del foglio e aggiungere delimitatori intorno alla stringa, ad esempio [Sheet1$]. In alternativa, cercare un nome che termina con il $ carattere nell'elenco di tabelle e viste esistenti.

  • Intervallo denominato. Per specificare un intervallo denominato, specificare il nome dell'intervallo, ad esempio MyDataRange. In alternativa, cercare un nome che non termina con il $ carattere nell'elenco di tabelle e viste esistenti.

  • Intervallo senza nome. Per specificare un intervallo di celle non denominato, aggiungere il carattere $ alla fine del nome del foglio, aggiungere la specifica dell'intervallo e aggiungere delimitatori intorno alla stringa, ad esempio [Sheet1$A1:B4].

Per selezionare o specificare il tipo di oggetto Excel che si desidera utilizzare come origine o destinazione per i dati, eseguire una delle operazioni seguenti:

In SSIS

In SSIS, nella pagina Gestione connessionedell'Editor origine Excel o dell'Editor destinazione Excel, eseguire una delle operazioni seguenti:

  • Per usare un foglio di lavoro o un intervallo denominato, selezionare Tabella o vista come modalità di accesso ai dati. Quindi, nell'elenco Nome del foglio di Excel selezionare il foglio di lavoro o l'intervallo denominato.

  • Per usare un intervallo senza nome specificato con il relativo indirizzo, selezionare Comando SQL come modalità di accesso ai dati. Quindi, nel campo di testo del comando SQL immettere una query simile all'esempio seguente:

    SELECT * FROM [Sheet1$A1:B5]

Nella procedura guidata di importazione ed esportazione di SQL Server

Nell'Importazione/Esportazione guidata eseguire una delle operazioni seguenti:

  • Quando si importa da Excel, eseguire una delle operazioni seguenti:

    • Per utilizzare un foglio di lavoro o un intervallo denominato, nella pagina Specificare la copia di tabelle o query selezionare Copia dati da una o più tabelle o viste. Quindi, nella pagina Seleziona tabelle e viste di origine, nella colonna Origine, selezionare i fogli di lavoro e gli intervalli denominati di origine.

    • Per usare un intervallo senza nome specificato con il relativo indirizzo, nella pagina Specificare la copia o la query della tabella selezionare Scrivi una query per specificare i dati da trasferire. Nella pagina Specificare una query di origine specificare quindi una query simile all'esempio seguente:

      SELECT * FROM [Sheet1$A1:B5]

  • Quando si esporta in Excel, eseguire una delle operazioni seguenti:

    • Per utilizzare un foglio di lavoro o un intervallo denominato, nella colonna Destinazione della pagina Seleziona tabelle e viste di origine, selezionare i fogli di lavoro di destinazione e gli intervalli denominati.

    • Per utilizzare un intervallo senza nome specificato con il relativo indirizzo, nella pagina Seleziona tabelle e viste di origine immettere l'intervallo nel formato seguente senza delimitatori nella colonna Destinazione : Sheet1$A1:B5. L'assistente aggiunge i delimitatori.

Dopo aver selezionato o immesso gli oggetti di Excel da importare o esportare, è anche possibile eseguire le operazioni seguenti nella pagina Selezione tabelle e viste di origine della procedura guidata:

  • Esaminare i mapping delle colonne tra origine e destinazione selezionando Modifica mapping.

  • Visualizzare in anteprima i dati di esempio per assicurarsi che siano gli elementi previsti selezionando Anteprima.

Problemi con i tipi di dati

Tipi di dati

Il driver di Excel riconosce solo un set limitato di tipi di dati. Ad esempio, tutte le colonne numeriche vengono interpretate come double (DT_R8) e tutte le colonne stringa (diverse dalle colonne memo) vengono interpretate come stringhe Unicode di 255 caratteri (DT_WSTR). SSIS esegue il mapping dei tipi di dati di Excel come segue:

  • Numerico - virgola mobile a doppia precisione (DT_R8)

  • Valuta - valuta (DT_CY)

  • Boolean - Boolean (DT_BOOL)

  • Data/ora - data e ora (DT_DATE)

  • Stringa - Stringa Unicode, lunghezza 255 caratteri (DT_WSTR)

  • Memo - Flusso di testo Unicode (DT_NTEXT)

Conversioni di tipi di dati e lunghezza

SSIS non converte in modo implicito i tipi di dati. Di conseguenza, potrebbe essere necessario utilizzare le trasformazioni Colonna derivata o Conversione dati per convertire i dati di Excel in modo esplicito prima di caricarli in una destinazione diversa da Excel o per convertire i dati da un'origine diversa da Excel prima di caricarli in una destinazione excel.

Ecco alcuni esempi delle conversioni che possono essere necessarie:

  • Conversione tra colonne stringa di Excel Unicode e colonne stringa non Unicode con tabelle codici specifiche.

  • Conversione tra colonne stringa di Excel a 255 caratteri e colonne stringa di lunghezze diverse.

  • Conversione tra colonne numeriche di Excel a precisione doppia e colonne numeriche di altri tipi.

Suggerimento

Se si usa l'Importazione/Esportazione guidata e i dati richiedono alcune di queste conversioni, la procedura guidata configura automaticamente le conversioni necessarie. Di conseguenza, anche quando si vuole usare un pacchetto SSIS, può essere utile creare il pacchetto iniziale usando l'Importazione/Esportazione guidata. Consenti alla procedura guidata di creare e configurare gestori di connessione, origini, trasformazioni e destinazioni.

Problemi relativi all'importazione

Righe vuote

Quando si specifica un foglio di lavoro o un intervallo denominato come origine, il driver legge il blocco contiguo di celle a partire dalla prima cella non vuota nell'angolo superiore sinistro del foglio di lavoro o dell'intervallo. Di conseguenza, i dati non devono iniziare nella riga 1, ma non è possibile avere righe vuote nei dati di origine. Ad esempio, non è possibile avere una riga vuota tra le intestazioni di colonna e le righe di dati oppure un titolo seguito da righe vuote nella parte superiore del foglio di lavoro.

Se sono presenti righe vuote sopra i dati, non è possibile eseguire query sui dati come foglio di lavoro. In Excel è necessario selezionare l'intervallo di dati e assegnare un nome all'intervallo, quindi eseguire una query sull'intervallo denominato anziché sul foglio di lavoro.

Valori mancanti

Il driver di Excel legge un determinato numero di righe (per impostazione predefinita, otto righe) nell'origine specificata per indovinare il tipo di dati di ogni colonna. Quando una colonna sembra contenere tipi di dati misti, in particolare dati numerici misti con dati di testo, il driver decide a favore del tipo di dati di maggioranza e restituisce valori Null per le celle che contengono dati dell'altro tipo. In un tie il tipo numerico vince. La maggior parte delle opzioni di formattazione delle celle nel foglio di lavoro di Excel non sembra influire sulla determinazione del tipo di dati.

È possibile modificare questo comportamento del driver di Excel specificando modalità di importazione per importare tutti i valori come testo. Per specificare la modalità di importazione, aggiungere IMEX=1 al valore delle Proprietà Estese nella stringa di connessione del gestore di connessione Excel nella finestra Proprietà.

Testo troncato

Quando il driver determina che una colonna di Excel contiene dati di testo, il driver seleziona il tipo di dati (stringa o memo) in base al valore più lungo campionato. Se il driver non rileva valori superiori a 255 caratteri nelle righe campionate, la colonna viene considerata come colonna di stringa di 255 caratteri anziché come colonna memo. Pertanto, i valori più lunghi di 255 caratteri possono essere troncati.

Per importare dati da una colonna memo senza troncamento, sono disponibili due opzioni:

  • Assicurarsi che la colonna memo in almeno una delle righe campionate contenga un valore più lungo di 255 caratteri

  • Aumentare il numero di righe campionate dal pilota per includere tale riga. È possibile aumentare il numero di righe campionate aumentando il valore di TypeGuessRows sotto la seguente chiave del Registro di sistema:

Versione dei componenti ridistribuibili Chiave di Registro
Excel 2016 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines\Excel
Excel 2010 HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

Problemi relativi all'esportazione

Creare un nuovo file di destinazione

In SSIS

Creare una gestione connessione Excel con il percorso e il nome file del nuovo file di Excel che si desidera creare. Quindi, nell'Editor destinazione Excel, per Nome del foglio di Excel, selezionare Nuovo per creare il foglio di lavoro di destinazione. A questo punto, SSIS crea il nuovo file di Excel con il foglio di lavoro specificato.

Nell'Importazione/Esportazione guidata SQL Server

Nella pagina Scegliere una destinazione selezionare Sfoglia. Nella finestra di dialogo Apri passare alla cartella in cui si desidera creare il nuovo file di Excel, specificare un nome per il nuovo file e quindi selezionare Apri.

Esportare in un intervallo di grandezza sufficiente

Quando si specifica un intervallo come destinazione, si verifica un errore se l'intervallo contiene meno colonne rispetto ai dati di origine. Tuttavia, se l'intervallo specificato contiene meno righe rispetto ai dati di origine, la procedura guidata continua a scrivere righe senza errori ed estende la definizione dell'intervallo in modo che corrisponda al nuovo numero di righe.

Esportare valori di testo lungo

Prima di poter salvare correttamente le stringhe con più di 255 caratteri in una colonna di Excel, il driver deve riconoscere il tipo di dati della colonna di destinazione come memo e non come stringa.

  • Se una tabella di destinazione esistente contiene già righe di dati, le prime righe campionate dal driver devono contenere almeno un'istanza di un valore maggiore di 255 caratteri nella colonna memo.

  • Se una nuova tabella di destinazione viene creata durante la progettazione del pacchetto o in fase di esecuzione o tramite l'Importazione/Esportazione guidata, l'istruzione CREATE TABLE deve usare LONGTEXT (o uno dei relativi sinonimi) come tipo di dati della colonna memo di destinazione. Nella procedura guidata controllare l'istruzione CREATE TABLE e modificarla, se necessario, facendo clic su Modifica SQL accanto all'opzione Crea tabella di destinazione nella pagina Mappatura delle colonne.

Contenuti correlati

Per altre informazioni sui componenti e sulle procedure descritte in questo articolo, vedere gli articoli seguenti:

Informazioni su SSIS

Gestione connessione Excel
Origine Excel
Destinazione Excel
Scorrere i file e le tabelle di Excel usando un contenitore ciclo Foreach
Utilizzo di file di Excel con l'attività Script

Informazioni sulla procedura guidata di importazione ed esportazione di SQL Server

Connettersi a un'origine dati di Excel
Iniziare con questo semplice esempio dell'Importazione/Esportazione guidata

Importare dati da Excel a SQL Server o al database SQL di Azure

  • Last updated on