Easily find issues by searching: #<Issue ID>
Example: #1832
Easily find members by searching in: <username>, <first name> and <last name>.
Example: Search smith, will return results smith and adamsmith
It would be very useful to have a preferences setting to specify the directory in which ADS is to place the MS Excel files generated by the "View as spreadsheet" function in the Query Analyzer results panel.
Are you looking for a customizable Folder location? or full File location?
Customizable folder location would be great.
Customizable folder location would be great.
@jenny: Under File-> Options -> General -> History we have an option to "Specify History Directory" - Determines in what directory SQL History will be saved.
Can we have a similar option under File -> Options -> Results -> Grid Results -> Specify View as Spreadsheet Directory" - Determines in what directory the spreadsheet will be generated ?
@jenny: Under File-> Options -> General -> History we have an option to "Specify History Directory" - Determines in what directory SQL History will be saved.
Can we have a similar option under File -> Options -> Results -> Grid Results -> Specify View as Spreadsheet Directory" - Determines in what directory the spreadsheet will be generated ?
We currently generate temporary Excel files for View as Spreadsheet. These temporary files are deleted when ADS is closed.
We can provide an option for the user to specify where to place the Excel files. But the files are still temporary files and will be deleted when ADS is closed. Is this ok?
We currently generate temporary Excel files for View as Spreadsheet. These temporary files are deleted when ADS is closed.
We can provide an option for the user to specify where to place the Excel files. But the files are still temporary files and will be deleted when ADS is closed. Is this ok?
@jenny: Part of the basis for this request was that several times in the past I have made significant changes to that ADS-generated Excel file and then saved it, but forgotten to save it with a different filename and/or in a location more relevant to the work than the temporary file where ADS creates it. Then when I have gone back to try to find my work, I have been unable to (which I now understand may well have been at least in part due to the file actually no longer existing based on ADS cleaning up what it considered a temporary file), resulting in several hours of lost work.
My thinking was that by saving them somewhere I could configure, I would be able to find them again in the future, but as you have noted, there's more to it than just where they are saved. Having them removed when ADS exits would defeat the purpose of my suggestion.
Knowing that these "View as Excel" files are temporary certainly helps reinforce for me the need to force myself to "Save As..." these files somewhere else when I start manipulating them. If ADS is going to remove the original file on exit, I don't really see any benefit to being able to specify where ADS generates them. Having the ability to specify where to put them would likely only be of value if a user could also disable that cleanup-on-exit logic, as well.
@jenny: Part of the basis for this request was that several times in the past I have made significant changes to that ADS-generated Excel file and then saved it, but forgotten to save it with a different filename and/or in a location more relevant to the work than the temporary file where ADS creates it. Then when I have gone back to try to find my work, I have been unable to (which I now understand may well have been at least in part due to the file actually no longer existing based on ADS cleaning up what it considered a temporary file), resulting in several hours of lost work.
My thinking was that by saving them somewhere I could configure, I would be able to find them again in the future, but as you have noted, there's more to it than just where they are saved. Having them removed when ADS exits would defeat the purpose of my suggestion.
Knowing that these "View as Excel" files are temporary certainly helps reinforce for me the need to force myself to "Save As..." these files somewhere else when I start manipulating them. If ADS is going to remove the original file on exit, I don't really see any benefit to being able to specify where ADS generates them. Having the ability to specify where to put them would likely only be of value if a user could also disable that cleanup-on-exit logic, as well.
>>File -> Options -> Results -> Grid Results -> Specify View as Spreadsheet Directory
If no directory is specified (default behavior), ADS will continue to generate the files in a temporary directory and the files will be marked as temporary.
If a directory is specified, ADS will generate the files in the specified directory & the files will not be marked as temporary. Hence, they will not be deleted upon exit.
>>File -> Options -> Results -> Grid Results -> Specify View as Spreadsheet Directory
If no directory is specified (default behavior), ADS will continue to generate the files in a temporary directory and the files will be marked as temporary.
If a directory is specified, ADS will generate the files in the specified directory & the files will not be marked as temporary. Hence, they will not be deleted upon exit.
@Sachin,
The method that implements "View as Spreadsheet" is used by many ADS requests, not just requests triggered from "Grid Results".
(1) I am assuming the feature requested by this issue in only for those ADS requests triggered from "Grid Results".
(2) In "Grid Results" pane, as far as I can see, there are 3 ways to directly request 'view data in spreadsheet':
(2-a) Clicking "View as Spreadsheet" button displayed in toolbar.
(2-b) Selecting "View Results In Spreadsheet Viewer" from popup menu.
(2-c) Selecting "View Cell In Spreadsheet Viewer" from popup menu.
(2-a) and (2-b) go through the same method implementation to generate result, (2-c) uses different implementation to generate result; should (2-c) be modified as well?
(3) In "Grid Results" pane, user can create a "Results Compare" tab by selecting "Results Compare" from popup menu. In "Results Compare" tab, there is also a "View as Spreadsheet" button in toolbar, should the request triggered from this button be modified as well?
Let's discuss above items on Monday before making any code change.
@Sachin,
The method that implements "View as Spreadsheet" is used by many ADS requests, not just requests triggered from "Grid Results".
(1) I am assuming the feature requested by this issue in only for those ADS requests triggered from "Grid Results".
(2) In "Grid Results" pane, as far as I can see, there are 3 ways to directly request 'view data in spreadsheet':
(2-a) Clicking "View as Spreadsheet" button displayed in toolbar.
(2-b) Selecting "View Results In Spreadsheet Viewer" from popup menu.
(2-c) Selecting "View Cell In Spreadsheet Viewer" from popup menu.
(2-a) and (2-b) go through the same method implementation to generate result, (2-c) uses different implementation to generate result; should (2-c) be modified as well?
(3) In "Grid Results" pane, user can create a "Results Compare" tab by selecting "Results Compare" from popup menu. In "Results Compare" tab, there is also a "View as Spreadsheet" button in toolbar, should the request triggered from this button be modified as well?
Let's discuss above items on Monday before making any code change.
(1) I am assuming the feature requested by this issue in only for those ADS requests triggered from "Grid Results".
Yes
(2-a) and (2-b) go through the same method implementation to generate result, (2-c) uses different implementation to generate result; should (2-c) be modified as well?
Only 2-a & 2-b
(3) In "Grid Results" pane, user can create a "Results Compare" tab by selecting "Results Compare" from popup menu. In "Results Compare" tab, there is also a "View as Spreadsheet" button in toolbar, should the request triggered from this button be modified as well?
No.
Also, if the specified directory does not exist when you try to create the xls, then use the temp directory & mark the file as temporary.
As noted by @jenny, when the file is marked as temporary, the name is auto-generated. Since the file will not be marked as temporary, we need to generate a user friendly filename. Proposed algorithm:
1) Use the name specified in the Query Analyzer tab. If any "illegal" characters are present, replace them w/ "_".
2) If pinning is enabled and pinned tab has a custom name, then append " - <custom tab name>" after step 1. Ask @kin-hong in how to determine whether pinned tab has a custom name.
3) In the past, we've had issues where the generated file name is too long for the filesystem -- this might have been for schema compare. Nhi added this code. Lets leverage the same concept to ensure generated file name doesn't exceed max length.
4) If generated filename will overwrite existing file, then append "-1" or "-2" or "-3", ... until we get to a unique filename which doesn't conflict w/ existing filename in specified directory.
5) If for any reason file creation fails in the specified directory, generate file in temp directory.
(1) I am assuming the feature requested by this issue in only for those ADS requests triggered from "Grid Results".
Yes
(2-a) and (2-b) go through the same method implementation to generate result, (2-c) uses different implementation to generate result; should (2-c) be modified as well?
Only 2-a & 2-b
(3) In "Grid Results" pane, user can create a "Results Compare" tab by selecting "Results Compare" from popup menu. In "Results Compare" tab, there is also a "View as Spreadsheet" button in toolbar, should the request triggered from this button be modified as well?
No.
Also, if the specified directory does not exist when you try to create the xls, then use the temp directory & mark the file as temporary.
As noted by @jenny, when the file is marked as temporary, the name is auto-generated. Since the file will not be marked as temporary, we need to generate a user friendly filename. Proposed algorithm:
1) Use the name specified in the Query Analyzer tab. If any "illegal" characters are present, replace them w/ "_".
2) If pinning is enabled and pinned tab has a custom name, then append " - <custom tab name>" after step 1. Ask @kin-hong in how to determine whether pinned tab has a custom name.
3) In the past, we've had issues where the generated file name is too long for the filesystem -- this might have been for schema compare. Nhi added this code. Lets leverage the same concept to ensure generated file name doesn't exceed max length.
4) If generated filename will overwrite existing file, then append "-1" or "-2" or "-3", ... until we get to a unique filename which doesn't conflict w/ existing filename in specified directory.
5) If for any reason file creation fails in the specified directory, generate file in temp directory.
@nhilam:
3) In the past, we've had issues where the generated file name is too long for the filesystem -- this might have been for schema compare. Nhi added this code. Lets leverage the same concept to ensure generated file name doesn't exceed max length.
Can you please tell me where to locate your implementation?
@nhilam:
3) In the past, we've had issues where the generated file name is too long for the filesystem -- this might have been for schema compare. Nhi added this code. Lets leverage the same concept to ensure generated file name doesn't exceed max length.
Can you please tell me where to locate your implementation?
Done: SVN r42955/16.0.0-rc-16
Done: SVN r42955/16.0.0-rc-16
added to the v16 documentation (text and screenshot) for Options here:
added to the v16 documentation (text and screenshot) for Options here:
rc-19
rc-19
Weighing in a little late here-- but instead of using 'View as Spreadsheet' creating the temporary file, can we not just have an additional action 'Save as Spreadsheet' that simply asks for the save location and would have behavior similar to the "Save Results" action (but instead of presenting the dialog presented by Save Results would use defaults set in preferences for options such as "Set text to (null)" and similar?
Weighing in a little late here-- but instead of using 'View as Spreadsheet' creating the temporary file, can we not just have an additional action 'Save as Spreadsheet' that simply asks for the save location and would have behavior similar to the "Save Results" action (but instead of presenting the dialog presented by Save Results would use defaults set in preferences for options such as "Set text to (null)" and similar?
1) Use the name specified in the Query Analyzer tab. If any "illegal" characters are present, replace them w/ "_".
--> As per above statement name should be: <Query Analyzer tab>.xlsx
However, Name of excelsheet is displayed as: <Query Analyzer tab> - <Reultset tab name>.xlsx
If Result Grid is not displayed with a tab, the generated spreadsheet name will be <Query Analyzer tab>.xlsx.
If Result Grid is displayed with a tab, the generated spreadsheet name will be <Query Analyzer tab> - <Reultset tab name>.xlsx.
Following characters are replaced by "_" , they are : *,<,>,:,",?,/,\,|
Other special charecters are displayed as they are.
However if tab name in ADS contains square brackets i.e.[ and ] then excel sheet name displays paranthesis i.e ( and ) respectevely.
"[" and "]" are replaced with "(" and ")" respectively on purpose because square brackets might cause problem on Linux, see this link for more info.
4) If generated filename will overwrite existing file, then append "-1" or "-2" or "-3", ... until we get to a unique filename which doesn't conflict w/ existing filename in specified directory.--> If generated filename overwrites existing file, then "-1" or "-2" or "-3" etc. are appended.
However, tabname have length more than 127 characters and filename overwrites existing file then instead of "-1", "-2" etc. "_1", "_2" are appended.
If the length of generated filename is shorter than L (currently L is set to 150) and duplicated name is detected, then "-N" (dash N) is appended to the generated filename where N >= 1.
If the length of generated filename is longer than L, then the generated filename is truncated at L location and "_N" (underscore N) is appended to the truncated filename if duplicated name found. This is consistent with the naming convention used by item #3 mentioned here.
1) Use the name specified in the Query Analyzer tab. If any "illegal" characters are present, replace them w/ "_".
--> As per above statement name should be: <Query Analyzer tab>.xlsx
However, Name of excelsheet is displayed as: <Query Analyzer tab> - <Reultset tab name>.xlsx
If Result Grid is not displayed with a tab, the generated spreadsheet name will be <Query Analyzer tab>.xlsx.
If Result Grid is displayed with a tab, the generated spreadsheet name will be <Query Analyzer tab> - <Reultset tab name>.xlsx.
Following characters are replaced by "_" , they are : *,<,>,:,",?,/,\,|
Other special charecters are displayed as they are.
However if tab name in ADS contains square brackets i.e.[ and ] then excel sheet name displays paranthesis i.e ( and ) respectevely.
"[" and "]" are replaced with "(" and ")" respectively on purpose because square brackets might cause problem on Linux, see this link for more info.
4) If generated filename will overwrite existing file, then append "-1" or "-2" or "-3", ... until we get to a unique filename which doesn't conflict w/ existing filename in specified directory.--> If generated filename overwrites existing file, then "-1" or "-2" or "-3" etc. are appended.
However, tabname have length more than 127 characters and filename overwrites existing file then instead of "-1", "-2" etc. "_1", "_2" are appended.
If the length of generated filename is shorter than L (currently L is set to 150) and duplicated name is detected, then "-N" (dash N) is appended to the generated filename where N >= 1.
If the length of generated filename is longer than L, then the generated filename is truncated at L location and "_N" (underscore N) is appended to the truncated filename if duplicated name found. This is consistent with the naming convention used by item #3 mentioned here.
This feature is now available with ADS v16.0.0. Any new defect should be logged as a new issue in AC
This feature is now available with ADS v16.0.0. Any new defect should be logged as a new issue in AC
SVN r43741/16.0.4-7
SVN r43742/17.0.0-dev-22
The generated Excel file name now confirms these requirements described here:
1) Use the name specified in the Query Analyzer tab. If any "illegal" characters are present, replace them w/ "_".
2) If pinning is enabled and pinned tab has a custom name, then append " - <custom tab name>" after step 1.
SVN r43741/16.0.4-7
SVN r43742/17.0.0-dev-22
The generated Excel file name now confirms these requirements described here:
1) Use the name specified in the Query Analyzer tab. If any "illegal" characters are present, replace them w/ "_".
2) If pinning is enabled and pinned tab has a custom name, then append " - <custom tab name>" after step 1.
Verified in ADS v16.0.4-7 in Mac and Win 8 and it works fine. File name comes up with Tab name or Custom name if specified. Earlier builds, the SQL was appearing in the file name which was incorrect.
Verified in ADS v16.0.4-7 in Mac and Win 8 and it works fine. File name comes up with Tab name or Custom name if specified. Earlier builds, the SQL was appearing in the file name which was incorrect.
Issue #12492 |
| Closed |
| Fixed |
| Resolved |
Completion |
| No due date |
| Fixed Build ADS 16.0.0-rc-16, ADS 16.0.4-7/17.0.0-dev-22 |
| No time estimate |
2 issue links |
relates to #13153
Issue #13153Message: "java.lang.object@73dbb5dc" is displayed in "Specify View as Spreadsheet Directory" textbox, if we cancle the operation of selecting the destination folder for first time in new ADS instance. |
relates to #13154
Issue #13154Spreadsheet remains saved in "temp" folder evenafter closing current ADS instance. |
Are you looking for a customizable Folder location? or full File location?