|
|
You're in the
sql server reporting services
group:CSV Export delimited not fixed
sql server reporting services:
default, open the file correctly. Is there a way to export to csv using a format which excel will handle correctly using its default settings? Currently the user must open the file from within Excel and specify "Delimited" not "Fixed Width" and go through the Text Import Wizard. Thanks in advance, Ryan Opfer
Thanks Bruce for the quick reply.
The Excel format is fine provided you go through the Text Import Wizard of Excel and specify that the file is a comma delimited csv file not Fixed Width. I would like to figure out how to Export from RS using a format in which Excel will natively open the file as a comma delimited file and thereby bypass having to have the user go through the Text Import Wizard. There was a post back in November (Jan Bodey) that said she fixed this by specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I could have, I wouldn't expect the user to put this in the link anyway. Does that make more sense? Thanks, Ryan [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:e5%2331j3HFHA.1476@TK2MSFTNGP09.phx.gbl... > Is there a problem with Excel format? > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message > news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... >> Since RS exports csv files using a comma delimiter, Excel doesn't by >> default, open the file correctly. Is there a way to export to csv using >> a >> format which excel will handle correctly using its default settings? >> Currently the user must open the file from within Excel and specify >> "Delimited" not "Fixed Width" and go through the Text Import Wizard. >> >> Thanks in advance, >> >> Ryan Opfer >> >> > >
I just noticed how I worded the subject title. By delimited not fixed, I
ment: default to "Delimited" not "Fixed Width". Sorry for the confusion. Ryan [quoted text, click to view] "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... > Since RS exports csv files using a comma delimiter, Excel doesn't by > default, open the file correctly. Is there a way to export to csv using a > format which excel will handle correctly using its default settings? > Currently the user must open the file from within Excel and specify > "Delimited" not "Fixed Width" and go through the Text Import Wizard. > > Thanks in advance, > > Ryan Opfer >
Is there a problem with Excel format?
-- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... > Since RS exports csv files using a comma delimiter, Excel doesn't by > default, open the file correctly. Is there a way to export to csv using a > format which excel will handle correctly using its default settings? > Currently the user must open the file from within Excel and specify > "Delimited" not "Fixed Width" and go through the Text Import Wizard. > > Thanks in advance, > > Ryan Opfer > >
You can render your report as an Excel file. No reason to use the CSV format
in the first place. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message news:uQS0Du3HFHA.3332@TK2MSFTNGP15.phx.gbl... > Thanks Bruce for the quick reply. > > The Excel format is fine provided you go through the Text Import Wizard of > Excel and specify that the file is a comma delimited csv file not Fixed > Width. I would like to figure out how to Export from RS using a format in > which Excel will natively open the file as a comma delimited file and > thereby bypass having to have the user go through the Text Import Wizard. > > There was a post back in November (Jan Bodey) that said she fixed this by > specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I > could have, I wouldn't expect the user to put this in the link anyway. > > Does that make more sense? > > Thanks, > > Ryan > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:e5%2331j3HFHA.1476@TK2MSFTNGP09.phx.gbl... >> Is there a problem with Excel format? >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >> news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... >>> Since RS exports csv files using a comma delimiter, Excel doesn't by >>> default, open the file correctly. Is there a way to export to csv using >>> a >>> format which excel will handle correctly using its default settings? >>> Currently the user must open the file from within Excel and specify >>> "Delimited" not "Fixed Width" and go through the Text Import Wizard. >>> >>> Thanks in advance, >>> >>> Ryan Opfer >>> >>> >> >> > >
An Excel format is what I tried first but I couldn't get around how Excel
sees the document (it's too smart). Excel sees the document map and puts it on sheet 1 then on sheet 2 (which has the main table data) it shows drill down. Ultimately this file will need to be imported into an MS SQL table so it needs to be simple. Excel would work fine if I can remove the document map (obviously I can do this by just removing the document map from the report...and maybe that's something the user will have to live with) and drill down capabilities (the user can't live without this though), but by then I'd pretty much be looking at a plain csv file anyway. The key here is the user will be doing all this and just sending us back a basic csv or Excel file for the sql import so thereby has to be a simple process. From your reply's, I take it that RS can't defign how it exports to csv or for that matter Excel. Thanks for the help, Ryan [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:%23VOimb4HFHA.3076@tk2msftngp13.phx.gbl... > You can render your report as an Excel file. No reason to use the CSV > format in the first place. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message > news:uQS0Du3HFHA.3332@TK2MSFTNGP15.phx.gbl... >> Thanks Bruce for the quick reply. >> >> The Excel format is fine provided you go through the Text Import Wizard >> of Excel and specify that the file is a comma delimited csv file not >> Fixed Width. I would like to figure out how to Export from RS using a >> format in which Excel will natively open the file as a comma delimited >> file and thereby bypass having to have the user go through the Text >> Import Wizard. >> >> There was a post back in November (Jan Bodey) that said she fixed this by >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I >> could have, I wouldn't expect the user to put this in the link anyway. >> >> Does that make more sense? >> >> Thanks, >> >> Ryan >> >> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message >> news:e5%2331j3HFHA.1476@TK2MSFTNGP09.phx.gbl... >>> Is there a problem with Excel format? >>> >>> -- >>> Bruce Loehle-Conger >>> MVP SQL Server Reporting Services >>> >>> "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >>> news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... >>>> Since RS exports csv files using a comma delimiter, Excel doesn't by >>>> default, open the file correctly. Is there a way to export to csv >>>> using a >>>> format which excel will handle correctly using its default settings? >>>> Currently the user must open the file from within Excel and specify >>>> "Delimited" not "Fixed Width" and go through the Text Import Wizard. >>>> >>>> Thanks in advance, >>>> >>>> Ryan Opfer >>>> >>>> >>> >>> >> >> > >
Thanks Bruce for all the help, I do appreciate it. This sounds like an
adequate solution that I think will work. It does seems strange though (although seemingly common for MS) to have RS export to csv using a format that Excel doesn't natively understand by default. I would have hoped that whatever MS decides to set as a default format for csv would be the same between MS applications. I guess that's just something we should all be use to by now anyway. Ryan Opfer [quoted text, click to view] "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message news:u1VflVAIFHA.3536@TK2MSFTNGP14.phx.gbl... > Here is an idea for you. Have an additional report that is very plain and > exports to Excel well. Have it be the same as the other on as far as the > data is concerned and the report parameters. Then add a link that uses > jump > to URL and have it render the second shadow report as Excel passing it the > parameters from the current report. I call it a shadow report because you > can set it to not be visible in list view so the user never sees it. The > only way it gets run is when they click on the link. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message > news:%23V19fKAIFHA.2784@TK2MSFTNGP09.phx.gbl... >> An Excel format is what I tried first but I couldn't get around how Excel >> sees the document (it's too smart). Excel sees the document map and puts > it >> on sheet 1 then on sheet 2 (which has the main table data) it shows drill >> down. Ultimately this file will need to be imported into an MS SQL table > so >> it needs to be simple. >> >> Excel would work fine if I can remove the document map (obviously I can >> do >> this by just removing the document map from the report...and maybe that's >> something the user will have to live with) and drill down capabilities > (the >> user can't live without this though), but by then I'd pretty much be > looking >> at a plain csv file anyway. >> >> The key here is the user will be doing all this and just sending us back >> a >> basic csv or Excel file for the sql import so thereby has to be a simple >> process. >> >> From your reply's, I take it that RS can't defign how it exports to csv >> or >> for that matter Excel. >> >> Thanks for the help, >> >> Ryan >> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message >> news:%23VOimb4HFHA.3076@tk2msftngp13.phx.gbl... >> > You can render your report as an Excel file. No reason to use the CSV >> > format in the first place. >> > >> > >> > -- >> > Bruce Loehle-Conger >> > MVP SQL Server Reporting Services >> > >> > "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >> > news:uQS0Du3HFHA.3332@TK2MSFTNGP15.phx.gbl... >> >> Thanks Bruce for the quick reply. >> >> >> >> The Excel format is fine provided you go through the Text Import >> >> Wizard >> >> of Excel and specify that the file is a comma delimited csv file not >> >> Fixed Width. I would like to figure out how to Export from RS using a >> >> format in which Excel will natively open the file as a comma delimited >> >> file and thereby bypass having to have the user go through the Text >> >> Import Wizard. >> >> >> >> There was a post back in November (Jan Bodey) that said she fixed this > by >> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if >> >> I >> >> could have, I wouldn't expect the user to put this in the link anyway. >> >> >> >> Does that make more sense? >> >> >> >> Thanks, >> >> >> >> Ryan >> >> >> >> >> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message >> >> news:e5%2331j3HFHA.1476@TK2MSFTNGP09.phx.gbl... >> >>> Is there a problem with Excel format? >> >>> >> >>> -- >> >>> Bruce Loehle-Conger >> >>> MVP SQL Server Reporting Services >> >>> >> >>> "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >> >>> news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... >> >>>> Since RS exports csv files using a comma delimiter, Excel doesn't by >> >>>> default, open the file correctly. Is there a way to export to csv >> >>>> using a >> >>>> format which excel will handle correctly using its default settings? >> >>>> Currently the user must open the file from within Excel and specify >> >>>> "Delimited" not "Fixed Width" and go through the Text Import Wizard. >> >>>> >> >>>> Thanks in advance, >> >>>> >> >>>> Ryan Opfer >> >>>> >> >>>> >> >>> >> >>> >> >> >> >> >> > >> > >> >> > >
Here is an idea for you. Have an additional report that is very plain and
exports to Excel well. Have it be the same as the other on as far as the data is concerned and the report parameters. Then add a link that uses jump to URL and have it render the second shadow report as Excel passing it the parameters from the current report. I call it a shadow report because you can set it to not be visible in list view so the user never sees it. The only way it gets run is when they click on the link. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message news:%23V19fKAIFHA.2784@TK2MSFTNGP09.phx.gbl... > An Excel format is what I tried first but I couldn't get around how Excel > sees the document (it's too smart). Excel sees the document map and puts it > on sheet 1 then on sheet 2 (which has the main table data) it shows drill > down. Ultimately this file will need to be imported into an MS SQL table so > it needs to be simple. > > Excel would work fine if I can remove the document map (obviously I can do > this by just removing the document map from the report...and maybe that's > something the user will have to live with) and drill down capabilities (the > user can't live without this though), but by then I'd pretty much be looking > at a plain csv file anyway. > > The key here is the user will be doing all this and just sending us back a > basic csv or Excel file for the sql import so thereby has to be a simple > process. > > From your reply's, I take it that RS can't defign how it exports to csv or > for that matter Excel. > > Thanks for the help, > > Ryan > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:%23VOimb4HFHA.3076@tk2msftngp13.phx.gbl... > > You can render your report as an Excel file. No reason to use the CSV > > format in the first place. > > > > > > -- > > Bruce Loehle-Conger > > MVP SQL Server Reporting Services > > > > "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message > > news:uQS0Du3HFHA.3332@TK2MSFTNGP15.phx.gbl... > >> Thanks Bruce for the quick reply. > >> > >> The Excel format is fine provided you go through the Text Import Wizard > >> of Excel and specify that the file is a comma delimited csv file not > >> Fixed Width. I would like to figure out how to Export from RS using a > >> format in which Excel will natively open the file as a comma delimited > >> file and thereby bypass having to have the user go through the Text > >> Import Wizard. > >> > >> There was a post back in November (Jan Bodey) that said she fixed this by > >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if I > >> could have, I wouldn't expect the user to put this in the link anyway. > >> > >> Does that make more sense? > >> > >> Thanks, > >> > >> Ryan > >> > >> > >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > >> news:e5%2331j3HFHA.1476@TK2MSFTNGP09.phx.gbl... > >>> Is there a problem with Excel format? > >>> > >>> -- > >>> Bruce Loehle-Conger > >>> MVP SQL Server Reporting Services > >>> > >>> "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message > >>> news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... > >>>> Since RS exports csv files using a comma delimiter, Excel doesn't by > >>>> default, open the file correctly. Is there a way to export to csv > >>>> using a > >>>> format which excel will handle correctly using its default settings? > >>>> Currently the user must open the file from within Excel and specify > >>>> "Delimited" not "Fixed Width" and go through the Text Import Wizard. > >>>> > >>>> Thanks in advance, > >>>> > >>>> Ryan Opfer > >>>> > >>>> > >>> > >>> > >> > >> > > > > > >
For some reason, Excel doesn't understand Unicode CSV files by default. If
we did an ANSI CSV file (which you can specify in the DeviceInfo) then we would lose data in certain scenarios. We could look at all of the characters in the file and see whether we could use ANSI without data loss but this is a lot of work. -- Brian Welcker Group Program Manager Microsoft SQL Server Reporting Services This posting is provided "AS IS" with no warranties, and confers no rights. [quoted text, click to view] "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message news:%23ETGwjAIFHA.1172@TK2MSFTNGP12.phx.gbl... > Thanks Bruce for all the help, I do appreciate it. This sounds like an > adequate solution that I think will work. > > > > It does seems strange though (although seemingly common for MS) to have RS > export to csv using a format that Excel doesn't natively understand by > default. I would have hoped that whatever MS decides to set as a default > format for csv would be the same between MS applications. I guess that's > just something we should all be use to by now anyway. > > > > Ryan Opfer > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message > news:u1VflVAIFHA.3536@TK2MSFTNGP14.phx.gbl... >> Here is an idea for you. Have an additional report that is very plain and >> exports to Excel well. Have it be the same as the other on as far as the >> data is concerned and the report parameters. Then add a link that uses >> jump >> to URL and have it render the second shadow report as Excel passing it >> the >> parameters from the current report. I call it a shadow report because you >> can set it to not be visible in list view so the user never sees it. The >> only way it gets run is when they click on the link. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >> news:%23V19fKAIFHA.2784@TK2MSFTNGP09.phx.gbl... >>> An Excel format is what I tried first but I couldn't get around how >>> Excel >>> sees the document (it's too smart). Excel sees the document map and puts >> it >>> on sheet 1 then on sheet 2 (which has the main table data) it shows >>> drill >>> down. Ultimately this file will need to be imported into an MS SQL table >> so >>> it needs to be simple. >>> >>> Excel would work fine if I can remove the document map (obviously I can >>> do >>> this by just removing the document map from the report...and maybe >>> that's >>> something the user will have to live with) and drill down capabilities >> (the >>> user can't live without this though), but by then I'd pretty much be >> looking >>> at a plain csv file anyway. >>> >>> The key here is the user will be doing all this and just sending us back >>> a >>> basic csv or Excel file for the sql import so thereby has to be a simple >>> process. >>> >>> From your reply's, I take it that RS can't defign how it exports to csv >>> or >>> for that matter Excel. >>> >>> Thanks for the help, >>> >>> Ryan >>> >>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message >>> news:%23VOimb4HFHA.3076@tk2msftngp13.phx.gbl... >>> > You can render your report as an Excel file. No reason to use the CSV >>> > format in the first place. >>> > >>> > >>> > -- >>> > Bruce Loehle-Conger >>> > MVP SQL Server Reporting Services >>> > >>> > "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >>> > news:uQS0Du3HFHA.3332@TK2MSFTNGP15.phx.gbl... >>> >> Thanks Bruce for the quick reply. >>> >> >>> >> The Excel format is fine provided you go through the Text Import >>> >> Wizard >>> >> of Excel and specify that the file is a comma delimited csv file not >>> >> Fixed Width. I would like to figure out how to Export from RS using >>> >> a >>> >> format in which Excel will natively open the file as a comma >>> >> delimited >>> >> file and thereby bypass having to have the user go through the Text >>> >> Import Wizard. >>> >> >>> >> There was a post back in November (Jan Bodey) that said she fixed >>> >> this >> by >>> >> specifying rc:Encoding=ASCII, but I couldn't get this to work, and if >>> >> I >>> >> could have, I wouldn't expect the user to put this in the link >>> >> anyway. >>> >> >>> >> Does that make more sense? >>> >> >>> >> Thanks, >>> >> >>> >> Ryan >>> >> >>> >> >>> >> "Bruce L-C [MVP]" <bruce_lcNOSPAM@hotmail.com> wrote in message >>> >> news:e5%2331j3HFHA.1476@TK2MSFTNGP09.phx.gbl... >>> >>> Is there a problem with Excel format? >>> >>> >>> >>> -- >>> >>> Bruce Loehle-Conger >>> >>> MVP SQL Server Reporting Services >>> >>> >>> >>> "Ryan Opfer" <ropfer@usa.ibs.org> wrote in message >>> >>> news:OkccOY3HFHA.1108@TK2MSFTNGP10.phx.gbl... >>> >>>> Since RS exports csv files using a comma delimiter, Excel doesn't >>> >>>> by >>> >>>> default, open the file correctly. Is there a way to export to csv >>> >>>> using a >>> >>>> format which excel will handle correctly using its default >>> >>>> settings? >>> >>>> Currently the user must open the file from within Excel and specify >>> >>>> "Delimited" not "Fixed Width" and go through the Text Import >>> >>>> Wizard. >>> >>>> >>> >>>> Thanks in advance, >>> >>>> >>> >>>> Ryan Opfer >>> >>>> >>> >>>> >>> >>> >>> >>> >>> >> >>> >> >>> > >>> > >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |