SQL upload XLS file to a SQL table

[Juvigy]

I am trying to upload a xls file to an empty table. The SQL server is on a network PC - a have full access to it and an ODBC connection that works. The excel files is located on my PC (i stored it on the server as well - still not working). If i do it manually with the import and export wizard from my PC it works. What i am strugling with is this:

$oDb = ObjCreate("ADODB.Connection")
;~ $oDB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111.xls;Extended Properties=Excel 8.0")
$oDB.Open ("sql server")
;~ $query = "SELECT * INTO [ODBC;Driver={SQL Server};Server=testing;Database=1111;].CSAT FROM [Survey$]"
;~ $query = "SELECT * INTO dbo.CSAT FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\111.xls',[Survey$])"
$query = "SELECT * INTO dbo.CSAT FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\111.xls', 'SELECT * FROM [Customers$]')"
$oSQLRS = $oDB.Execute ($query)
$oDb = 0

I am trying to convert this:

The following Visual Basic 6.0 code sample requires that you add a project reference to ADO. This code sample demonstrates how to import Excel data to SQL Server over an ADO connection by using the Jet 4.0 Provider.

    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

Or this:

expandcollapse popup

Use ADO and SQLOLEDB
When you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same "distributed query" syntax from the Using Distributed Queries section to import Excel data into SQL Server.

The following Visual Basic 6.0 code sample requires that you add a project reference to ActiveX Data Objects (ADO). This code sample also demonstrates how to use OPENDATASOURCE and OPENROWSET over an SQLOLEDB connection.

    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

I failed. Has anyone ever done something like this?

[Juvigy]

i partally managed to make it work using

$oDB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111.xls;Extended Properties=Excel 8.0")
$query = "SELECT * INTO [ODBC;DSN=server].CSAT FROM [Survey$]"
$oSQLRS = $oDB.Execute ($query)

Problem is that if one of the cells contain larger string or formatting it fails. Anyway i can fix that ?

[Juvigy]

Can anyone help here?

[agreiner]

This seems like more of an SQL issue than an AutoIT issue...

There are several ways to fix this.

1.  Change your select to trim the value in the cell to a length which will fit in your DB column (data is lost).

2.  Change your DB columns to text, instead of fixed length.  (no data loss, but you lose indexing on the column).

[Juvigy]

It is an excel connection string issue. The solution is this:

''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' &$filename&';Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1;'"

Instead of this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\111.xls;Extended Properties=Excel 8.0"