SQL Query To CSV File

[autith3Lp]

Does anyone know if its possible or have an example of autoit executing a sql query and sending the results to a csv file? I am new when it comes to using autoit and any help would be greatly appreciated.

Thanks!

[hannes08]

Hi,

for those jobs I use the _SQL UDF by ChrisL, just search the forum.

I will post an example later.

#include
$server = "yoursqlserver"
$db = "yourdb"
$user = "leave empty for win authentication"
$pass = "leave empty for win authentication"

$oADODB = _SQL_Startup()

If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
If _sql_Connect(-1, $server, $db, $user, $pass) = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
    Exit 1
EndIf

$sql = "SELECT * FROM tabelxy"

If _SQL_GetTable2D($oADODB, $sql, $aResult, $iRows, $iColumns) = $SQL_ERROR Then
    MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())
    Exit 1
EndIf

You can now just write your csv from the $aResult 2-Dimensional array.

Edited January 28, 2013 by hannes08

[jchd]

Why not use the standard SQLite.au3 UDF that comes with any AutoIt setup instead?

[hannes08]

Why not use the standard SQLite.au3 UDF that comes with any AutoIt setup instead?

The OP doesn't state what kind of sql DB he uses - would be interesting to know. I just assumed he uses a MS SQL (Express) server...

[autith3Lp]

I am using SQL 2005 Standard and have been spinning my wheels on this for about a week now. My select query pulls data from multiple tables so I am not sure if that factors into anything inside autoit.

[hannes08]

So, did you try the

As a result you get a 2D Array (zero based) and two variables that give you the amount of columns and rows.

Now it's a simple fact of looping...

Example:

For $i = 0 To $iRows
For $j = 0 To $iColumns
MsgBox(0,"Row, Col", $i & ", " & $j)
Next
Next

Oh and by the way there is no problem using this in queries with multiple tables!

Edited January 28, 2013 by hannes08

[autith3Lp]

I've modified the UDF to include my select statement and correct db info. Should i be able to test it or do I need to figure out the output to csv file portion? Right now I get a cannot parse #include

[kylomas]

autith3Lp,

You should not need to modify that UDF at all. And what does this mean?

Right now I get a cannot parse #include

The best thing that you can do right now is post some code so the guessing stops.

kylomas

[autith3Lp]

I figured out where i went wrong with the above error. Here is what I have so far:

[autoit]

##include <_sql.au3>

$server = "*****"

$db = "*****"

$user = "*****"

$pass = "*****"

$oADODB = _SQL_Startup()

If $oADODB = $SQL_ERROR Then MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())

If _sql_Connect(-1, $server, $db, $user, $pass) = $SQL_ERROR Then

MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())

Exit 1

EndIf

$sql = "SELECT cust.account_nbr as ACCOUNT_NUM,

phone_nbr.phone_nbr as PRIMARY_PHONE_NU,

address.address as ADDRESS,

city.city_name as CITY,

state_prov.abbrev as STATE,

address.postal_code as ZIP,

cust.name as CUSTOMER_NAME,

cust.ytd_sale_count as NUM_OF_ORDERS,

cust.ytd_sale_total as YTD_PURCH,

cust.creation_date as DATE_OPENED

FROM cust

LEFT JOIN address ON address.indv_org_id = cust.id and address.primary_ind = 1 and address.indv_org_typ_cd = 'u'

LEFT JOIN city ON city.id = address.city_id

LEFT JOIN state_prov ON state_prov.id = address.state_prov_id

LEFT JOIN phone_nbr on phone_nbr.indv_org_id = cust.id and phone_nbr.primary_ind = 1 and phone_nbr.indv_org_typ_cd = 'u'

WHERE cust.id > 7 order by date_opened asc;"

If _SQL_GetTable2D($oADODB, $sql, $aResult, $iRows, $iColumns) = $SQL_ERROR Then

MsgBox(0 + 16 + 262144, "Error", _SQL_GetErrMsg())

Exit 1

EndIf

[autoit]

[kylomas]

autith3Lp,

Include stmt's have ONE "#" preceeding the directive.

The closing code tag needs a "/" in front of it (inside the brackets).

kylomas

edit: corrected statement

Edited January 28, 2013 by kylomas

[jchd]

My bad for incorrectly targetting SQLite: a hobby of mine!

[autith3Lp]

I am finished with the 1st portion and I am in the process of trying to figure out the GetTable2D portion of it. Is it just a matter of referencing the one UDF and replacing the $vQuery with the $sql that I defined?

Func _SQL_GetTable2D($hConHandle, $vQuery, ByRef $aResult, ByRef $iRows, ByRef $iColumns)

vs

Func _SQL_GetTable2D($hConHandle, $sql, ByRef $aResult, ByRef $iRows, ByRef $iColumns)

[jchd]

Correct, but remove the ByRef keywords: it's only used for function definition, not invokation.

[hannes08]

Maybe the OP should start reading the Tutorial / Helpfile first.

[autith3Lp]

Thanks for the help so far. I totally forgot about the actual help file. Below is the code

$iRval = _SQL_GetTable2d (-1, $sql, $aData, $iRows, $iColumns)

If $iRval = $SQL_OK then _arrayDisplay($aData,"2D (" & $iRows & " Rows) (" & $iColumns & " Columns)" )

Now I get to figure out how to get it to a file

[kylomas]

autith3lp,

Help file again, "_filewritefromarray()"

kylomas

[kylomas]

Or, if you want to do some custom formatting:

local $array[5][9] =    [ _
            [1,2,3,4,5,6,7,8,9], _
            [1,2,3,4,5,6,7,8,9], _
            [1,2,3,4,5,6,7,8,9], _
            [1,2,3,4,5,6,7,8,9] _
            ]

local $str

for $1 = 0 to ubound($array) - 1
    for $2 = 0 to ubound($array,2) - 1
        $str &= stringformat('%-10s',$array[$1][$2])
    Next
    $str &= @CRLF
next

filedelete(@scriptdir & '\test.txt')
filewrite(@scriptdir & '\test.txt',$str)
shellexecute(@scriptdir & '\test.txt')

kylomas

[autith3Lp]

Just wanted to say thank you to everyone that helped. I was able to piece everything together and get it done.