Sign in to follow this  
Followers 0
skybax

Read from mysql and write to excel problems

22 posts in this topic

Im using mysql.au3 UDF

i have this code and i read from 5 tables (Sala, tehnician, piesa, cantitate,date) and i want to write the result from $Rezultat_raport_2 to a excel file and i dont know how to make a brake between the results

all the data is writen in only one cell

I have tried @LF and @CRLF with no success

Can someone help me?

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance,$SQLCode_raport_2)
With $TableContents_raport_2
     While Not .EOF
     $Rezultat_raport_2 &= .Fields ("Sala").value &" "&.Fields ("tehnician").value &" "&.Fields ("piesa").value &" "&.Fields ("cantitate").value &" "&.Fields ("data").value & @LF
     .MoveNext
     WEnd
EndWith

Share this post


Link to post
Share on other sites



Write the data to an array. Excel has a function to read an array and write the data into cells.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

i know that, but to write the data from a array i still net to brake the string of data

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

Something like this:

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance,$SQLCode_raport_2)
Global $iRecords = mysql_num_rows($TableContents_raport_2)
Global $aResult[$iRecords+1] = [$iRecords]
Global $iIndex = 1
With $TableContents_raport_2
While Not .EOF
     $aResult[$iIndex] = .Fields ("Sala").value & @TAB & .Fields ("tehnician").value & @TAB & .Fields ("piesa").value & @TAB & .Fields ("cantitate").value & @TAB & .Fields ("data").value
     $iIndex = $iIndex + 1
     .MoveNext
WEnd
EndWith

I'm not sure about property "recordcount" set by MySQL for the number of returned records. Maybe it's just called "count" or something like that.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Variable must be of type "Object".:
Global $aResult[$TableContents_raport_2.recordcount+1]
Global $aResult[$TableContents_raport_2^ ERROR

i tried with "count" and i get the same error

Share this post


Link to post
Share on other sites

I altered my example above. According to Google you have to use function mysql_num_rows to get the record count.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

it still doesn't work

mysql_num_rows function not recognized

Share this post


Link to post
Share on other sites

I know nothing about AutoIt's MySQL implementation, so we need another approach.

How many records does the MySQL query return? 100, 1000, 10000, more?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

in php after query need to fetch result. I think here need it too.

I seed in UDF some func named like _fetch_ - try one of it

ps: sorry for my English

Share this post


Link to post
Share on other sites

for now because is in the test faze it return anly 6 but it will return over 100. If you select to Select * Form Sala it will show all those records and it can grow to over 1000 in a year

Share this post


Link to post
Share on other sites

I use this made by cdkid

Share this post


Link to post
Share on other sites

This code should work for up to 1000 records.

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '" & GUICtrlRead($data_de_la_2) & "' AND '" & GUICtrlRead($data_pana_la_2) & "' AND `tehnician` LIKE '" & GUICtrlRead($tehnician_raport_2) & "' AND `Sala` =" & GUICtrlRead($sala_raport_2) & " ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance, $SQLCode_raport_2)
Global $aResult[1001] = [1000]
Global $iIndex = 1
With $TableContents_raport_2
    While Not .EOF
        $aResult[$iIndex] = .Fields("Sala").value & @TAB & .Fields("tehnician").value & @TAB & .Fields("piesa").value & @TAB & .Fields("cantitate").value & @TAB & .Fields("data").value
        $iIndex = $iIndex + 1
        .MoveNext
    WEnd
EndWith
ReDim $aResult[$iIndex]
$aResult[0] = $iIndex - 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

now it works it splits the result but the @TAB doesn't work

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

OK. Then we need a two dimensional array:

$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '" & GUICtrlRead($data_de_la_2) & "' AND '" & GUICtrlRead($data_pana_la_2) & "' AND `tehnician` LIKE '" & GUICtrlRead($tehnician_raport_2) & "' AND `Sala` =" & GUICtrlRead($sala_raport_2) & " ORDER BY `Sala` , `data` ASC"
$TableContents_raport_2 = _Query($SQLInstance, $SQLCode_raport_2)
Global $aResult[1001][5] = [[1000, 5]]
Global $iIndex = 1
With $TableContents_raport_2
While Not .EOF
     $aResult[$iIndex][0] = .Fields("Sala").value
$aResult[$iIndex][1] = .Fields("tehnician").value
$aResult[$iIndex][2] = .Fields("piesa").value
$aResult[$iIndex][3] = .Fields("cantitate").value
$aResult[$iIndex][4] = .Fields("data").value
     $iIndex = $iIndex + 1
     .MoveNext
WEnd
EndWith
ReDim $aResult[$iIndex][5]
$aResult[0][0] = $iIndex - 1

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

ihet a error here

$aResult[0] = $iIndex - 1

^ ERROR

Share this post


Link to post
Share on other sites

Fixed the above example.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

$aResult[0][0] = $iIndex - 1 is no good

C:\Program Files\AutoIt3\Include\Excel.au3 (526) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$oExcel.Activesheet.Cells($iStartRow, ($xx - $iIndexBase) + $iStartColumn).Value = $aArray[$xx]

$oExcel.Activesheet.Cells($iStartRow, ($xx - $iIndexBase) + $iStartColumn).Value = ^ ERROR

Share this post


Link to post
Share on other sites

Can you post your complete script or least the part where you call _ExcelWriteSheetFromArray?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

the whole haves over 4000 lines and its to big to post

Case $arata_raport_2
$contor_2 = 0
If GUICtrlRead($sala_raport_2) >0 Then
$contor_2=$contor_2+1
EndIf
If GUICtrlRead($tehnician_raport_2) >"" Then
$contor_2=$contor_2+10
EndIf
If GUICtrlRead($bifa) =1 Then
$contor_2=$contor_2+100
EndIf

If $contor_2 = 0 Then
$SQLCode_raport_2 =0
EndIf

If $contor_2 = 1 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 10 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 100 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 11 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `Sala` ="&GUICtrlRead($sala_raport_2)&" AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' ORDER BY `data` ASC"
EndIf
If $contor_2 = 101 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 110 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' ORDER BY `Sala` , `data` ASC"
EndIf
If $contor_2 = 111 Then
$SQLCode_raport_2 = "SELECT * FROM raspandire WHERE `data` BETWEEN '"&GUICtrlRead($data_de_la_2)&"' AND '"&GUICtrlRead($data_pana_la_2)&"' AND `tehnician` LIKE '"&GUICtrlRead($tehnician_raport_2)&"' AND `Sala` ="&GUICtrlRead($sala_raport_2)&" ORDER BY `Sala` , `data` ASC"
EndIf

If $contor_2 > 0 Then
$TableContents_raport_2 = _Query($SQLInstance,$SQLCode_raport_2)
Global $aResult[1001][5] = [[1000, 5]]
Global $iIndex = 1
With $TableContents_raport_2
While Not .EOF
;~ $Rezultat_raport_2 &= .Fields ("Sala").value &" "&.Fields ("tehnician").value &" "&.Fields ("piesa").value &" "&.Fields ("cantitate").value &" "&.Fields ("data").value & @LF
$aResult[$iIndex][0] = .Fields("Sala").value
$aResult[$iIndex][1] = .Fields("tehnician").value
$aResult[$iIndex][2] = .Fields("piesa").value
$aResult[$iIndex][3] = .Fields("cantitate").value
$aResult[$iIndex][4] = .Fields("data").value
$iIndex = $iIndex + 1
.MoveNext
WEnd
EndWith
EndIf
ReDim $aResult[$iIndex][5]
$aResult[0][0] = $iIndex - 1
Local $WExcel = _ExcelBookNew()
_ExcelWriteArray($WExcel, 1, 1, $aResult)
Edited by skybax

Share this post


Link to post
Share on other sites

Replace _ExcelWriteArray with

_ExcelWriteSheetFromArray($WExcel, $aResult, 1, 1, 0, 0)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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  
Followers 0