Come importare dati da Excel a SQL Server

Riepilogo

In questo articolo viene illustrato come importare dati da fogli di lavoro Microsoft Excel in database di Microsoft SQL Server utilizzando vari metodi.

Descrizione della tecnica

Gli esempi di questo articolo importano i dati di Excel utilizzando:

  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • Server SQL Server collegato
  • Query distribuite di SQL Server
  • ActiveX Data Objects (ADO) e il Provider Microsoft OLE DB per SQL Server
  • ADO e il Provider Microsoft OLE DB per Jet 4.0

Requisiti

Nell'elenco seguente vengono indicati l'hardware consigliato, software, infrastruttura di rete e i service pack necessari:

  • Disponibile l'istanza di Microsoft SQL Server 7.0 o Microsoft SQL Server 2000 o Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 per gli esempi di ADO che utilizzano Visual Basic
Parti di questo articolo presuppongono che si abbia familiarità con i seguenti argomenti:

  • Data Transformation Services
  • I server collegati e le query distribuite
  • Sviluppo di ADO in Visual Basic

Esempi

Importazione e Append

Le istruzioni SQL di esempio che vengono utilizzate in questo articolo vengono illustrate le query Create Table per l'importazione di dati di Excel in una nuova tabella di SQL Server utilizzando SELECT... INTO FROM sintassi. È possibile convertire queste istruzioni in query di Accodamento utilizzando INSERT INTO... SELEZIONA... DALLA sintassi mentre si continuano a fare riferimento gli oggetti di origine e di destinazione, come illustrato in questi esempi di codice.

Utilizzo DTS o SSIS

È possibile utilizzare l'importazione guidata di SQL Server Data Transformation Services (DTS) o di SQL Server importazione / esportazione guidata per importare dati di Excel in tabelle di SQL Server. Quando si esegue la procedura guidata e selezionando le tabelle di origine di Excel, tenere presente che i nomi degli oggetti di Excel che vengono aggiunti con un segno di dollaro ($) rappresentano i fogli di lavoro (ad esempio, Sheet1$) e che i nomi di oggetto normale senza il segno di dollaro rappresentano Excel intervalli denominati.

Utilizzare un Server collegato

Per semplificare la query, è possibile configurare una cartella di lavoro di Excel come server collegato in SQL Server.

Per ulteriori informazioni, fare clic sul numero dell'articolo per visualizzare l'articolo della Microsoft Knowledge Base:

306397 HOWTO: utilizzo Excel con SQL Server collegati e query distribuite
Il codice riportato di seguito i dati importati dal foglio di lavoro del server collegato di Excel "EXCELLINK" in una nuova tabella di SQL Server denominato XLImport1 clienti:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
È anche possibile eseguire la query sull'origine in modalità passthrough utilizzando la funzione OPENQUERY come segue:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,    'SELECT * FROM [Customers$]')

Utilizzare le query distribuite

Se non si desidera configurare una connessione permanente alla cartella di lavoro di Excel come server collegato, è possibile importare i dati per uno scopo specifico utilizzando il OPENDATASOURCE o la funzione OPENROWSET. Esempi di codice importare anche i dati dal foglio di lavoro Excel clienti in nuove tabelle di SQL Server:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Utilizzo di ADO e SQLOLEDB

Quando si è connessi a SQL Server in un'applicazione ADO utilizzando Microsoft OLE DB per SQL Server (SQLOLEDB), è possibile utilizzare la stessa sintassi "query distribuite" nella sezione Utilizzo di query distribuite per importare dati di Excel in SQL Server.

Nell'esempio di codice seguente di Visual Basic 6.0 è necessario aggiungere un riferimento a Microsoft ActiveX Data Objects (ADO). In questo esempio viene inoltre illustrato come utilizzare OPENDATASOURCE e OPENROWSET tramite una connessione di SQLOLEDB.

    Dim cn As ADODB.Connection    Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
"Initial Catalog=<database>;User ID=<user>;Password=<password>"

'Import by using OPENDATASOURCE.
strSQL = "SELECT * INTO XLImport6 FROM " & _
"OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
"'Data Source=C:\test\xltest.xls;" & _
"Extended Properties=Excel 8.0')...[Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

'Import by using OPENROWSET and object name.
strSQL = "SELECT * INTO XLImport7 FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:\test\xltest.xls', " & _
"[Customers$])"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

'Import by using OPENROWSET and SELECT query.
strSQL = "SELECT * INTO XLImport8 FROM " & _
"OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
"'Excel 8.0;Database=C:\test\xltest.xls', " & _
"'SELECT * FROM [Customers$]')"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

Utilizzo di ADO e il Provider Jet

Il codice di esempio nella sezione precedente utilizza ADO con il Provider SQLOLEDB per connettersi alla destinazione dell'importazione in Excel-SQL. È anche possibile utilizzare il Provider OLE DB per Jet 4.0 per connettersi all'origine di Excel.

Il motore di database Jet può fare riferimento a database esterni nelle istruzioni SQL utilizzando una sintassi speciale che dispone di tre diversi formati:

  • [Percorso completo del database di Microsoft Access]. [Nome tabella]
  • [Nome ISAM; Stringa di connessione ISAM]. [Nome tabella]
  • [ODBC; Stringa di connessione ODBC]. [Nome tabella]
In questa sezione utilizza il terzo formato per creare una connessione ODBC al database di SQL Server di destinazione. È possibile utilizzare un nome di origine dati ODBC (DSN) o una stringa di connessione senza DSN:

DSN:    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
[odbc;Driver={SQL Server};Server=<server>;Database=<database>;
UID=<user>;PWD=<password>]

Nell'esempio di codice seguente di Visual Basic 6.0 è necessario aggiungere un riferimento ad ADO. Esempio di codice riportato di seguito viene illustrato come importare dati di Excel a SQL Server tramite una connessione ADO utilizzando il Provider Jet 4.0.

    Dim cn As ADODB.Connection    Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\test\xltestt.xls;" & _
"Extended Properties=Excel 8.0"

'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=<server>;Database=<database>;" & _
"UID=<user>;PWD=<password>].XLImport9 " & _
"FROM [Customers$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff

cn.Close
Set cn = Nothing

È inoltre possibile utilizzare questa sintassi, che supporta il Provider Jet, per importare dati di Excel in altri database di Microsoft Access, accesso sequenziale indicizzato (ISAM) metodo ("desktop") database o database ODBC.

Risoluzione dei problemi

  • Tenere presente che i nomi degli oggetti di Excel che vengono aggiunti con un segno di dollaro ($) rappresentano i fogli di lavoro (ad esempio, Sheet1$) e che i nomi degli oggetti semplici intervalli denominati Excel.
  • In alcune circostanze, soprattutto quando si specificano i dati di origine di Excel utilizzando il nome della tabella anziché una query di selezione, le colonne nella tabella di SQL Server di destinazione sono organizzate in ordine alfabetico. Per ulteriori informazioni su questo problema con il Provider Jet, fare clic sul numero dell'articolo per visualizzare l'articolo della Microsoft Knowledge Base:

    299484 PRB: le colonne vengono ordinate alfabeticamente quando si utilizza ADOX per recuperare le colonne della tabella di Access
  • Quando il Provider Jet determina che una colonna di Excel contiene dati numerici e testo misto, il Provider Jet seleziona il tipo di dati "parte" e restituisce valori non corrispondenti come valori null. Per ulteriori informazioni su come risolvere il problema, fare clic sul numero dell'articolo per visualizzare l'articolo della Microsoft Knowledge Base:

    194124 PRB: valori restituiti come valori NULL utilizzando il metodo DAO OpenRecordset di Excel

Riferimenti

Per ulteriori informazioni sull'utilizzo di Excel come origine dati, fare clic sul numero dell'articolo per visualizzare l'articolo della Microsoft Knowledge Base riportato di seguito:

257819 HOWTO: utilizzare ADO con dati di Excel da Visual Basic o VBA
Per ulteriori informazioni su come trasferire dati in Excel, fare clic sui numeri degli articoli della Microsoft Knowledge Base:

295646 HOWTO: trasferire dati dall'origine dati ADO in Excel con ADO
247412 INFO: metodi per il trasferimento di dati in Excel da Visual Basic
246335 HOWTO: trasferire dati da un Recordset ADO a Excel con l'automazione
319951 HOW TO: trasferire dati in Excel utilizzando SQL Server Data Transformation Services
306125 procedura: importare dati da SQL Server in Microsoft Excel
Proprietà

ID articolo: 321686 - Ultima revisione: 30 gen 2017 - Revisione: 1

Microsoft Excel 2000 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft Excel 2002 Standard Edition, Microsoft Excel 97 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition

Feedback