Seal Report Forum
»
General
»
General Discussions/Other
»
Capabilities question - can I paste in a SQL query and get a CSV report out?
Rank: Newbie
Groups: Registered
Joined: 4/21/2015(UTC)
Posts: 0
|
My company is seeking a reporting framework which we can utilize internally. Seal looks compelling on many fronts.
One of the more basic things we occasionally have a need to do is to provide a larger data extract in a CSV format.
I know this is not necessarily the sweet spot of reporting - but I am curious if Seal can accommodate this.
Basically we have the queries written so we want to be able to paste them into Seal, allow the queries to run, and get a clean CSV as the report produced.
Is this within the wheelhouse for Seal?
Thanks,
---EDB
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Normally, you can do that like this:
Create a new report
Expand your Data Source (that defines your connection string)
Then select Tables>MasterTable and cut/paste your SQL in the SQL Statement property
You can then select the Model in the tree view, right click on the Master Category and click the menu [Select All]
Finally remove the view named 'view' to keep the 'view Excel' which generates the CSV, and that's it.
3 remarks:
- there is a bug in the 1.6 and you can not choose the default view for your report (in the Root View), the work around is to remove the default view in your case.
- the column are sorted by alphabetic order, a new option will come soon to keep the order of the select (hopefully in 1.7)
- the 1.7 will offer the Excel Converter to generate native Excel files (with worksheets, format, charts), it will be a commercial component included with the PDF converter
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 4/21/2015(UTC)
Posts: 0
|
The column order is important to us. What is the timing on 1.7? When might you know if this feature would be included?
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Actually we have the budget to do it :-), Concerning the planning, the 1.7 will probably be released in 2-3 weeks. Note that a beta might be available quicker. Edited by user Tuesday, April 21, 2015 6:17:33 PM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Now the 1.7 is released and can do what you need:
The sort of the columns can be based on the table, the column can be named as the one of the table.
Performances have been improved: I manage to extract 1'000'000 rows in 1 minute in a CSV file.
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 4/21/2015(UTC)
Posts: 0
|
I will download 1.7 and take a look. Thanks for prioritizing this highly.
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 4/21/2015(UTC)
Posts: 0
|
Ok - I made it through the steps - and I run it and get an Out of Memory error. Is the memory allocation configurable? Any advice? Execution error when parsing the view 'Model CSV Excel View (Model CSV Excel)': Exception of type 'System.OutOfMemoryException' was thrown. -------------------------------------------------------------------------------- Edited by user Tuesday, May 12, 2015 4:00:44 PM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
not really...
What is the size of your query (how many rows) ?
To get the best performances, you should not set any element in the Column panel of your model, thus not sort is necessary when building the table...
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 4/21/2015(UTC)
Posts: 0
|
500,000 rows of output. I only have the items set as Row Elements.
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
well I think you have reached the limit of your computer... the rows contains probably a lot of text... perhaps you can google to see how to increase the memory allocated to a .net process, but as far as I know, the limit comes mainly from the fact that Seal Report is compiled and released in 32bit and there is a 2Gb limit for memory, we do not had limitations so far, so we did not do the effort for the 64bit compilation. I made my tests on a computer with 16Gb... let us know if you managed to do it. Edited by user Tuesday, May 12, 2015 4:38:55 PM(UTC)
| Reason: Not specified
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 4/21/2015(UTC)
Posts: 0
|
My machine has 4 GB of Ram with 10 GB of virtual memory.
I watched the ram consume from 1.8 GB to 2.89 GB and then I get the Out Of Memory error.
If I shrink down my query to retrieve fewer rows, it does execute successfully. But that is not my need. I need to run some large transaction sets that retrieve several millions of rows.
How would you scale up your application to handle that? Would a 64 bit compilation help? Have you considered any database cursors or paging to retrieve chunks of data from the database? Would this help at all?
Thanks!
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Yes I understand the problem but there is always a limit...
Compiling in 64bit will probably increase the limit, using db cursor may be another option,
we could consider to include this in the next release (but none of our customers need it).
However if you want to transfer millions of rows, I wonder why you do not use a real ETL software (SSIS or other open source ETL)...it will be probably better than SR for huge amount of data.
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 5/15/2015(UTC) Posts: 0  Location: Bogota
|
The request is like using a shotgun to kill an ant !! :)
I just would use: DataCommand, DataReader, File Functions. Easy, Fast, low resource consumption
|
|
|
|
|
|
Rank: Newbie
Groups: Registered
Joined: 4/4/2016(UTC) Posts: 5  Location: Pune
|
Can we Export CSV reports without using paid plugins?
if not.
Can we get the trial copy of Export CSV/PDF plugin?
as we want to be sure before buying the actual one.
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Yes, you should use the Excel view (created by default), this generates a simple CSV file. The converter is to convert to PDF and to generate xlsx with multi-tabs.. Evaluation can be got from www.ariacom.com
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209 Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
|
Update for this post: Just change the format in the main view to CSV and you will generate a CSV file readable by Excel If you want to have full Excel capacities, you should use the commercial component from Ariacom at https://ariacom.com/sealreportlicense.cshtmlThis component allows to generate the Excel Sheet you want in xslx format.
|
|
|
|
|
|
Seal Report Forum
»
General
»
General Discussions/Other
»
Capabilities question - can I paste in a SQL query and get a CSV report out?
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
