Jump to content
Sign in to follow this  
autith3Lp

SQL Query To CSV File

Recommended Posts

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!

Share this post


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

Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


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


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


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

Share this post


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

Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


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

Share this post


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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


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

Share this post


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

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


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

Share this post


Link to post
Share on other sites
jchd

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


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
hannes08

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


Regards,Hannes[spoiler]If you can't convince them, confuse them![/spoiler]

Share this post


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

Share this post


Link to post
Share on other sites
kylomas

autith3lp,

Help file again, "_filewritefromarray()"

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
autith3Lp

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

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  

×