Discussion:
Need to export data into excel
Add Reply
t***@gmail.com
2018-09-24 05:35:53 UTC
Reply
Permalink
Hi

I have use case, where i need to read the data from DB2 table and export data into excel and send mail with the attachment.

I am using the database connector in Iterator mode, and using the below query in SQL select option but getting the error

"select DISTINCT"+"("+"decrypt_char"+"("+"EMPLOYEE_NAME"+",'"+"AppOps-CMDB'"+")"+"),"+ "APPOPS_ENDDATE from CMDB.person where appops_enddate"+">="+"VARCHAR_FORMAT"+"("+"CURRENT TIMESTAMP"+","+" 'YYYY-MM-DD'"+")"+" OR APPOPS_ENDDATE"+"=''"+" >> "+"test.csv"


Could you please help me to understand.
1. Can I execute this query to export the data into excel sheet using TDI, as I am able to execute the same query on Db2
2. Is there any other method, where I can read the data from Db2 table, write into excel sheet and export into some specified location

Thanks
e***@tdiingoutloud.com
2018-09-25 12:34:56 UTC
Reply
Permalink
Post by t***@gmail.com
Hi
I have use case, where i need to read the data from DB2 table and export data into excel and send mail with the attachment.
I am using the database connector in Iterator mode, and using the below query in SQL select option but getting the error
"select DISTINCT"+"("+"decrypt_char"+"("+"EMPLOYEE_NAME"+",'"+"AppOps-CMDB'"+")"+"),"+ "APPOPS_ENDDATE from CMDB.person where appops_enddate"+">="+"VARCHAR_FORMAT"+"("+"CURRENT TIMESTAMP"+","+" 'YYYY-MM-DD'"+")"+" OR APPOPS_ENDDATE"+"=''"+" >> "+"test.csv"
Could you please help me to understand.
1. Can I execute this query to export the data into excel sheet using TDI, as I am able to execute the same query on Db2
2. Is there any other method, where I can read the data from Db2 table, write into excel sheet and export into some specified location
Thanks
Hi Arif,

We spoke over webex about this and I just wanted to post what we ended up with:

A Database connector in Iterator mode (data pump) and a FileSystem connector with CSV parser to write the file. In the 'Connection' tab of the Database connector you have the connection parameters. This differs for different connector types and may change based on the Mode setting.

Whenever you click on the label for a parameter, or the little pencil button next to each, you get the Parameter dialog. Here you tell TDI how you want this setting provided. For example, from a property in some property file, from a database row, from a command-line parameter when your AL is launched...
You decide. TDI also tells you here what it calls this parameter (like 'jdbcSelect').

Parameter values are handled before initialization so this where the jdbcSelect param is evaluated. So far so good.

But when we finally took a close look at your sql we discovered that it had been copy/pasted from a commandline that re-directed stdout to a csv file. We cut off that and everything worked like a champ.

We also looked at how you can right-click any connector and browse the data in that store - even perform queries interactively. And how to determine the ordering of fields in a csv file (Parser > Advanced > Field Names param - one field name per line)

/Eddie

Loading...