get sql table contents to excel file

[sadhana]

Iam trying to execute sql select query and getting the output(2d array)  in excel.

I can get the contents only if the table  has 2 columns, if more than 2 then the excel is blank. I can get the output in message box but while trying to write into excel file the array is not copied to the excel. it is blank.it is neither showing any error.

$oADODB = _SQL_Startup()
   If _SQL_Connect(-1,"localhost","database","username","password") = $SQL_ERROR then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg())

 Local $iRows,$iColumns;Variables to store the array data in to and the row count and the column count
    $iRval = _SQL_GetTable2D(-1,"SELECT * FROM db.table1;",$aData,$iRows,$iColumns)
  ; If $iRval = $SQL_OK then _arrayDisplay($aData,"2D  (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

If _SQL_Close() <> $SQL_OK then Msgbox(0 + 16 +262144,"Error",_SQL_GetErrMsg() )

;*****************************write to excel*************************************

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aData, "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

 

[water]

Does the array look good when you run _ArrayDisplay?

[sadhana]

Yes , Array displays all rows and columns. But in excel it is not getting updated. it is blank.

[Andreik]

#include <SQL.au3>
#include <Array.au3>
#include <Excel.au3>

_AutoItObject_Startup()

$oSQL = SQL()
With $oSQL
    .Server = 'localhost'
    .Database = 'Test'
    .TrustedConnection = True
    .Connect()
EndWith

$oQuery = $oSQL.Query('SELECT * FROM Types')
$aData = $oQuery.Data

$oExcel = _Excel_Open(False)
$oBook = _Excel_BookNew($oExcel)
_Excel_RangeWrite($oBook,Default,$aData)
_Excel_BookSaveAs($oBook,@ScriptDir & '\test.xlsx',$xlOpenXMLWorkbook)
_Excel_Close($oExcel)

 

SQL.au3 is my personal OOP UDF for SQL Server

expandcollapse popup

#include-once
#include <AutoItObject.au3>

Global $TypeEnum[40][2] = [['Array',0x2000], ['adBigInt',20], ['Binary',128], ['Boolean',11], ['BSTR',8], ['Chapter',136], ['Char',129], _
   ['Currency',6], ['Date',7], ['DBDate',133], ['DBTime',134], ['DBTimeStamp',135], ['Decimal',14], ['Double',5], ['Empty',0], ['Error',10], ['FileTime',64], _
   ['GUID',72], ['IDispatch',9], ['Integer',3], ['IUnknown',13], ['LongVarBinary',205], ['LongVarChar',201], ['LongVarWChar',203], ['Numeric',131], ['PropVariant',138], _
   ['Single',4], ['SmallInt',2], ['TinyInt',16], ['UnsignedBigInt',21], ['UnsignedInt',19], ['UnsignedSmallInt',18], ['UnsignedTinyInt',17], ['UserDefined',132], _
   ['VarBinary',204], ['VarChar',200], ['Variant',12], ['VarNumeric',139], ['VarWChar',202], ['WChar',130]]

Func SQL()
   Local $oClass = _AutoItObject_Class()
   With $oClass
      .AddMethod("Connect", "__sql_connect")
      .AddMethod("Query" , "__sql_query")
      .AddMethod("Execute" , "__sql_execute")
      .AddProperty("Provider", $ELSCOPE_PUBLIC, 'SQLNCLI11')
      .AddProperty("Server", $ELSCOPE_PUBLIC, Null)
      .AddProperty("Database", $ELSCOPE_PUBLIC, Null)
      .AddProperty("Username", $ELSCOPE_PUBLIC, Null)
      .AddProperty("Password", $ELSCOPE_PUBLIC, Null)
      .AddProperty("TrustedConnection", $ELSCOPE_PUBLIC, False)
      .AddProperty("ADO", $ELSCOPE_PRIVATE, Null)
   EndWith
   Return $oClass.Object
EndFunc

Func Query($oConnection)
   Local $oClass = _AutoItObject_Class()
   With $oClass
      .AddMethod("Query", "__query_execute")
      .AddProperty("Data", $ELSCOPE_READONLY, 0)
      .AddProperty("Struct", $ELSCOPE_READONLY, 0)
      .AddProperty("Columns", $ELSCOPE_READONLY, 0)
      .AddProperty("Rows", $ELSCOPE_READONLY, 0)
      .AddProperty("ADO", $ELSCOPE_PRIVATE, $oConnection)
   EndWith
   Return $oClass.Object
EndFunc

Func __sql_connect($oSelf,$Provider=Null,$Server=Null,$Database=Null,$Username=Null,$Password=Null,$TrustedConnection=Null)
   #forceref $oSelf
   If $Provider <> Null Then $oSelf.Provider = $Provider
   If $Server <> Null Then $oSelf.Server = $Server
   If $Database <> Null Then $oSelf.Database = $Database
   If $Username <> Null Then $oSelf.Username = $Username
   If $Password <> Null Then $oSelf.Password = $Password
   If $TrustedConnection <> Null Then $oSelf.TrustedConnection = $TrustedConnection
   If $oSelf.ADO = Null Then $oSelf.ADO = ObjCreate("ADODB.Connection")
   Local $oConnection = $oSelf.ADO
   $oConnection.CursorLocation = 3
   If $oSelf.TrustedConnection Then
      $oConnection.Open('Provider=' & $oSelf.Provider & ';Server=' & $oSelf.Server & ';Database=' & $oSelf.Database & ';Trusted_Connection=yes;')
   Else
      $oConnection.Open('Provider=' & $oSelf.Provider & ';Server=' & $oSelf.Server & ';Database=' & $oSelf.Database & ';Uid=' & $oSelf.Username & ';Pwd=' & $oSelf.Password)
   EndIf
EndFunc

Func __sql_query($oSelf,$sQuery,$fStruct=False)
   #forceref $oSelf
   Local $oQuery = Query($oSelf.ADO)
   $oQuery.Query($sQuery,$fStruct)
   Return $oQuery
EndFunc

Func __sql_execute($oSelf,$sCommand)
   #forceref $oSelf
   $oSelf.ADO.Execute($sCommand)
EndFunc

Func __query_execute($oSelf,$sQuery,$fStruct=False)
   #forceref $oSelf
   Local $oRecord = $oSelf.ADO.Execute($sQuery)
   Local $oFields = $oRecord.Fields
   $oSelf.Rows = $oRecord.RecordCount
   $oSelf.Columns = $oFields.Count
   If $fStruct Then
      Local $aStruct[$oSelf.Columns][5]
      For $Index = 0 To $oSelf.Columns-1
         $aStruct[$Index][0] = $oFields.Item($Index).Name
         $aStruct[$Index][1] = __adType($oFields.Item($Index).Type)
         $aStruct[$Index][2] = $oFields.Item($Index).DefinedSize
         $aStruct[$Index][3] = $oFields.Item($Index).ActualSize
         $aStruct[$Index][4] = $oFields.Item($Index).Precision
      Next
      $oSelf.Struct = $aStruct
   EndIf
   Local $aData[$oSelf.Rows][$oSelf.Columns]
   While Not $oRecord.EOF
      For $Column = 0 To $oSelf.Columns-1
         $aData[$oRecord.AbsolutePosition-1][$Column] = $oRecord.Fields.Item($Column).Value
      Next
      $oRecord.MoveNext
   WEnd
   $oSelf.Data = $aData
EndFunc

Func __adType($iType)
   Local $sType
   If $iType >= 0x2000 Then
      $sType = 'Array/'
      $iType = BitXOR($iType,0x2000)
   EndIf
   For $Index = 0 To UBound($TypeEnum)
      If $iType = $TypeEnum[$Index][1] Then Return $sType & $TypeEnum[$Index][0]
   Next
EndFunc

and you will also need AutoItObject.au3 included in AutoItObject UDF. But you can also try just the part where array is written in excel file.

 

[sadhana]

i tried only writing the array into excel part, its again a blank excel. I also tried with your sql udf . it is showing the error 

"D:\658610\SQL&Outlook\SQL.au3" (90) : ==> The requested action with this object has failed.:
$aData[$oRecord.AbsolutePosition-1][$Column] = $oRecord.Fields.Item($Column).Value
$aData[$oRecord.AbsolutePosition-1][$Column] = $oRecord.Fields.Item($Column)^ ERROR

[Andreik]

What version of Excel do you have installed? Use the appropriate export type and file extension.

[water]

Could you please set the _Excel_RangeWrite parameter $bForceFunc to True and try again?

[Mercy]

@water yes, now it is working. thanks!