Damn SQL

[raptorrvh]

Ok, I am new and also not much of a programmer but i get along with what i know.

Need urgent help with this..PLEASE!!

I am running sql query using the DBQuery command which, as i understand it, returns the result as an array.

I need to write this to excel. Here is where it gets complex....

The data is about 50 columns wide and about 5000 records deep). If i can even write it delimited to a CSV file it will be ok.

Please help!!

[Marcus Tettmar]

This code will connect to a database, perform a SQL query and then loop through the returned array outputting the data to a CSV file:

Code: Select all

//define output CSV path and filename here
Let>out_file=c:\temp\out.csv

//connect to your database
Let>ConStr=YOUR CONNECTION STRING HERE
DBConnect>ConStr,dbH

//perform the SQL query
DBQuery>dbH,SELECT * FROM TABLE,fields,num_recs,num_fields,0

//loop through and output to CSV file
Let>rec=0
Repeat>rec
  Let>rec=rec+1
  Let>field=0
  Let>csv_line=
  Repeat>field
    Let>field=field+1
    Let>this_field=fields_%rec%_%field%
    Let>csv_line=%csv_line%"%this_field%",
  Until>field=num_fields
  WriteLn>out_file,res,csv_line
Until>rec=num_recs

MessageModal>Done, output file is: %out_file%

DBCLose>dbH

Where you see Let>csv_line=%csv_line%.... you could instead use XLSetCell to put the data directly into Excel. You'd first need to use XLOpen at the start of the script, and you'd need some way of keeping track of which row,col to output to.

Hope this gets you started.[/quote]