Forum

Kognitio Console issues, advice, etc.
Multiple Poster
Offline
User avatar
Posts: 2
Joined: Tue Jul 29, 2014 10:49 am

Code to export into Excel

by rwhawell » Tue Jul 29, 2014 10:59 am

Hi All,

I'd like to write a piece of code to export a view image into excel - is this possible?

Any advice you can give would be greatly appreicated.

Look forward to hearing from you,

Many thanks,
Rebecca
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Code to export into Excel

by markc » Wed Jul 30, 2014 10:25 am

Rebecca,

If you want to export the contents of a view image called V1 to Excel using Console, you can do SELECT * FROM V1, then right-click on a row in the result set and select "Results to Excel".

Ensure you don't restrict the number of result rows to less than the number of rows in the view image when you do this, or you will get curtailed results.

Also, if you need the results in a particular order, you will need to add the appropriate ORDER BY clause to your SELECT query.

This won't be particularly efficient for very large results sets - e.g. I've used Console with a 1 million row view image, and formatting those rows in Console was not very quick, but transferring them to Excel was slower still. For that sort of operation you might even be better exporting to CSV from within Console by right-clicking on the view in the Systems pane and selecting "Unload...", then opening the resulting CSV file in Excel. This won't order the results though, so you would need to do that in Excel.
Reply with quote Top
Multiple Poster
Offline
User avatar
Posts: 2
Joined: Tue Jul 29, 2014 10:49 am

Re: Code to export into Excel

by rwhawell » Wed Jul 30, 2014 10:52 am

Hi Mark,

Many thanks for the reply, I'm able to export data from Kognitio into excel using the send results to excel function. I was hoping there was a way that I could right this in a code so that when I run the code there is no need to go back into the console as the files will have been exported and saved into a location. This is possible in SAS so I was just wondering whether it's possible in the console?

Thanks,
Rebecca
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Code to export into Excel

by markc » Wed Jul 30, 2014 11:37 am

Rebecca,

You should be able to do this directly from Excel without having to do anything with Console, as Excel supports retrieval of ODBC data sets.

From the Data tab in Excel, select From Other Sources, then From Microsoft Query, pick a DSN, specify connection details, and you will see a list of objects. Pick what you want (e.g. your view image), add any predicates you like, add sorting order, Save Query if you want to reuse, then run the query to populate Excel.

If you need to repeat, you can use Data | Existing Connections, and use the query saved above - you should be prompted for your password again by default, and then the data will be retrieved.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron