Jump to content

get sql table contents to excel file


sadhana
 Share

Recommended Posts

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)

 

Link to comment
Share on other sites

Does the array look good when you run _ArrayDisplay?

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • 2 weeks later...

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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...