Jump to content
Sign in to follow this  
sadhana

get sql table contents to excel file

Recommended Posts

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)

 

Share this post


Link to post
Share on other sites
water

Does the array look good when you run _ArrayDisplay?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
sadhana

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

ArrayDisplay.png

Share this post


Link to post
Share on other sites
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

#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.

 


When the words fail... music speaks

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Andreik

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


When the words fail... music speaks

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Mercy

@water yes, now it is working. thanks!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×